How to duplicate the database in postgresql?

Question

I need to duplicate the existing database including its schema and structure to another new database. I need this in shell command environment and not in pgadmin. Please kindly help me.

nohup pg_dump exampledb > example-01.sql
createdb -O postgres exampledbclone_01

my user is "postgres"

nohup psql exampledbclone_01 < example-01.sql

$ pg_dump mydb > db.sql
$ psql -d newdb -f db.sql

score 61 · Answer 1 · answered May 05 '16 at 13:42

61

If you want to duplicate it within the same PostgreSQL install and you don't have actively connected users there's a handy shortcut:

CREATE DATABASE my_new_database TEMPLATE my_old_database;

or from the shell

createdb -T my_old_database my_new_database;

Otherwise you'll need to use pg_dump, createdb and pg_restore, e.g.

pg_dump -Fc -f olddb.pgdump -d olddb &&\
createdb newdb &&\
pg_restore -d newdb olddb.pgdump

If you're using nohup so the command doesn't die if you lose your ssh session, consider using screen instead.

answered May 05 '16 at 13:42

Craig Ringer

56,343
5
158
190

2

Awesome CREATE DATABASE my_new_database TEMPLATE my_old_database; – anjaneyulubatta505 Apr 11 '19 at 05:40
1

Brilliant! It not just copy structure, but also data! – Foton Jan 09 '20 at 08:25
@Foton Yep, though you can use pg_dump and pg_restore if you want to be more selective and dump (say) just the structure. – Craig Ringer Jan 13 '20 at 02:23
Any idea how long this should take for a 1 TB database? It's been going for about an hour already for me.. – Vincent Oct 20 '20 at 04:39
@Vincent Depends mainly on your system's I/O – Craig Ringer Oct 23 '20 at 06:40
For me, it finished after about 12 hours. – Vincent Oct 23 '20 at 15:36
Beautiful. Postgres is so easy. I have a recreate-test-db script with two lines: dropdb xxx_test; createdb -T xxx_development xxx_test – Dogweather Jun 01 '21 at 04:01

score 8 · Answer 2 · edited Feb 09 '20 at 10:04

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 disconnect all other users from the database, 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();

How to duplicate the database in postgresql?

2 Answers2