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?

Answer 1

Your "quick solution" is the correct one:

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

This may not be an issue in your situation, but if you restore the database while ignoring owners and privileges, it results in information loss. In some cases, the information loss is not an issue, but in others, such as if you have an application that is accessing the database using one or more roles, and you need this information preserved, following some of the recommendations here, like doing a restore with "--no-privileges/--no-owner" may break your application(s).

PostgreSQL stores user and role information at a higher level in the hierarchy, i.e. at the level of the server, than at the level of the database. When you make a backup of a single database with pg_dump, it does not backup the creation of the users and roles itself, because this information resides at the level of the server.

As such, if you want to restore the database from the dump, you need to first create or restore the relevant users and roles.

If you have access to the original server setup, it is also possible to dump / export / backup this information using pg_dumpall. This might be the best option if you have a lot of users and roles.

In other cases, which it sounds like you have, the setup might be as simple as a single user that owns a particular database, and then you don't need to worry about this, you just need to create a single user and then assign that user as the owner of the new database you created, and then you can run pg_restore and it will not throw these errors.

There is no one-size-fits-all answer to the question above, but because your error messages only reference one user, I strongly suspect your setup is a simple one where there is just one user. In this case you just need to create this user on the level of the server, then create the database and assign this user as the owner, and then you will be able to run pg_restore.

If it's too late, you can always fix this stuff after-the-fact; in a simple case you might just need something like:

ALTER DATABASE db_name OWNER TO new_owner_name;

Again, you may not need to do this in your particular instance but in general, changing the role / owner tends to break things when there are external applications or scripts that might be connecting to the database using this role, so it's not a good thing to get in the habit of doing. It's always best to establish good habits and learn how to do things without information loss because even if it works now, it might not work next time.

Also, on a site like Stack Overflow, you will always have people finding your question who have a similar, but slightly different situation, and in this case, the "hack" solution that involves information loss will mislead them and waste their time.

Answer 2

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.

Answer 3

To restore the db, run the following command:

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

Answer 4

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

Answer 5

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.