Postgres - restore database with different owner

1 minute read

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.