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?
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.