Restore Azure PostgreSQL-Flexible server as Files using Azure portal

This article describes how to restore an Azure PostgreSQL-Flexible server as Files backed up using Azure portal.

Prerequisites

Before you restore from Azure Database for PostgreSQL Flexible server backups, review the following prerequisites:

• 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.

• Ensure the target storage account for restoring backup as a file is accessible via a public network. If the storage account uses a private endpoint, update its public network access settings before executing a restore operation.

Restore Azure PostgreSQL-Flexible database

To restore Azure PostgreSQL-Flexible database, Follow these steps:

1. Go to Backup vault > Backup Instances. Select the PostgreSQL - Flexible server to be restored and select Restore.

   

   Alternatively, go to Backup center and select Restore.

2. Select the point in time you would like to restore by using Select restore point. Change the date range by selecting Time period.

   

3. 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.

4. Once the validation is successful, select Review + restore.

5. After final review of the parameters, select Restore to restore the selected PostgreSQL - Flexible server backup in target storage account.

6. Submit the Restore operation and track the triggered job under Backup jobs.

After the restore job is completed successfully, go to the storage account container to view the restored databases as files (.sql files) from your PostgreSQL – Flexible server. Azure Backup also generates the following backup files:

• Database.sql file per database: Contains data and schema information for a particular database.

• Roles.sql files for entire instance: Contains all role information ((azure_su(superuser) azure_pg_admin, replication, local admin, Microsoft Entra admins and any other custom roles on the server)) that exists at server level.

• Tablespace.sql file: Tablespace file.

• Schema.sql file: Contains schema information for all the databases on the server.

  Note

  We recommend you not to run this script on the PostgreSQL - Flexible server because the schema is already part of the database.sql script.

Restore the backup files from storage container to a new or existing PostgreSQL – Flexible server

To restore the backup files from storage container to a new or existing PostgreSQL – Flexible server, follow these steps:

1. Ensure that all required extensions are enabled on the new target Flexible server.

2. Match the server parameter values from the source PostgreSQL database to the Azure Database for PostgreSQL by accessing the Server parameters section in the Azure portal and manually updating the values accordingly. Save the parameter changes, and then restart the Azure Database for PostgreSQL - Flexible server to apply the new configuration.

3. If Microsoft Entra Authentication is required on the new server, enable it and create the relevant Microsoft Entra admins.

4. Create a new database for restoration.

   Note

   Before the database restoration, you must create a new, empty database. Ensure that your user account has the CREATEDB permission.

   To create the database, use the CREATE DATABASE Database_name command.

5. Restore the database using the database.sql file as the target admin user. 1.After the target database is created, restore the data in this database (from the dump file) from an Azure storage account by running the following command:

   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> --no-owner -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.

   Alternatively, you can download the backup file and run the restore directly.

6. Restore only the required roles and privileges, and ignore the common errors. Skip this step if you're performing the restoration for compliance requirements and data retrieval, as a local admin.

Restore roles and users for the restored databases

Vaulted backups are primarily restored for compliance needs such as, testing and audits. You can sign in as a local admin and restore using the database.sql file; no other roles are needed for data retrieval.

For other uses like accidental deletion protection or disaster recovery, ensure necessary roles are created as per your organization needs. Avoid duplications between roles.sql and database.sql.

• Restore the same Flexible server: Role restoration might not be necessary.
• Restore to a different Flexible server: Use the roles.sql file to recreate the required roles.

When you restore from roles.sql, some roles or attributes might not be valid for the new target server.

For environments with superuser access (on-premises or VMs), you can run all commands seamlessly.

Key considerations for the Flexible server scenario

Here are the key considerations:

• Remove Superuser-Only Attributes: On Flexible server, there's no superuser privileges. So, remove attributes, such as NOSUPERUSER and NOBYPASSRLS from the roles dump.
• Exclude Service-Specific Users: Exclude users specific to Flexible Server services ( azure_su, azure_pg_admin, replication, localadmin, Entra Admin). These specific service roles are automatically recreated when administrators are added to the new Flexible server.

Before you restore the database objects, ensure that you properly dump and clean up the roles. To perform this action, download the roles.sqlscript from your storage container and create all required logins.

• Create Non-Entra Roles: Use a local admin account to run the role creation scripts.
• Create Microsoft Entra Roles: If you need to create roles for Microsoft Entra users, use a Microsoft Entra administrator account to run the necessary scripts.

Next steps

Manage backup of Azure PostgreSQL - Flexible Server using Azure portal.