Restore Azure Database for PostgreSQL backups by using the Azure portal

This article explains how to restore a database to an Azure Database for PostgreSQL server that you backed up by using Azure Backup.

You can restore a database to any Azure Database for PostgreSQL server of a different subscription or the same subscription but within the same region of the vault, if the service has the appropriate set of permissions on the target server.

Restore a PostgreSQL database

1. In the Azure portal, go to Backup vault > Backup Instances. Select a database, and then select Restore.

   

   Alternatively, you can go to this page from the Backup center.

2. On the Select restore point tab, select a recovery point from the list of all full backups available for the selected backup instance. By default, the latest recovery point is selected.

   

   If the restore point is in the archive tier, you must rehydrate the recovery point before restoring. Provide the following additional parameters required for rehydration:

   • Rehydration priority: The default is Standard.
   • Rehydration duration: The maximum rehydration duration is 30 days, and the minimum rehydration duration is 10 days. The default value is 15 days. The recovery point is stored in the backup datastore for this duration.

   Note

   Archive support for Azure Database for PostgreSQL is in limited preview.

3. On the Restore parameters tab, select one of the following restore types:

   • Restore as Database: The target server can be the same as the source server. However, overwriting the original database isn't supported. You can choose from the servers across all subscriptions but in the same region as that of the vault.

     1. For Select key vault to authenticate with target server, select a vault that stores the credentials to connect to the target server.

     2. Select Review and restore to trigger validation that checks if the service has restore permissions on the target server. These permissions must be granted manually.

     

     Important

     The database user whose credentials were chosen via the key vault has all the privileges over the restored database. Any existing database user boundaries are overridden.

     If the backed-up database had any user-specific permissions or constraints (for example, one database user can access a few tables, and another database user can access a few other tables), such permissions aren't preserved after the restore. If you want to preserve those permissions, use Restore as Files, and use the pg_restore command with the relevant switch.

   • Restore as Files: You can choose from the storage accounts across all subscriptions but in the same region as that of the vault.

     1. In the Target Container dropdown list, select one of the containers filtered for the selected storage account.
     2. Select Review + Restore to trigger validation that checks if the backup service has the restore permissions on the target storage account.

     

4. Submit the restore operation, and then track the triggered job on the Backup jobs pane.

   

Restore permissions on the target storage account

To assign the Backup vault's managed identity permission to access the storage account containers, follow these steps:

1. In the Azure portal, go to Storage Account > Access Control (IAM), and then select Add.

2. On the Add role assignment pane, in the Role dropdown list, select the Storage Blob Data Contributor role for the Backup vault's managed identity.

   

Alternatively, give granular permissions to the specific container that you're restoring to by using the Azure CLI az role assignment create command:

az role assignment create --assignee $VaultMSI_AppId  --role "Storage Blob Data Contributor"   --scope $id

Replace the assignee parameter's value with the application ID of the vault's managed identity. For the value of the scope parameter, refer to your specific container. To get the application ID of the vault's managed identity, select All applications under Application type. Search for the vault name and copy the Application ID value.

Restore databases across regions

You can use the Cross Region Restore option to restore Azure Database for PostgreSQL servers in a secondary region that's an Azure-paired region.

Before you start using Cross Region Restore, read these important considerations. To check if the feature is enabled, see Configure Cross Region Restore.

View backup instances in a secondary region

If Cross Region Restore is enabled, you can view the backup instances in a secondary region:

1. In the Azure portal, go to Backup Vault > Backup Instances.

2. Select the filter as Instance Region == Secondary Region.

Restore in a secondary region

The experience of restoring in a secondary region is similar to the experience of restoring in a primary region.

When you're configuring details on the Restore Configuration pane to configure your restore, you're prompted to provide only secondary region parameters. A vault should already exist in the secondary region, and the Azure Database for PostgreSQL server should be registered to the vault in the secondary region.

Follow these steps:

1. Select Backup Instance name to view details.

2. Select Restore to secondary region.

   

3. Select the restore point, the region, and the resource group.

4. Select Restore.

Monitor restore jobs in a secondary region

1. In the Azure portal, go to Monitoring + reporting > Backup jobs.

2. Filter Instance Region for Secondary Region to view the jobs in the secondary region.

Related content

• Troubleshoot PostgreSQL database backup by using Azure Backup