How to back up and restore an Azure Database for PostgreSQL server using PowerShell

APPLIES TO: Azure Database for PostgreSQL - Single Server

Azure Database for PostgreSQL servers is backed up periodically to enable restore features. Using this feature you may restore the server and all its databases to an earlier point-in-time, on a new server.

Prerequisites

To complete this how-to guide, you need:

• The Az PowerShell module installed locally or Azure Cloud Shell in the browser
• An Azure Database for PostgreSQL server

If you choose to use PowerShell locally, connect to your Azure account using the Connect-AzAccount cmdlet.

Azure Cloud Shell

Azure hosts Azure Cloud Shell, an interactive shell environment that you can use through your browser. You can use either Bash or PowerShell with Cloud Shell to work with Azure services. You can use the Cloud Shell preinstalled commands to run the code in this article, without having to install anything on your local environment.

To start Azure Cloud Shell:

Option	Example/Link
Select Try It in the upper-right corner of a code or command block. Selecting Try It doesn't automatically copy the code or command to Cloud Shell.
Go to https://shell.azure.com, or select the Launch Cloud Shell button to open Cloud Shell in your browser.
Select the Cloud Shell button on the menu bar at the upper right in the Azure portal.

To use Azure Cloud Shell:

1. Start Cloud Shell.

2. Select the Copy button on a code block (or command block) to copy the code or command.

3. Paste the code or command into the Cloud Shell session by selecting Ctrl+Shift+V on Windows and Linux, or by selecting Cmd+Shift+V on macOS.

4. Select Enter to run the code or command.

Set backup configuration

At server creation, you make the choice between configuring your server for either locally redundant or geographically redundant backups.

While creating a server via the New-AzPostgreSqlServer command, the GeoRedundantBackup parameter decides your backup redundancy option. If Enabled, geo redundant backups are taken. Or if Disabled, locally redundant backups are taken.

The backup retention period is set by the BackupRetentionDay parameter.

For more information about setting these values during server creation, see Create an Azure Database for PostgreSQL server using PowerShell.

The backup retention period of a server can be changed as follows:

Update-AzPostgreSqlServer -Name mydemoserver -ResourceGroupName myresourcegroup -BackupRetentionDay 10

The preceding example changes the backup retention period of mydemoserver to 10 days.

The backup retention period governs how far back a point-in-time restore can be retrieved, since it's based on available backups. Point-in-time restore is described further in the next section.

Server point-in-time restore

You can restore the server to a previous point-in-time. The restored data is copied to a new server, and the existing server is left unchanged. For example, if a table is accidentally dropped, you can restore to the time just the drop occurred. Then, you can retrieve the missing table and data from the restored copy of the server.

To restore the server, use the Restore-AzPostgreSqlServer PowerShell cmdlet.

Run the restore command

To restore the server, run the following example from PowerShell.

$restorePointInTime = (Get-Date).AddMinutes(-10)
Get-AzPostgreSqlServer -Name mydemoserver -ResourceGroupName myresourcegroup |
  Restore-AzPostgreSqlServer -Name mydemoserver-restored -ResourceGroupName myresourcegroup -RestorePointInTime $restorePointInTime -UsePointInTimeRestore

The PointInTimeRestore parameter set of the Restore-AzPostgreSqlServer cmdlet requires the following parameters:

When you restore a server to an earlier point-in-time, a new server is created. The original server and its databases from the specified point-in-time are copied to the new server.

The location and pricing tier values for the restored server remain the same as the original server.

After the restore process finishes, locate the new server and verify that the data is restored as expected. The new server has the same server admin login name and password that was valid for the existing server at the time the restore was started. The password can be changed from the new server's Overview page.

The new server created during a restore does not have the VNet service endpoints that existed on the original server. These rules must be set up separately for the new server. Firewall rules from the original server are restored.

Geo restore

If you configured your server for geographically redundant backups, a new server can be created from the backup of the existing server. This new server can be created in any region that Azure Database for PostgreSQL is available.

To create a server using a geo redundant backup, use the Restore-AzPostgreSqlServer command with the UseGeoRestore parameter.

To geo restore the server, run the following example from PowerShell:

Get-AzPostgreSqlServer -Name mydemoserver -ResourceGroupName myresourcegroup |
  Restore-AzPostgreSqlServer -Name mydemoserver-georestored -ResourceGroupName myresourcegroup -Location eastus -Sku GP_Gen5_8 -UseGeoRestore

This example creates a new server called mydemoserver-georestored in the East US region that belongs to myresourcegroup. It is a General Purpose, Gen 5 server with 8 vCores. The server is created from the geo-redundant backup of mydemoserver, also in the resource group myresourcegroup.

To create the new server in a different resource group from the existing server, specify the new resource group name using the ResourceGroupName parameter as shown in the following example:

Get-AzPostgreSqlServer -Name mydemoserver -ResourceGroupName myresourcegroup |
  Restore-AzPostgreSqlServer -Name mydemoserver-georestored -ResourceGroupName newresourcegroup -Location eastus -Sku GP_Gen5_8 -UseGeoRestore

The GeoRestore parameter set of the Restore-AzPostgreSqlServer cmdlet requires the following parameters:

When creating a new server using geo restore, it inherits the same storage size and pricing tier as the source server unless the Sku parameter is specified.

After the restore process finishes, locate the new server and verify that the data is restored as expected. The new server has the same server admin login name and password that was valid for the existing server at the time the restore was started. The password can be changed from the new server's Overview page.

The new server created during a restore does not have the VNet service endpoints that existed on the original server. These rules must be set up separately for this new server. Firewall rules from the original server are restored.

Next steps