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?