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?

Answer 1

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

Answer 2

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.