How do I dump a Postgres table and restore under another user/role? 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=&lt;password&gt;
PG_DATABASE=db_dev
PGHOSTADDR=&lt;ip_address&gt;
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=&lt;password&gt;
PGDATABASE=db_prod
PGHOSTADDR=&lt;ip_address&gt;
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?
Use the --no-owner option of pg_dump to skip the ALTER ... OWNER TO ... statements.