Azure PostgreSQL Flexible Server Backups Incompatible with pg_restore Directory Format

Hi @Jeeva

Greetings!

As I understand it, your question is about how Azure PostgreSQL Flexible Server backups can properly support directory format and the correct approach for restore operations. Please correct me if I am misunderstanding.

there are some best recommendations for Azure PostgreSQL Flexible Server backups and restores.

Best practices for pg_dump

You can use the pg_dump utility to extract an Azure Database for PostgreSQL flexible server database into a script file or archive file. A few of the command line options that you can use to reduce the overall dump time by using pg_dump are listed in the following sections.

Directory format(-Fd)

This option outputs a directory-format archive that you can input to pg_restore. By default, the output is compressed.

Parallel jobs(-j)

When you're setting a value for the parallel jobs option, pg_dump requires the following:

• The number of connections must equal the number of parallel jobs +1, so be sure to set the max_connections value accordingly.
• The number of parallel jobs should be less than or equal to the number of vCPUs that are allocated for the database server.
• Compression(-Z0) This option specifies the compression level to use. Zero means no compression. Zero compression during the pg_dump process could help with performance gains.

Use a PITR server

You can perform a pg_dump on an online or live server. It makes consistent backups even if the database is being used. It doesn't block other users from using the database. Consider the database size and other business or customer needs before you start the pg_dump process. Small databases might be good candidates for performing a pg_dump on the production server.

Syntax for pg_dump

Use the following syntax for pg_dump:

pg_dump -h <hostname> -U <username> -d <databasename> -Fd -j <Num of parallel jobs> -Z0 -f sampledb_dir_format

Please refer to the official Microsoft documentation for more information and details.

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-pgdump-restore?wt.mc_id=knowledgesearch_inproduct_9ccfc0ae-fdbf-4e6d-99fc-db7dd8a70d95#best-practices-for-pg%5C_restore

I hope this information helps. Please do let us know if you have any further queries.

Hi @Jeeva

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

My question is around restoring azure Postgresql flexi server backups from Azure Backup vault.

1. Deployed Azure postgresql flexible server with Backup vault configured.
2. Triggered couple of adhoc backups from the backup instance (vault).
3. Then Using restore option from the backup vault which restored as files in azure storage account(list of files given below)
4. Then as per MS Docs suggestion tried to use pg_restore for the database.sql files and psql for _roles.sql, _schema.sql & _tablespace.sql
5. The documentation confuses by instructing to use **Directory format(-Fd)** where the files restored from the vault is not in the directory format.
6. Also How would I restore to either a new or existing Azure Postgresql flexible server. There is no clear path to restore into the db and use them without any issues.
7. If Azure does not provide clear instruction on safely restoring the postgresql flexible server from Backup Vault then I don't see a point in using Azure Backup vault. We could simply use pg tools for taking regular backups and store it somewhere which can be restored later.
8. When a existing postgresql flexi server is used to restore these backup files, postgres management db already exists and using psql to import the schema from the _schema.sql and _roles.sql file errors with conflicting objects. Could Azure provide clear instruction on how to get this restored to an existing flexible server.

Files List from the restore

1. backupstarttimestamp
2. **_database_azure_maintenance.sql
3. **_database_azure_sys.sql
4. **_database_openfga_20250404110645.sql
5. **_database_openfga.sql
6. **_database_postgres.sql
7. **_database_slip_20250404110645.sql
8. **_database_slip.sql
9. **_database_template1.sql
10. **_roles.sql
11. **_schema.sql
12. **_tablespaces.sql

Hi @Jeeva

Thank you for getting back to me with a detailed explanation of the issue.

The issue seems to be more related to the Azure Backup Vault. I am currently working with the specialized Service Area team to investigate further.

Please allow me some time I will come up with a solution soon.

Hi Jeeva

Since the backups are individual SQL dump files, you’ll need to restore each database using the psql command, rather than pg_restore.

Use the following command for each SQL file:

psql -h <hostname> -U <username> -d <database_name> -f <path_to_sql_file>

For files such as _roles.sql, _schema.sql, and _tablespaces.sql, please also use the psql command.

Make sure to connect to the appropriate database and verify that the roles or schemas do not already exist to avoid conflicts. You may need to manually drop conflicting roles, schemas, or tablespaces before running the scripts.
https://learn.microsoft.com/en-us/azure/backup/restore-azure-database-postgresql-flex

let us know if any help, we will always help as you needed.!

If the comment is helpful, please click "Upvote it".