0

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?

2 Answers 2

1

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

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.