Creating a copy of a database in Postgres

Question

What's the correct way to copy entire database (its structure and data) to a new one in pgAdmin?

sorin · Accepted Answer · 2012-04-05 12:29:41Z

up vote 229 down vote accepted

Postgres allows the use of any existing database on the server as a template when creating a new database. I'm not sure whether pgAdmin gives you the option on the create database dialog but you should be able to execute the following in a query window if it doesn't:

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Still, you may get:

ERROR:  source database "originaldb" is being accessed by other users

edited Apr 5 '12 at 12:29

sorin
13.6k971165

answered May 18 '09 at 7:18

Bell
3,7031814

20

Note that originaldb needs to be idle (no write transactions) for this to work. – synecdoche May 19 '10 at 23:51

3

this should be marked as best answer!! – Janning May 31 '11 at 11:29

7

in pgAdmin3, in the Object browser (left) pane, I can select Servers -> (my server) -> Databases, right-click Databases, and select "New Database". One of the options is the template, and the SQL used to create the database is equivalent. It is so much faster than a dump / restore on the same server. – jwhitlock Jun 17 '11 at 15:19

4

I know this is an old Q/A, but I feel it needs clarification: When @synecdoche says that originaldb must be idle, that means no write possibility at all. "Copying" a database in this fashion does not lock originaldb. PostgreSQL only prevents starting the copy if there are others accessing originaldb--not after the copy starts, so it is possible that another connection could modify the database while the "copy" is occurring. IMHO, this may be the easiest answer, but the "best" would be to use dump/restore. – Josh Jan 25 '12 at 15:20

1

I just saw this. @Josh: while the originaldb is being copied by create database with template, postgresql does not allow creating a new connection to it, so no changes are possible. – ceteras Nov 14 '12 at 17:15

show 2 more comments

Dariusz · Answer 2 · 2013-01-09 14:42:18Z

For those still interested, I have come up with a bash script that does (more or less) what the author wanted. I had to make a daily business database copy on a production system, this script seems to do the trick. Remember to change the database name/user/pw values.

#!/bin/bash

if [ 1 -ne $# ]
then
  echo "Usage `basename $0` {tar.gz database file}"
  exit 65;
fi

if [ -f "$1" ]
then
  EXTRACTED=`tar -xzvf $1`
  echo "using database archive: $EXTRACTED";
else
  echo "file $1 does not exist"
  exit 1
fi


PGUSER=dbuser
PGPASSWORD=dbpw
export PGUSER PGPASSWORD

datestr=`date +%Y%m%d`


dbname="dbcpy_$datestr"
createdbcmd="CREATE DATABASE $dbname WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;"
dropdbcmp="DROP DATABASE $dbname"

echo "creating database $dbname"
psql -c "$createdbcmd"

rc=$?
if [[ $rc != 0 ]] ; then
  rm -rf "$EXTRACTED"
  echo "error occured while creating database $dbname ($rc)"
  exit $rc
fi


echo "loading data into database"
psql $dbname < $EXTRACTED > /dev/null

rc=$?

rm -rf "$EXTRACTED"

if [[ $rc != 0 ]] ; then
  psql -c "$dropdbcmd"
  echo "error occured while loading data to database $dbname ($rc)"
  exit $rc
fi


echo "finished OK"

Arta · Answer 3 · 2012-05-25 03:15:32Z

up vote 2 down vote

PostgreSQL 9.1.2:

$ CREATEDB new_db_name -T orig_db_name -O db_user;

answered May 25 '12 at 3:15

Arta
413513

This is probably implemented as CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser; and as a result requires that the original database must be idle (no connections with write access) and any new connections to original database are prevented while the copying is in process. If you're happy with that, this works. – Mikko Rantalainen Mar 6 at 9:09

Anirban Chakrabarti · Answer 4 · 2012-01-11 06:20:26Z

up vote 2 down vote

What's the correct way to copy entire database (its structure and data) to a new one in pgAdmin?

Ans. CREATE DATABASE newdb WITH TEMPLATE originaldb;

Tried and tested.

answered Jan 11 '12 at 6:20

Anirban Chakrabarti
313

1

This requires originaldb to not be in use. Isomorph's method does not. – Bradley Jan 16 '12 at 19:50

zbyszek · Answer 5 · 2011-07-18 22:05:05Z

up vote 48 down vote

A command-line version of Bell's answer:

createdb -O ownername -T originaldb newdb

This should be run under the privileges of the database master, usually postgres.

answered Jul 18 '11 at 22:05

zbyszek
61548

1

This is a nice command BUT you will get createdb: database creation failed: ERROR: source database "conf" is being accessed by other users if you try to do it on a production database and as expected you do not want to shut it down to create a copy. – sorin Apr 5 '12 at 12:28

2

Yes, the same caveats apply to this command, as to explicit CREATE DATABASE invocation. Like the comments for Bell's answer above say, the database should be idle. – zbyszek Apr 13 '12 at 15:50

Isomorph · Answer 6 · 2010-08-14 00:24:43Z

up vote 3 down vote

In pgAdmin you can make a backup from your original database, and then just create a new database and restore from the backup just created.

Regards

answered Aug 14 '10 at 0:24

Isomorph
313

this works poorly if you have foreign keys or use schemas – Bradley Jan 16 '12 at 19:49

TrayMan · Answer 7 · 2009-05-18 07:05:46Z

up vote 19 down vote

Don't know about pgAdmin, but pgdump gives you a dump of the database in SQL. You only need to create a database by the same name and do 'psql mydatabase < mydump' to restore all of the tables and their data and all access privileges.

answered May 18 '09 at 7:05

TrayMan
2,941815

Thanks, I needed to create a dump from another server, and it seems this helps: postgresql.org/docs/8.3/interactive/… – egaga May 18 '09 at 11:33

4

You can even do pg_dump -U postgres sourcedb | psql -U postgres newdb although the efficiency of this technique may be questionable (since you probably end up context switching between reads and writes) – Frank Farmer Jun 7 '10 at 17:19

1

You can even get your dump from a remote machine via ssh: ssh dbserver pg_dump DBNAME | psql NEWDB ... or pg_dump DBNAME | ssh otherserver pgsql NEWDB ... Permissions and authentication of course need to be handled however you want to handle them. – ghoti Aug 15 '12 at 15:52

asked	3 years ago
viewed	42608 times
active	3 months ago

Creating a copy of a database in Postgres

7 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged postgresql or ask your own question.

Creating a copy of a database in Postgres

7 Answers

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged postgresql or ask your own question.

Related