How to restore postgres database into another database name

Question

I use the postgres today and got a problem I dump the database that way

 pg_dump zeus_development -U test > zeus_development.dump.out 

what if I wnat to restore to another database zeus_production

How could I do?

Answer 1

Simple, first create your database using template0 as your template database:

createdb -U test -T template0 zeus_production 

Then, restore your dump on this database:

psql -U test zeus_production -f /path/to/zeus_development.dump.out 

When restoring, always use template0 explicit, as it is always an empty and unmodifiable database. If you don't use an explicit template, PostgreSQL will assume template1, and if it has some objects, like a table or function that your dumped database already has, you will get some errors while restoring.

Nonetheless, even if you were restoring on a database with the same name (zeus_development) you should create (or recreate) it the same way. Unless you used -C option while dumping (or -C of pg_restore if using a binary dump), which I don't recommend, because will give you less flexibility (like restoring on a different database name).

Answer 2

The PostgresSQL documentation has influenced me to use the custom format. I've been using it for years and it seems to have various advantages but your mileage may vary. That said, here is what worked for me:

pg_restore --no-owner --dbname postgres --create ~/Desktop/pg_dump  psql --dbname postgres -c 'ALTER DATABASE foodog_production RENAME TO foodog_development' 

There was no foodog_development nor foodog_production databases existing before the sequence.

This restores the database from the dump (~/Desktop/pg_dump) which will create it with the name it was dumped as. The rename names the DB to whatever you want.

The --no-owner may not be needed if your user name is the same on both machines. In my case, the dump was done as user1 and the restore done as user2. The new objects need to be owned by user2 and --no-owner achieves this.