Restore Azure Database for PostgreSQL Flexible backups (preview)
This article explains how to restore an Azure PostgreSQL -flex server backed up by Azure Backup.
Prerequisites
Before you restore from Azure Database for PostgreSQL Flexible server backups, ensure that you have the required permissions for the restore operation.
Backup data is stored in the Backup vault as a blob within the Microsoft tenant. During a restore operation, the backup data is copied from one storage account to another across tenants. Ensure that the target storage account for the restore has the AllowCrossTenantReplication property set to true.
Restore Azure PostgreSQL-Flexible database
Follow these steps:
Go to Backup vault > Backup Instances. Select the PostgreSQL-Flex server to be restored and select Restore.
Alternatively, go to Backup center and select Restore.
Select the point in time you would like to restore by using Select restore point. Change the date range by selecting Time period.
Choose the target storage account and container in Restore parameters tab. Select Validate to check the restore parameters permissions before the final review and restore.
Once the validation is successful, select Review + restore.
After final review of the parameters, select Restore to restore the selected PostgreSQL-Flex server backup in target storage account.
Submit the Restore operation and track the triggered job under Backup jobs.
Once the job is finished, the backed-up data is restored into the storage account. Below are the set of files recovered in your storage account after the restore:
The first file is a marker or timestamp file that gives the customer the time the backup was taken at. The file cannot be restored but if opened with a text editor should tell the customer the UTC time when the backup was taken.
The Second file database is an individual database backup for database called tempdata2 taken using pg_dump. Each database has a separate file with format – {backup_name}database{db_name}.sql
The Third File _roles. Has roles backed up using pg_dumpall
The Fourth file _schemas. backed up using pg_dumpall
The Fifth file _tablespaces. Has the tablespaces backed up using pg_dumpall
Post restoration completion to the target storage account, you can use pg_restore utility to restore the database and other files to a PostgreSQL Flexible server. Use the following command to connect to an existing postgresql flexible server and an existing database
az storage blob download --container-name <container-name> --name <blob-name> --account-name <storage-account-name> --account-key <storage-account-key> --file - | pg_restore -h <postgres-server-url> -p <port> -U <username> -d <database-name> -v ---account-name: Name of the Target Storage Account.--container-name: Name of the blob container.--blob-name: Name of the blob.--account-key: Storage Account Key.-Fd: The directory format.-j: The number of jobs.-C: Begin the output with a command to create the database itself and then reconnect to it.
If you have more than one database to restore, re-run the earlier command for each database.
Also, by using multiple concurrent jobs -j, you can reduce the time it takes to restore a large database on a multi-vCore target server. The number of jobs can be equal to or less than the number of vCPUs that are allocated for the target server.
To restore the other three files (roles, schema and tablespaces), use the psql utility to restore them to a PostgreSQL Flexible server.
az storage blob download --container-name <container-name> --name <blob-name> --account-name <storage-account-name> --account-key <storage-account-key> --file - | psql -h <hostname> -U <username> -d <db name> -f <dump directory> -v -Re-run the above command for each file.
Next steps
Support matrix for PostgreSQL-Flex database backup by using Azure Backup.