Postgres - restore database with different owner

1 minute read


  • You have a database
  • You want to “clone” this database into another database
  • That one should get a different owner


Step one, dump the database.

pg_dump -h            \
  -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                  \
  -U different_database_user        \
  -W                                \
  -d different_database             \
  --no-owner                        \
  --role=different_database_user    \
   -Ft                              \

DISCLAIMER: I have no idea if there is a better way. That just worked.