How to Copy a PostgreSQL Database Safely
Learn how to copy a PostgreSQL database using SQL and command-line tools, including connection cleanup and template-based cloning.
On this page
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
