22

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
47

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.

| improve this answer | |
  • 1
    Awesome CREATE DATABASE my_new_database TEMPLATE my_old_database; – anjaneyulubatta505 Apr 11 '19 at 5:40
  • 1
    Brilliant! It not just copy structure, but also data! – Foton Jan 9 at 8: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 at 2: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 at 4:39
  • @Vincent Depends mainly on your system's I/O – Craig Ringer Oct 23 at 6:40
5

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();
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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