Unable to restore psql database from pg_dump with a different username

Question

I need to dump a postgres database from computer1 with postgres username1 and then restore it on computer2 with postgres username2. I keep running into the error that looks like the backup file wants to use username1:

When I run this on computer2:

psql dbname < backupname.pgsql

I get this error:

ERROR:  role "username1" does not exist

I have tried:

// Dumping from computer1:

pg_dump dbname > backupname.sql
pg_dump dbname > backupname.pgsql
pg_dump -U username1 dbname -N topology -T spacial_ref_sys > backupname.pgsql


// Restoring on computer2:

psql dbname < backupname.pgsql

Is it the dumping or the restoring that needs to be modified to get past this?

Nether

Answer 1

The problem is with the dumping. With insight from this post I was able to resolve this using:

// On Computer1

pg_dump dbname -O -x > backupname.sql


// On Computer2

psql dbname < backupname.sql

The option flags used with pg_dump are:

-O   <-- No owner
         Do not output commands to set ownership of objects to match the original database

-x   <-- No privileges
         Prevent dumping of access privileges (grant/revoke commands)

See the PostgreSQL docs for pg_dump for more info on the option flags.

Nether

Answer 2

If you are using pgAdmin then you can either remove the checkbox in DumpOptions #2 with Owner and otherwise remove the privilege like --no-privileges and remove ownership like --no-password in the dump query like
/usr/bin/pg_dump --host localhost --port 5432 --username "postgres" --no-password --format custom --no-privileges --no-tablespaces --verbose --file "as" "databasename". Also if you have constraints on the table then disable triggers also while creating the dump.

If you cannot create another backup of the database then the alternate way is to replicate the owner and roles of the dumped database to the new database. If you don't do that then you will get an error saying 'ACL does not exist' (not sure as faced it long back)

Sabatier

I am not using pgAdmin, but maybe someone who sees this will be. Thanks for providing this option. @Piyush – Nether

Thank you @Piyush. – Nether

Answer 3

You don't need to cripple your dump by discarding owner/privileges. You can do it at restore time.

Use pg_restore with the --no-acl (and probably --no-owner) options:

-x
--no-privileges
--no-acl
    Prevent restoration of access privileges (grant/revoke commands).

--no-owner
    Do not output commands to set ownership of objects to match the
    original database. By default, pg_restore issues ALTER OWNER or SET
    SESSION AUTHORIZATION statements to set ownership of created schema
    elements. These statements will fail unless the initial connection
    to the database is made by a superuser (or the same user that owns
    all of the objects in the script). With -O, any user name can be
    used for the initial connection, and this user will own all the
    created objects.

So something like:

pg_restore --no-privileges --no-owner -U postgres --clean ... $Your_sql_backup

Otherwhere