pg_dumpall - Azure Database for PostgreSQL - permission denied for database "azure_maintenance"

Question

PGPASSWORD=mypassword pg_dumpall -h mydbname.postgres.database.azure.com -p 5432 -U admin@mydbname

pg_dump: [archiver (db)] connection to database "azure_maintenance" failed: FATAL: permission denied for database "azure_maintenance" DETAIL: User does not have CONNECT privilege. pg_dumpall: pg_dump failed on database "azure_maintenance", exiting

How would you backup all db's in Azure Postgresql hosted service?

Answer 1

Since PostgreSQL 12.0 pg_dumpall has a --exclude-database option (see Release Notes).
Following should work:

pg_dumpall -h mydbname.postgres.database.azure.com -p 5432 -U admin@mydbname --exclude-database=azure* --clean --file=dump.sql