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 (
#!/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 (
#!/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?