How to create a copy of a database in PostgreSQL
Last modified: December 09, 2019
To create a copy of a database, run the following command in psql:
CREATE DATABASE [Database to create]
WITH TEMPLATE [Database to copy]
OWNER [Your username];For more information continue reading.
Starting the Server
The first step to copying a database is to open psql (the postgreSQL command line). On a macOS this can be done when you start the server.
Open the Postgres app:
In order to start the server, click the start button.
Once this is done, a list will appear showing your databases:
Double-click a database in order to open a psql command line interface. This will open a new window with a connection:
Now that a connection has been established, we can begin writing queries. You can switch to other databases by typing “\c [Database Name]”. To look at all the databases, the \list or \l meta-command can be used:
Copying the Database
CREATE DATABASE [Database to create]
WITH TEMPLATE [Database to copy]
OWNER [Your username];Replace the bracketed portions with your database names and username. This query will generate a copy of the database as long as the “Database to copy” is not currently being accessed. If the “Database to copy” is being accessed by a user, that connection will have to be terminated before copying the database. To do this, run the following command:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '[Database to copy]'
AND pid <> pg_backend_pid();This query will terminate any open connections to the “Database to copy”, and will cause brief interruptions to anyone accessing the “Database to copy”. It will disconnect users from the database, however psql will automatically reconnect a user whenever they run their next query as shown below:
Once they reconnect they can then run queries again against the database.
Note: They will not be able to reconnect until the database is completely copied.
Once you terminate the connections, create the copy using the first command to CREATE a copy of the specified database. Due to the fact that people can reconnect between the time you terminate and the time you copy, you may want to structure your commands like so:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = '[Database to copy]'
AND pid <> pg_backend_pid();
CREATE DATABASE [Database to create]
WITH TEMPLATE [Database to copy]
OWNER [Your username];When structured and run like this, the CREATE DATABASE command will run immediately after terminating connections. This will help ensure no connections form between terminating connections and copying the database.
Written by: Matthew Layne
Reviewed by: Matt David