Restore a dropped Azure Database for PostgreSQL server

APPLIES TO: Azure Database for PostgreSQL - Single Server

Important

Azure Database for PostgreSQL - Single Server is on the retirement path. We strongly recommend that you upgrade to Azure Database for PostgreSQL - Flexible Server. For more information about migrating to Azure Database for PostgreSQL - Flexible Server, see What's happening to Azure Database for PostgreSQL Single Server?.

When a server is dropped, the database server backup is retained for five days in the service. The database backup can be accessed and restored only from the Azure subscription where the server originally resided. The following recommended steps can be followed to recover a dropped PostgreSQL server resource within five days from the time of server deletion. The recommended steps work only if the backup for the server is still available and not deleted from the system.

Prerequisites

To restore a dropped Azure Database for PostgreSQL server:

• Azure Subscription name hosting the original server
• Location where the server was created

Steps to restore

1. Browse to the Azure portal. Select the Azure Monitor service, then select Activity Log.

2. In Activity Log, select Add filter as shown and set filters for the following:

   • Subscription = Your Subscription hosting the deleted server
   • Resource Type = Azure Database for PostgreSQL servers (Microsoft.DBforPostgreSQL/servers)
   • Operation = Delete PostgreSQL Server (Microsoft.DBforPostgreSQL/servers/delete)

3. Select the Delete PostgreSQL Server event, then select the JSON tab. Copy the resourceId and submissionTimestamp attributes in JSON output. The resourceId is in the following format: /subscriptions/ffffffff-ffff-ffff-ffff-ffffffffffff/resourceGroups/TargetResourceGroup/providers/Microsoft.DBforPostgreSQL/servers/deletedserver.

4. Browse to the PostgreSQL Create Server REST API Page and select the Try It tab highlighted in green. Sign in with your Azure account.

5. Provide the resourceGroupName, serverName (deleted server name), subscriptionId properties, based on the resourceId attribute JSON value captured in the preceding step 3. The api-version property is prepopulated and can be left alone.

6. Scroll below on Request Body section and paste the following replacing the "Dropped server Location" (for example, CentralUS, EastUS etc.), "submissionTimestamp", and "resourceId". For "restorePointInTime", specify a value of "submissionTimestamp" minus 15 minutes to ensure the command doesn't error out.

   {
     "location": "Dropped Server Location",
     "properties":
     {
       "restorePointInTime": "submissionTimestamp - 15 minutes",
       "createMode": "PointInTimeRestore",
       "sourceServerId": "resourceId"
     }
   }
   

   For example, if the current time is 2020-11-02T23:59:59.0000000Z, we recommend a minimum of 15 minutes prior restore point in time 2020-11-02T23:44:59.0000000Z. See below example and ensure that you're changing three parameters (location,restorePointInTime,sourceServerId) as per your restore requirements.

   {
     "location": "EastUS",
     "properties":
     {
       "restorePointInTime": "2020-11-02T23:44:59.0000000Z",
       "createMode": "PointInTimeRestore",
       "sourceServerId": "/subscriptions/ffffffff-ffff-ffff-ffff-ffffffffffff/resourceGroups/SourceResourceGroup/providers/Microsoft.DBforPostgreSQL/servers/sourceserver"
     }
   }
   

7. If you see Response Code 201 or 202, the restore request is successfully submitted.

   The server creation can take time depending on the database size and compute resources provisioned on the original server. The restore status can be monitored from Activity log by filtering for

   • Subscription = Your Subscription
   • Resource Type = Azure Database for PostgreSQL servers (Microsoft.DBforPostgreSQL/servers)
   • Operation = Update PostgreSQL Server Create

Troubleshooting

If you encounter any issues during the restore process, ensure that the resourceId and submissionTimestamp values are correct and that the backup is still available within the five-day retention period.

Next step

Resource Locks