question

Tech7868 avatar image
0 Votes"
Tech7868 asked AnuragSharma-MSFT commented

Azure Postgresql single server DB restore

Hi

We have created 2 postgresql server in 2 regions. Both have geo redundant backup on.

What are the steps to restore a specific DB on 1 server of the region to the server on the other region.

I only found articles where it mentioned to create an entire new server from the backup of the server of the other region.

Our requirement is to restore a specific DB from 1 region to another. Server in both the region already exits.

Thanks

azure-database-postgresql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Tech7868, just wanted to check if you need any other details. If any answer helped your query, you can mark it 'Accept Answer'

0 Votes 0 ·

1 Answer

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered AnuragSharma-MSFT commented

Hi @Tech7868, thanks for your patience.

We can use the pg_dump and pg_restore to achieve the required functionality but in this case we need to take manual backup of existing database and then restore the same in other instance of Azure PostgreSQL. Below are the commands that can be used to achieve this:

Please note these are Azure CLI commands and I used them in Bash in Azure Portal.

//Taking Dump of existing Database

 pg_dump -Fd -v --host=<YourSourceServer>.postgres.database.azure.com --port=5432 --username=<YourUsername> --dbname=<DatabaseName> -j 4 -f dump.dir

Restoring Database to target Server. We need to create the empty database where we want to restore

 $ pg_restore -v --no-owner --host=<TargetServer>.postgres.database.azure.com --port=5432 --username=<Username> --dbname=<NewDatabase> -j 4 dump.dir

Referenced Article: Upgrade your PostgreSQL database using dump and restore

Please let us know if this helps or else we can discuss further.


If answer helps, please mark it 'Accept Answer' as it could help others having similar issues.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @AnuragSharma-MSFT - I was looking for the geo-restore backup. Use case is if One region is down, how to restore specific Db from that region to the new region. If the region is down I cant run the pg_dump commands to get a db copy.

Azure Sql does provide a way to restore specific db across region, but since we are using postgresql we need the same functionality.

Thanks

0 Votes 0 ·

Hi @Tech7868, thanks for responding back.

As per Product Group, currently PostgreSQL engine does not support taking physical backups/restore at a database level.

However we can achieve this either through the scripts as mentioned in last reply or we can configure the database-level locally redundant or geo-redundant backup in Azure backup center and later on restore it on another Azure PostgreSQL instance at database-level. Please read through the below article that explains the same in detail and let us know if that could help you. This feature is still in preview.

Azure Database for PostgreSQL backup with long-term retention (preview)


0 Votes 0 ·