Permission denied for sequence jobid_seq when trying to do a pg_dump

Question

Hi!

I am trying to perform a backup of my database in Azure Database for PostgreSQL flexible server into my machine. I have tried using pg_dump and also doing a backup through the pgAdmin client, however, in both cases I get the same error:

pg_dump: error: query failed: ERROR: permission denied for sequence jobid_seq
pg_dump: error: query was: SELECT last_value, is_called FROM cron.jobid_seq

The command I'm using for pg_dump is the following:

pg_dump -Fc -v --host=<host> --port=5432 --username=<username> --password=<password> --dbname=postgres -f <path-to-file>

I am connected to this database as the admin user (member of the azure_pg_admin role), so I think I should have the required permissions for performing a database backup.

I was also wondering if this issue was related to this one:

https://learn.microsoft.com/en-us/answers/questions/1026933/permission-denied-for-schema-cron.html

Any help on this matter would be much appreciated :)

Answer 1

Hi, @giovanni Welcome to the Microsoft Q&A platform, thanks for posting the question and for using Azure Services.
If my understanding is correct you are taking the dump of the Postgres database. with pg_dump -Fc -v --host=<host> --port=5432 --username=<username> --password=<password> --dbname=postgres -f <path-to-file>
Can you please confirm if you are trying to take default DB Postgres? if yes then because of that you are getting the error for cron.jobid sequence.
As per the design of PaaS environment, you cannot take dump of Postgres database as it is system database.
We recommend you create separate user databases to store the data.

You take backups of each tables in the Postgres database as a workaround. But in the future, please do not use the Postgres database to store user data.

Backup and restore in Azure Database for PostgreSQL - Flexible Server | Microsoft Learn

Regards
Geetha

Answer 2

I am having the same issue, but I don't understand what the solution is. Can you provide a more detailed description of the solution?

Answer 3

As mentioned by accepted answered by @GeethaThatipatri-MSFT , the best practice is indeed to create database per application/user instead of using default "postgres" database.

As per the design of PaaS environment, you cannot take dump of Postgres database as it is system database. We recommend you create separate user databases to store the data.

But if you are in that position, you can still perform your backup by appending the following flag to your pg_dump command: -N cron

Full example:

PGPASSWORD='your-pass' pg_dump postgres -h database-name.postgres.database.azure.com -U you-user -p 5432 -Z0 -Fc -N cron > postgres_dump.sql