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 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.
The basic idea is:
- dump the source db with
--no-owner and --no-privileges
--clean shouldn't be needed as we will restore into a new/empty DB.
- drop the target db if it already exists.
- re-create the target DB (empty) setting the owner to our target user (already exists, if not need to create).
- restore the dump to the empty target AS THE TARGET USER
--no-owner --no-privileges ensures the target user owns everything and no ownership/privs get carried over from the dump we made.--no-privileges and --no-owner are redundant here as we already excluded them when we dumped. So there are some redundant flags in the below example which you may not need if you are confident about how your dump was made.
Note that I am ditching any privs/ownership as I have a single user per db as db owner with full rights. If you have a more complicated permissions structure this may not be appropriate. Or perhaps you do the restore with single user and then let your server provisioning system add any missing roles/privs to the restored DB.
Example
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 --no-privileges "$DB_NAME_SRC" > "$DUMP_FILE"
# THE FOLLOWING HAPPENS ON THE PG box where you want to restore
# 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 target user/role
# DB_TARGET_OWNER must already exist in postgres or you need to create it
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 target 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.
