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?
7 Answers
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).
Comments
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.
1 Comment
code_monk
I use the custom format universally because text format often fails on restore. This answer is the most useful to me.
This has an answer on dba.stackexchange, which I reproduce here:
Let's define a few variables to make the rest easier to copy/paste
old_db=my_old_database
new_db=new_database_name
db_dump_file=backups/my_old_database.dump
user=postgres
The following assumes that your backup was created with the "custom" format like this:
pg_dump -U $user -F custom $old_db > "$db_dump_file"
To restore $db_dump_file to a 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"
Comments
Isn't it easier to simply do the following?
createdb -U test -T zeus_development zeus_production
5 Comments
pedz
Actually, the above does work. The original question did not mention he was moving to a new machine. It was just assumed. The -T option is quite power.
M46
That's great. It safes me a lot of time. Simple and easy.
Ken Ratanachai S.
Wouldn't this take double disk space?
Chris F
How does this work if restoring onto a new machine, that is, dumping from MachineA, but restoring on MachineB?
jacurtis
@ChrisF it wouldn't work in your scenario. It is also important to note that the above command only works if there are no connections on the template database (the "old" database) when duplicating and renaming it. So usually you need to terminate connections first, then run this. It also isn't portable across machines as you noted. But this is convenient if your database has no users (or can afford to cut connections) and just need to clone databases within the same instance.
If your dump does not include the name, the restore will use the DB defined in DESTINATION. Both SOURCE and DESTINATION are Connection URLs.
Dump without --create
pg_dump \
--clean --if-exists \
--file ${dump_path} \
--format=directory \
--jobs 5 \
--no-acl \
--no-owner \
${SOURCE}
Restore without --create
pg_restore \
--clean --if-exists \
--dbname=${DESTINATION} \
--format=directory \
--jobs=5 \
--no-acl \
--no-owner \
$dump_path
1 Comment
Malcolm Crum
This worked seamlessly, no issues with foreign keys or anything, unlike when I used
psql and a text dump. Thanks!Thanks to @mmel for the answer. But this I tweaked that works mine:
Dump:
pg_dump \
--file {filename}.tar \
--format=tar \
--no-acl \
--no-owner \
--no-privileges \
-U {username} \
-h {host} \
-p {port} \
{db-name}
Restore:
pg_restore \
--format=tar \
--no-acl \
--no-owner \
--no-privileges \
-U {username} \
-h {host} \
-p {port} \
{db-name}
{same-filename-as-dump}.tar
2 Comments
Richard Barraclough
This does not work.
pg_restore: error: too many command-line arguments (first is "dump.pg_dump_c").
Ammar Sani
@richard-barraclough Sorry to hear that this doesn't works for you. Referring to mmel answer previously also not working at the first place. However after few tweaks and trials, it works. Hence post here as it works on mine. Not much could help to solve yours. Hope you will find one soon.
Here's a hacky way of doing it, that only works if you can afford the space and time to use regular .sql format, and if you can safely sed out your database name and user.
$ pg_dump -U my_production_user -h localhost my_production > my_prod_dump.sql
$ sed -i 's/my_production_user/my_staging_user/g' my_prod_dump.sql
$ sed -i 's/my_production/my_staging/g' my_prod_dump.sql
$ mv my_prod_dump.sql my_staging_dump.sql
$ sudo su postgres -c psql
psql> drop database my_staging;
psql> create database my_staging owner my_staging_user;
psql> \c my_staging;
psql> \i my_staging_dump.sql
here