Postgresql - backup database and restore on different owner?

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?

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

The above answer was helpful but ultimately didn't get me 100% there for my case so I thought I would share an iteration on the above for people with similar cases to myself.

In my scenario I may have staging and production databases with different names and different owners. I may need to migrate the staging database to replace the production database but with different name and different owner.

Or perhaps I need to restore a daily backup but change the name or owner for some reason.

Our permissions are fairly simple as each app gets own db/user so this won't help people with complicated user/role/permissions setups.

I tried using the create from template approach to copy the db but this fails if any users/connections are active on the source db so this doesn't work with live source dbs.

With a basic --no-owner restore, the db/table owners on the restored/new db are the user executing the commands (e.g. postgres)...so you will have an additional step to fix all the db permissions. As we have a simple single-app-specific-user-per-db setup, we can make things easier.

I want my app-specific user to own the db/tables even if they don't have permission to create the db in the first place.

Setup some vars...

DB_NAME_SRC="app_staging"
DB_NAME_TARGET="app_production"
DB_TARGET_OWNER="app_production_user"
DUMP_FILE="/tmp/$DB_NAME_SRC"

Then do backup/restore

# backup clean/no-owner
sudo -i -u postgres pg_dump --format custom --clean --no-owner "$DB_NAME_SRC" > "$DUMP_FILE"

# drop target if exists - doesn't work for db with active users/connections
sudo -i -u postgres dropdb   -U postgres --if-exists  "$DB_NAME_TARGET"

# recreate target db, specifying owner to be the new owner/user (user must already exist in postgres, presumably setup by your app deploy/provisioning)
sudo -i -u postgres createdb -U postgres --owner "$DB_TARGET_OWNER" -T template0 "$DB_NAME_TARGET"

# do the restore to the target db as the target user so any created objects will be owned by our target user.
sudo -i -u postgres pg_restore --host localhost --port 5432 --username "$DB_TARGET_OWNER" --password --dbname "$DB_NAME_TARGET" --no-owner --no-privileges "$DUMP_FILE"

# now in this simple case I don't need an additional step of fixing all the owners/permissions because the db and everything in it will be owned by the specified user.

Note that in the restore section I connected over the network with password instead of locally so I didn't have to change postgres local user authentication from peer to password. My db app-specific users are not local users anyway.

If you are looking for heroku. first create a sql dump file with no owner. And then load it to heroku.

pg_dump -O target_db -f mydb.sql
heroku pg:psql < mydb.sql

-O is used here for no-owner.

Using .sql file to restore is good idea instead of .dump file. (.dump file is need to uploaded on a downloaded url)