Restore Azure Database for PostgreSQL - Flexible Server using Azure PowerShell

This article describes how to restore Azure Database for PostgreSQL - Flexible Server using Azure PowerShell.

Let's use an existing Backup vault TestBkpVault, under the resource group testBkpVaultRG in the examples.

$TestBkpVault = Get-AzDataProtectionBackupVault -VaultName TestBkpVault -ResourceGroupName "testBkpVaultRG"

Set up permissions for PostgreSQL - Flexible Server restore

Backup vault uses managed identity to access other Azure resources. To restore from backup, Backup vault’s managed identity requires a set of permissions on the Azure PostgreSQL – Flexible Server to which the database should be restored.

To assign the relevant permissions for vault's system-assigned managed identity on the target PostgreSQL – Flexible Server, check the set of permissions needed to backup Azure PostgreSQL – Flexible Server database.

To restore the recovery point as files to a storage account, the Backup vault's system-assigned managed identity needs access on the target storage account.

Fetch the relevant recovery point of PostgreSQL - Flexible Server

Fetch all instances using Get-AzDataProtectionBackupInstance cmdlet and identify the relevant instance.

$AllInstances = Get-AzDataProtectionBackupInstance -ResourceGroupName "testBkpVaultRG" -VaultName $TestBkpVault.Name

You can also use Az.Resourcegraph and the Search-AzDataProtectionBackupInstanceInAzGraph cmdlet to search recovery points across instances in many vaults and subscriptions.

$AllInstances = Search-AzDataProtectionBackupInstanceInAzGraph -ResourceGroupName "testBkpVaultRG" -VaultName $TestBkpVault.Name -DatasourceType AzureDatabaseForPGFlexServer -ProtectionStatus ProtectionConfigured

To filter the search criteria, use the following PowerShell client search capabilities:

Search-AzDataProtectionBackupInstanceInAzGraph -ResourceGroupName "testBkpVaultRG" -VaultName $TestBkpVault.Name -DatasourceType AzureDatabaseForPGFlexServer -ProtectionStatus ProtectionConfigured | Where-Object { $_.BackupInstanceName -match "testpgflex"}

Once the instance is identified, fetch the relevant recovery point.

$rp = Get-AzDataProtectionRecoveryPoint -ResourceGroupName "testBkpVaultRG" -VaultName $TestBkpVault.Name -BackupInstanceName $AllInstances[2].BackupInstanceName

Prepare the restore request for PostgreSQL - Flexible Server

You can restore the recovery point for a PostgreSQL – Flexible Server database as files only.

Restore as files

Fetch the Uniform Resource Identifier (URI) of the container, within the storage account to which permissions were assigned. For example, a container named testcontainerrestore under a storage account testossstorageaccount with a different subscription.

$contURI = "https://testossstorageaccount.blob.core.windows.net/testcontainerrestore"

Use the Initialize-AzDataProtectionRestoreRequest cmdlet to prepare the restore request with all relevant details.

$OssRestoreAsFilesReq = Initialize-AzDataProtectionRestoreRequest -DatasourceType AzureDatabaseForPGFlexServer -SourceDataStore VaultStore -RestoreLocation $TestBkpVault.Location -RestoreType RestoreAsFiles -RecoveryPoint $rps[0].Property.RecoveryPointId -TargetContainerURI $contURI -FileNamePrefix "empdb11_postgresql-westus_1628853549768"

To connect to an existing PostgreSQL – Flexible Server and an existing database, use the following cmdlet:

pg_restore -h <hostname> -U <username> -d <db name> -Fd -j <NUM> -C <dump directory>

In this script:

• -Fd: The directory format.
• -j: The number of jobs.
• -C: Starts the output with a cmdlet to create the database itself and then reconnect to it.

The following example shows how the syntax might appear:

pg_restore -h <hostname> -U <username> -j <Num of parallel jobs> -Fd -C -d <databasename> sampledb_dir_format

If you have more than one database to restore, rerun the earlier cmdlet for each database. Also, by using multiple concurrent jobs -j, you can reduce the restore time of a large database on a multi-vCore target server. The number of jobs can be equal to or less than the number of vCPUs allocated for the target server.

Trigger the restore for PostgreSQL - Flexible Server

To trigger the restore operation with the prepared request, use the Start-AzDataProtectionBackupInstanceRestore cmdlet

Start-AzDataProtectionBackupInstanceRestore -BackupInstanceName $AllInstances[2].BackupInstanceName -ResourceGroupName "testBkpVaultRG" -VaultName $TestBkpVault.Name -Parameter $OssRestoreReq

Track jobs for PostgreSQL - Flexible Server restore

Track all jobs by using the Get-AzDataProtectionJob cmdlet. You can list all jobs and fetch a particular job detail.

You can also use Az.ResourceGraph to track jobs across all Backup vaults. Use the Search-AzDataProtectionJobInAzGraph cmdlet to get the relevant job that is across all Backup vaults.

$job = Search-AzDataProtectionJobInAzGraph -Subscription $sub -ResourceGroupName "testBkpVaultRG" -Vault $TestBkpVault.Name -DatasourceType AzureDatabaseForPGFlexServer -Operation OnDemandBackup

Next steps

Troubleshoot common errors for backup and restore operations for Azure Database for PostgreSQL - Flexible Server.