pg_restore error: role XXX does not exist

Thalis K.

Trying to replicate a database from one system to another. The versions involved are 9.5.0 (source) and 9.5.2 (target).

Source db name is foodb with owner pgdba and target db name will be named foodb_dev with owner pgdev.

All commands are run on the target system that will host the replica.

The pg_dump command is:

    pg_dump -f schema_backup.dump --no-owner -Fc -U pgdba -h $PROD_DB_HOSTNAME -p $PROD_DB_PORT -d foodb -s --clean;

This runs without errors.

The corresponding pg_restore is:

    pg_restore --no-owner --if-exists -1 -c -U pgdev -d foodb_dev schema_backup.dump

which throws error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3969; 0 0 ACL public pgdba
pg_restore: [archiver (db)] could not execute query: ERROR:  role "pgdba" does not exist
Command was: REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM pgdba;
GRANT ALL ON SCHEMA public TO pgdba;
GRANT ...

If I generate the dump file in plain text format (-Fp) I see it includes several entries like:

REVOKE ALL ON TABLE dump_thread FROM PUBLIC;
REVOKE ALL ON TABLE dump_thread FROM pgdba;
GRANT ALL ON TABLE dump_thread TO pgdba;
GRANT SELECT ON TABLE dump_thread TO readonly;

that try to set privileges for user pgdba who of course doesn't even exist as a user on the target system which only has user pgdev, and thus the errors from pg_restore.

On the source db the privileges for example of the dump_thread table:

# \dp+ dump_thread
Access privileges
-[ RECORD 1 ]-----+--------------------
Schema            | public
Name              | dump_thread
Type              | table
Access privileges | pgdba=arwdDxt/pgdba+
                  | readonly=r/pgdba
Column privileges |
Policies          |

A quick solution would be to simply add a user pgdba on the target cluster and be done with it.

But shouldn't the --no-owner take care of not including owner specific commands in the dump in the first place?

Thalis K.

I realized the --no-owner is not the same as the -x. I added the -x to all pg_dump commands, which means:

-x, --no-privileges          do not dump privileges (grant/revoke)

which in effect excludes the offending GRANT/REVOKE commands from the dump. Problem resolved.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

PostgreSQL getting error during pg_restore using pgadmin pg_restore: [archiver (db)] could not execute query: ERROR: role does not exist

From Dev

pg_restore ERROR: “Relation does not exist” and creating new database

From Dev

PG::ConnectionBad FATAL: role "Myname" does not exist

From Dev

PG::ConnectionBad FATAL: role "Myname" does not exist

From Dev

PostgreSQL error Fatal: role “username” does not exist

From Java

PostgreSQL error: Fatal: role "username" does not exist

From Dev

role (h) does not exist on document cudasign error

From Dev

Pg:Error column does not exist

From Dev

ERROR: column "xxx" does not exist: Postgresql plpgsql stored procedure

From Dev

"ERROR: role "$user" does not exist" when setting up postgresql server

From Dev

"ERROR: role "$user" does not exist" when setting up postgresql server

From Dev

role does not exist and syntax error for inserting query in NodeJs into postgresql

From Dev

Import psql file trows "ERROR: role "userabc" does not exist"

From Dev

FOSJsRoutingBundle - The route "xxx" does not exist

From Dev

Rails: PG::UndefinedTable: ERROR: relation "..." does not exist

From Dev

PG::UndefinedTable: ERROR: relation does not exist

From Dev

PG::UndefinedTable: ERROR: relation "musicians" does not exist

From Dev

Sidekiq PG::UndefinedColumn: ERROR: does not exist

From Dev

PG::UndefinedTable: ERROR: relation 'caves' does not exist

From Dev

Postgresql: FATAL: role does not exist

From Dev

FATAL: role "root" does not exist

From Dev

PG::UndefinedObject: ERROR: type "hstore" does not exist but it does

From Dev

TFS build server error on build "The type or namespace name 'xxx' does not exist..."

From Dev

Error in _ViewImports.cshtml The type or namespace name 'xxx' does not exist in the namespace 'yyy'

From Dev

ActionView::Template::Error (PG::UndefinedColumn: ERROR: column "weeknumber" does not exist

From Dev

Postgres. role "root" does not exist. When trying to pg:pull database from Heroku

From Dev

UUID=xxx does not exist. Dropping to a shell

From Dev

UUID=xxx does not exist. Dropping to a shell

From Dev

oracle database 19c - grant [rolename] to [user] error ORA-01917: user or role does not exist

Related Related

  1. 1

    PostgreSQL getting error during pg_restore using pgadmin pg_restore: [archiver (db)] could not execute query: ERROR: role does not exist

  2. 2

    pg_restore ERROR: “Relation does not exist” and creating new database

  3. 3

    PG::ConnectionBad FATAL: role "Myname" does not exist

  4. 4

    PG::ConnectionBad FATAL: role "Myname" does not exist

  5. 5

    PostgreSQL error Fatal: role “username” does not exist

  6. 6

    PostgreSQL error: Fatal: role "username" does not exist

  7. 7

    role (h) does not exist on document cudasign error

  8. 8

    Pg:Error column does not exist

  9. 9

    ERROR: column "xxx" does not exist: Postgresql plpgsql stored procedure

  10. 10

    "ERROR: role "$user" does not exist" when setting up postgresql server

  11. 11

    "ERROR: role "$user" does not exist" when setting up postgresql server

  12. 12

    role does not exist and syntax error for inserting query in NodeJs into postgresql

  13. 13

    Import psql file trows "ERROR: role "userabc" does not exist"

  14. 14

    FOSJsRoutingBundle - The route "xxx" does not exist

  15. 15

    Rails: PG::UndefinedTable: ERROR: relation "..." does not exist

  16. 16

    PG::UndefinedTable: ERROR: relation does not exist

  17. 17

    PG::UndefinedTable: ERROR: relation "musicians" does not exist

  18. 18

    Sidekiq PG::UndefinedColumn: ERROR: does not exist

  19. 19

    PG::UndefinedTable: ERROR: relation 'caves' does not exist

  20. 20

    Postgresql: FATAL: role does not exist

  21. 21

    FATAL: role "root" does not exist

  22. 22

    PG::UndefinedObject: ERROR: type "hstore" does not exist but it does

  23. 23

    TFS build server error on build "The type or namespace name 'xxx' does not exist..."

  24. 24

    Error in _ViewImports.cshtml The type or namespace name 'xxx' does not exist in the namespace 'yyy'

  25. 25

    ActionView::Template::Error (PG::UndefinedColumn: ERROR: column "weeknumber" does not exist

  26. 26

    Postgres. role "root" does not exist. When trying to pg:pull database from Heroku

  27. 27

    UUID=xxx does not exist. Dropping to a shell

  28. 28

    UUID=xxx does not exist. Dropping to a shell

  29. 29

    oracle database 19c - grant [rolename] to [user] error ORA-01917: user or role does not exist

HotTag

Archive