How to restore postgres database to a different database name

Question

I am using postgres today and there is a problem I am dumping the database this way

pg_dump zeus_development -U test > zeus_development.dump.out 

what if i want to restore another zeus_production database

How can i do

Answer 1

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

 createdb -U test -T template0 zeus_production 

Then restore the dump in this database:

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

When restoring, always use template0 explicit, since it is always an empty and non-modifiable database. If you do not use an explicit template, PostgreSQL will accept template1 , and if it has some objects, such as a table or function, that are already in your database, you will get some recovery errors.

However, even if you were restoring a database with the same name ( zeus_development ), you must create (or recreate) it in the same way. If you did not use the -C option during dumping (or -C from pg_restore if you use a binary dump), which I do not recommend, because it will give you less flexibility (for example, restoring to a different database name).

Answer 2

The PostgresSQL documentation has influenced me to use a custom format. I have been using it for years, and it seems to have various advantages, but your mileage may vary. However, 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' 

Prior to this sequence, neither foodog_development nor foodog_production data nor foodog_development nor foodog_production .

This will restore the database from the dump ( ~/Desktop/pg_dump ), which will create it with the name under which it was saved. Rename the database names to whatever you want.

--no-owner may not be needed if your username is the same on both machines. In my case, the dump was made as user1 and recovery was done as user2 . New objects must belong to user2 and --no-owner does this.

Answer 3

Isn’t it easier to just do the following?

 createdb -U test -T zeus_development zeus_production 

Answer 4

This is the answer to dba.stackexchange , which I reproduce here:

Let's define some variables to make copying / pasting the rest easier.

 old_db=my_old_database new_db=new_database_name db_dump_file=backups/my_old_database.dump user=postgres 

It is further assumed that your backup was created in a “custom” format similar to the following:

 pg_dump -U $user -F custom $old_db > "$db_dump_file" 

To restore the $db_dump_file new database name $new_db :

 dropdb -U $user --if-exists $new_db createdb -U $user -T template0 $new_db pg_restore -U $user -d $new_db "$db_dump_file"