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

postgresql

shareimprove this question

edited Jan 11 '15 at 21:17

frlan

4,49731452

asked May 18 '09 at 7:00

egaga

11.3k94054

add a comment

16 Answers

active oldest votes

up vote

925

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

To fix it you can use this query

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity

WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();

shareimprove this answer

edited Jun 4 '17 at 20:37

Alex78191

541717

answered May 18 '09 at 7:18

Bell

12.9k31823

60

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

54

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

21

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

8

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

4

Note that if you're using pgAdmin and executing CREATE DATABASE ... TEMPLATE xxx from a SQL command window, you must disconnect from the database in the main pgAdmin window or you'll get the error about users connected to the database. – Jack R-G Sep 18 '14 at 19:18

show 7 more comments

up vote

260

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.

shareimprove this answer

edited May 23 '17 at 12:02

Community♦

11

answered Jul 18 '11 at 22:05

zbyszek

3,89411917

5

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

6

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

add a comment

up vote

94

down vote

To clone an existing database with postgres you can do that

/* KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)*/

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity

WHERE pg_stat_activity.datname = 'SOURCE_DB' AND pid <> pg_backend_pid();

/* CLONE DATABASE TO NEW ONE(TARGET_DB) */

CREATE DATABASE TARGET_DB WITH TEMPLATE SOURCE_DB OWNER USER_DB;

IT will kill all the connection to the source db avoiding the error

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

shareimprove this answer

edited Apr 4 '16 at 11:59

David

1,85863766

answered May 12 '14 at 14:33

Brugolo

2,0651412

7

+1 for mentioning a script solution to avoid the access error – bully Jul 8 '14 at 12:35

13

On Postgres 9.2 I have to replace procpid with pid for this to work – marxjohnson Sep 10 '14 at 10:14

add a comment

up vote

50

down vote

In production environment, where the original database is under traffic, I'm simply using:

pg_dump production-db | psql test-db

shareimprove this answer

edited Apr 26 at 18:57

answered Nov 12 '14 at 21:47

Tregoreg

3,61072951

4

One issue I've found with this method is that pg_dump will hold open its transaction until the restore into the new database is complete, even if pg_dump has actually finished its dump. This can cause locking issues in some cases (for example, if a DDL statement is run on the source DB). – Chris Butler Mar 4 '15 at 17:17

1

Plus one for not using temporary intermediate files. – Ardee Aram Jul 6 '15 at 4:27

It was also my solution. Yesterday it worked, now random unique constrain are violated. Note: I drop all table to receiver db. – gunzapper Apr 1 '16 at 10:45

@gunzapper Yep, the destination database (test-db) sure has to be clean before you run the operation. – Tregoreg Apr 1 '16 at 13:22

1

@AlexanderMills pg_dump -s? postgresql.org/docs/current/static/app-pgdump.html – Tregoreg Oct 24 '16 at 19:15