pg_restore error: role XXX does not exist

Question

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. · Accepted Answer · 2016-05-17T09:23:57.280

111

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.

edited May 17 '16 at 09:23

answered May 17 '16 at 09:12

Thalis K.

7,363
6
39
54

9

Is there any way to restore the database without -x option..in my case dump is given to mr by someone else hence I cant modify it with)x option – Sanjay Salunkhe Sep 20 '16 at 16:45
114

`pg_restore --no-privileges --no-owner ...` allows you to specify not to include ownership or privileges at the time of restore whether or not those privileges and ownerships were included in the `pg_dump` – user1448319 Mar 21 '17 at 00:26
6

@user1448319 This should be the answer. It's much better to skip owner/privileges at restore time than at dump time. – mivk Sep 01 '19 at 18:21
1

@mivk: It's worth noting that, if using the (default) "plain" format option with `pg_dump`, it's not possible to apply options like `--no-privileges`/`--no-owner` at restore-time, so using them with `pg_dump` is the only option. – David P Jan 22 '20 at 10:26
1

@DavidP: true. It depends on the use case. But since the "plain" format is of course plain text, it would also be easy to filter out the lines setting ownership and privileges at restore time, or to edit them before restoring. – mivk Jan 22 '20 at 15:31
1

why not easily creating the missing role _before_ `pg_restore` by: `sudo -iu postgres createuser XXX` (Manjaro Linux / ArchLinux)? – Hartmut Pfarr Aug 14 '20 at 17:43
8

Are there any unforseen downsides to removing the ownerships and priviliges? – L.Youl Jan 05 '21 at 15:18
1

@l-youl: for a production DB I'm pretty sure there would be. You better recreate the roles before restoring, as Hartmut suggests. In this case, the OP most likely doesn't care because they're restoring just to test or look at the data. – Paul Dec 02 '21 at 18:13
1

For postgres v12+ here's the command that worked for me: `pg_restore -x --no-owner -d DB_NAME FILE.dump` – Puneet Pandey Jun 06 '22 at 06:18
@PuneetPandey Thanks, it's early 2023 and this was the only command on the whole thread that worked for me (psql v14) – Dan Mar 08 '23 at 03:21

score 7 · Answer 2 · edited Feb 19 '23 at 21:59

7

To restore the db, run the following command:

pg_restore -x --no-owner -d db_name backup.dump

edited Feb 19 '23 at 21:59

CharlesB

86,532
28
194
218

answered May 21 '22 at 14:25

AbdUlRahman Shawareb

161
3
8

score 3 · Answer 3 · answered Mar 26 '22 at 10:48

Restore DB backup using below command

pg_restore --no-privileges --no-owner -h localhost -p <DB_Port> -U <DB_User> -d <DB_Name>-1 <DB_Backup_Path>

Use flag --no-privileges To prevent restoration of access privileges (grant/revoke commands) and

--no-owner To prevent setting ownership of objects to match the original database

score 0 · Answer 4 · answered May 15 '23 at 09:13

If you have exported the database as "custom" file type, you can use the following command.

pg_restore -c -U <USERNAME_FOR_DATABASE -d <DATABASE_NAME> -v <PATH_NAME_IN_STRING> -W

Make sure you have created a database and you specify the correct username and pathname of the file.

pg_restore error: role XXX does not exist

4 Answers4

Linked