Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

pg_restore error: role XXX does not exist

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?

like image 401
Thalis K. Avatar asked May 17 '16 08:05

Thalis K.


People also ask

What is Pg_restore in Postgres?

Description. pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats. It will issue the commands necessary to reconstruct the database to the state it was in at the time it was saved.

Where is Pg_restore?

pg_dump, pg_dump_all, pg_restore are all located in the bin folder of the PostgreSQL install and PgAdmin III install.

How long does a Pg_restore take?

Its data is updated regularly as a result of tests, then a new dump must be made, and the dumps are regularly used to recreate the database in a well-defined state. I noted that the dump (using pg_dump -Fc database ) only takes a few seconds, but the restore ( pg_restore -d database ) takes about a minute.


1 Answers

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.

like image 136
Thalis K. Avatar answered Sep 30 '22 04:09

Thalis K.