Menu
I did backup on database on different server and that has different role than I need, with this command:
pg_dump -Fc db_name -f db_name.dump Then I copied backup to another server where I need to restore the database, but there is no such owner that was used for that database. Let say database has owner owner1, but on different server I only have owner2 and I need to restore that database and change owner.
What I did on another server when restoring:
createdb -p 5433 -T template0 db_name pg_restore -p 5433 --role=owner2 -d db_name db_name.dump But when restore is run I get these errors:
pg_restore: [archiver (db)] could not execute query: ERROR: role "owner1" does not exist How can I specify it so it would change owner? Or is it impossible?
810
First, connect to admin database and update DB ownership: psql postgres=# REASSIGN OWNED BY old_name TO new_name; This is a global equivalent of ALTER DATABASE command provided in Frank's answer, but instead of updating a particular DB, it change ownership of all DBs owned by 'old_name'.
No, each database can only have one owner. As stated previously you can have more than one superuser, or you can grant permissions specifically to group roles that are then inherited.
To back up, a PostgreSQL database, start by logging into your database server, then switch to the Postgres user account, and run pg_dump as follows (replace tecmintdb with the name of the database you want to backup). By default, the output format is a plain-text SQL script file.
You should use the --no-owner option, this stops pg_restore trying to set the ownership of the objects to the original owner. Instead the objects will be owned by the user specified by --role
createdb -p 5433 -T template0 db_name pg_restore -p 5433 --no-owner --role=owner2 -d db_name db_name.dump pg_restore doc
78
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
