How to Copy a PostgreSQL Database Safely
For the developer who needs a working copy of a Postgres database to test a migration against real rows.
On this page
You need the schema and the data of a live database on a second database, so that a migration can be tried on real rows before it runs anywhere that matters. PostgreSQL does that in one statement: CREATE DATABASE clones an existing database when you name it as the template. The condition it comes with is strict, and it is what makes a first attempt fail: nothing else may be connected to the database you are copying.
Every statement below runs from a session on a different database, because your own session would otherwise be one of the connections in the way:
# sports_db is the database to copy, sports_db_copy will be the copy.
psql -d postgres
Copy the database with SQL
By default a new database is cloned from the system database template1. Name a different one and you get a copy of that database instead, with its tables, its indexes and its rows:
CREATE DATABASE sports_db_copy WITH TEMPLATE sports_db OWNER dbuser;
CREATE DATABASE
OWNER dbuser hands the copy to the role that will use it; leave the clause out and the copy belongs to the role that ran the statement. Creating a database owned by another role requires the privilege to SET ROLE to it.
The two system templates are worth keeping apart while you are here. template1 is the one you get without a TEMPLATE clause, so a new database carries whatever has been added to it on this server. TEMPLATE template0 gives you a pristine database instead: only the standard objects of your PostgreSQL version, with no user-defined objects and no altered system objects.
The PostgreSQL 18 documentation for CREATE DATABASE is explicit that this is a clone rather than a general copy facility: "The principal limitation is that no other sessions can be connected to the template database while it is being copied." Your own session counts, which is why the statement runs from postgres and not from sports_db.
Why the copy fails while somebody is connected
CREATE DATABASE fails if any other connection to the template exists when it starts. Once it has started, new connections to the template are locked out until it finishes, so the source database is unavailable to applications for the length of the copy. Close the applications that hold a connection, and end the sessions that are left:
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'sports_db' AND pid <> pg_backend_pid();
With one session still open on sports_db, the query returns one row:
| pg_terminate_backend |
|---|
| t |
pg_terminate_backend sends the session a termination signal, and true says the signal was sent, not that the process is already gone. Passing a timeout in milliseconds as a second argument makes the function wait for the session to actually end. The pid <> pg_backend_pid() condition keeps the query from terminating the session running it.
Terminating the sessions leaves a gap: an application that reconnects between this query and CREATE DATABASE puts a connection back, and the copy fails again. Stop the application first, or run the two statements one after the other and read the error.
The same copy from the command line
createdb is the shell command that wraps CREATE DATABASE, so it is the same operation with the same restriction, reachable from a terminal or a deployment script without opening psql:
createdb -O dbuser -T sports_db sports_db_copy
-T names the template database and -O names the owner of the new one, both processed as double-quoted identifiers, so a name in mixed case survives. Add -e and createdb echoes the CREATE DATABASE statement it sends to the server, which is worth doing in a script whose log somebody reads later.
A clone is only as useful as your confidence that it is complete. Download DbSchema at https://dbschema.com/download.html and connect it to sports_db_copy: reverse-engineering draws the tables, columns and foreign keys of the copy as one diagram, so checking that everything came across is a look at a picture rather than a listing per table. Connecting and reverse-engineering into diagrams are in the free Community Edition.
