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

2 Answers 2


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.

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

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:


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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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