How do I dump a Postgres table and restore under another user/role?

Question

I am getting an error when I try to restore a Postgres database table to a different user/role than from which it was dumped.

I am doing the pg_dump as OS user: 'postgres', and doing the restore as OS user 'postgres'.

The databases however were created as other users, not by the postgres user.

---

Error when running pg_restore script:

CREATE TABLE
psql:./person.sql:49: ERROR:  must be member of role "dev"
psql:./person.sql:57: ERROR:  current transaction is aborted, commands ignored until end of transaction block
psql:./person.sql:58: error: invalid command \N

I do not get this error if I dump/restore to any database created by the same user/role ( 'dev' ).

I only see it when I dump a table created by one user, and then try to restore under another database created by a different user/role.

---

Here is my dump script ( $./dump-table )

#!/bin/bash

PGUSER=dev
PGPASSWORD=<password>
PG_DATABASE=db_dev
PGHOSTADDR=<ip_address>
PGPORT=5432

TABLE=person
SQL_FILE=$TABLE.sql

rm -f ./*.sql
pg_dump \
   --format plain \
   --verbose \
   --file $SQL_FILE \
   --table $TABLE $PG_DATABASE

---

Here is my restore script ( $./restore-table )

#!/bin/bash

PGUSER=prod
PGPASSWORD=<password>
PGDATABASE=db_prod
PGHOSTADDR=<ip_address>
PGPORT=5432

TABLE=person
SQL_FILE=$TABLE.sql
psql -1 -f ./$SQL_FILE

---

Is there another parameter I need to use when dumping, or restoring the table to avoid this error?

Answer 1

Use the --no-owner option of pg_dump to skip the ALTER ... OWNER TO ... statements.

Answer 2

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.

pg_restore -p 5433 --no-owner --role=owner2 -d db_name db_name.dump

pg_restore link and doc