Export and import a PostgreSQL database with a different name?

Solution 1:

The solution was dumping it like this:

pg_dump --no-owner --no-acl blah > blah.psql

and importing it like this:

psql blah_devel < blah.psql > /dev/null

I still get this warning:

WARNING:  database "blah" does not exist

but the rest seems to work.

Solution 2:

If you are creating a text dump you can export the database without the CREATE DATABASE bits (i.e. don't specify -c and -C options to pg_dump) ; This will prevent Postgres from trying to drop, create & connect to the database.

If you're using one of the archive formats you can specify the -d option to pg_restore to name the database you want to restore to.

Check the man pages for pg_dump and pg_restore for more details, and don't forget to mount a scratch monkey before you try this on production systems in case I left out some important detail.

Solution 3:

Now pg_restore has -d option and you could set database name for importing data.

on source:

pg_dump -v -Fc mydb.dmp mydb

on dest:

createdb -T template1 mydb2

pg_restore -v -e -d mydb2 mydb.dmp