Situation:
- You have a database
- You want to “clone” this database into another database
- That one should get a different owner
Go.
Step one, dump the database.
pg_dump -h database.host \
-U database_user \
-W \
-O -Ft \
database \
> database.dump.tar
Important here is -O, which means --no-owner, which means “do not include owner information.
Step two, restore the database. The database in which the dump file is restored has to exist. If you don’t know how to create it, see here.
pg_restore \
-h database.host \
-U different_database_user \
-W \
-d different_database \
--no-owner \
--role=different_database_user \
-Ft \
database.dump.tar
DISCLAIMER: I have no idea if there is a better way. That just worked.