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

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.

  • Awesome CREATE DATABASE my_new_database TEMPLATE my_old_database; – anjaneyulubatta505 Apr 11 '19 at 5:40
  • 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

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.