I have what seems to me at least an odd behavior when I'm dealing with a restored database from Azure Postgres. The end result seems to be that both the original and the restored server are linked meaning any change against the restored server is also seen in the in the original server.
Here is the details of what I'm doing. In the Azure Portal I'm going to my Azure Postgres server and clicking the restore button. I'm picking a date from a few days ago and typing in a new server name. After about 10 minutes the restore is completed and I connect Azure data studio to both the original server and the new restored server. At this point I write the queries against each of these servers and they return the same results.
To be clear I can insert rows into the restored server and the inserted rows are available in the original server. This is not the behavior that I expected, can somebody explain to me what is going and why this is the case and more importantly I can use this Restore.
What I am trying to do is restore the server, but then I only want to copy the data from 1 or 2 tables from the restored server onto the original server.
Paul. The behavior you are experiencing is the result of the restore operation by the PostgreSQL Azure Database service, as detailed here.
There are two types of restore available:
- Point-in-time restore is available with either backup redundancy option and creates a new server in the same region as your original server.
- Geo-restore is available only if you configured your server for geo-redundant storage and it allows you to restore your server to a different region.
The restore operation actually creates an entirely new instance of a PostgreSQL instance with your server configuration and a copy of your database at the point-in-time-selected.