Postgresql - backup database and restore on different owner?

database postgresql database-backups database-restore owner
Andrius picture Andrius · Jul 17, 2015 · Viewed 63.1k times · Source

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?

Answer

Gary picture Gary · Jul 17, 2015

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

Related questions

How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
What is this Javascript "require"?

I'm trying to get Javascript to read/write to a PostgreSQL database. I found this project on github. I was able to get the following sample code to run in node. var pg = require('pg'); //native libpq bindings = `var pg = …

Read More
Import SQL dump into PostgreSQL database

We are switching hosts and the old one provided a SQL dump of the PostgreSQL database of our site. Now, I'm trying to set this up on a local WAMP server to test this. The only problem is that I …

Read More