3

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?

sirkubax
  • 885
  • 2
  • 10
  • 19
  • is there a particular reason why you want this done on azure_maintenance db? you should be able to do this for all user databases without an issue but as far as azure_maintenance db, users do not have the required access to perform this. – chi Jul 22 '19 at 21:45
  • how did you get it sorted ? – jujule Dec 23 '19 at 17:16
  • I list db-names in separate script, and dump one by one:/ – sirkubax Jan 11 '20 at 14:16
  • Also trying to do this with pg_dumpall and having the same issue. Is there any way to avoid doing it one by one? Or any better way to dump and restore 300+ medium sized DB's on Azure? – Tim Trewartha Mar 25 '20 at 13:37

1 Answers1

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
mpoqq
  • 96
  • 5
  • 3
    ```pg_dumpall: error: query failed: ERROR: permission denied for table pg_authid pg_dumpall: error: query was: SELECT rolname FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 1 ``` – holms May 08 '22 at 14:07