How to create a copy of a database in PostgreSQL using psql

4
min read

To create a copy of a database, run the following command in psql:

CODE:https://gist.github.com/fronofro/fafb20d21d56ce0cf9fbccccb6eb8f9f.js

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

CODE:https://gist.github.com/fronofro/fafb20d21d56ce0cf9fbccccb6eb8f9f.js

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:

CODE:https://gist.github.com/fronofro/2d6c8a884367d774af3b6bf6a5816f71.js

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:

CODE:https://gist.github.com/fronofro/e9a368523db6b6874bd16cd3a6c7ac6c.js

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.

Give Feedback on our Google Doc

Chapter topics