Permission denied for sequence jobid_seq when trying to do a pg_dump

giovanni 21 Reputation points
2022-09-30T22:32:49.997+00:00

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 :)

Azure Database for PostgreSQL
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    4 deleted comments

    Comments have been turned off. Learn more

  2. Danton Heuer 0 Reputation points
    2025-01-22T03:35:11.28+00:00

    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

    Was this answer helpful?

    0 comments
  3. ab-oe 6 Reputation points
    2022-10-07T15:57:48.347+00:00

    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?

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.