CODEWITHSUNDEEP
postgresql
newBike
newBike

Reputation: 15022

How to restore postgres database into another database name

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?

Upvotes: 49

Views: 79902

Answers (7)

mivk
mivk

Reputation: 15019

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"

Upvotes: 9

Ammar Sani
Ammar Sani

Reputation: 2273

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

Upvotes: 2

mmell
mmell

Reputation: 2498

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

Upvotes: 5

jethro
jethro

Reputation: 172

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

Upvotes: 0

pedz
pedz

Reputation: 2349

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.

Upvotes: 30

Ricardo Coelho
Ricardo Coelho

Reputation: 749

Isn't it easier to simply do the following?

createdb -U test -T zeus_development zeus_production

Upvotes: 7

MatheusOl
MatheusOl

Reputation: 11865

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).

Upvotes: 50

Related Questions