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
Accepted answer
  1. GeethaThatipatri-MSFT 27,642 Reputation points Microsoft Employee
    2022-10-01T02:14:41.73+00:00

    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

    2 people found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. 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?