DbSchema | Create a Database Copy in PostgreSQL

---

PostgreSQL • 13-Apr-2019

by DbSchema Team

---

Using SQL

1.Make sure that no other user is using the database by killing all connections:

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

2.Create a new database from the original template:

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Replace originaldb and newdb.

Using psql

Another method is to use psql for creating a database copy. Access the PostgreSQL command and execute:

createdb -O ownername -T originaldb newdb