Copying an existing database or cloning a database is an important task in any database management system. Copying a database provides numerous features like time-saving, efficiency, data recovery, etc. Postgres allows us to create a new database based on the existing one. To accomplish this task the “CREATE DATABASE” command is used in Postgres.

This write-up presents a practical guide on how to copy a database in Postgres.

Copying or Cloning a Database in Postgres

There are various methods to copy a database in Postgres, such as CREATE DATABASE, CREATEDB, etc. Among them, the most convenient way of copying a database is the “CREATE DATABASE” command, whose syntax is depicted in the following snippet:

CREATE DATABASE [new_database_name]
WITH TEMPLATE [original_database]
OWNER [username];

Let’s comprehend the above syntax step-by-step:

• The CREATE DATABASE statement creates a new database in Postgres.
• The “new_database_name” represents the name of the duplicate/copied database.
• The “WITH TEMPLATE” parameter is used to create a new database based on an already existing database template.
• The “original_database” represents the name of the database to be copied.

Let’s put this syntax into practice.

Example 1: Copying a Database With CREATE DATABASE Command in Postgres

Follow the below-provided steps to create a copy of a particular database in Postgres:

Step 1: Launching SQL Shell (psql)

Open the psql and provide the necessary details to log in:

Step 2: Listing the Available Databases

Once you are successfully logged in, execute the “\l” command to get the list of available databases:

\l

Pick a database to be copied.

Step 3: Select a Database

Suppose the user wants to copy the “postgres” database. Use the “\d” command to see the content of the selected database:

\d

Step 4: Copying a Database

Now, use the CREATE DATABASE command to copy the selected database:

CREATE DATABASE postgres_copy
WITH TEMPLATE postgres
OWNER postgres;

A duplicate database named “postgres_copy” has been created successfully.

Step 5: Verify the Copied Database

To verify if the selected database has been copied or not. Users must follow the below-provided instructions:

Firstly, connect to the newly created database via the “\c” command:

\c postgres_copy;

The output snippet demonstrates that we have been successfully connected to the selected database, i.e. “postgres_copy”. Now, use the “\d” command to verify if the content of the original database has been copied to the “postgres_copy” database or not:

\d

From the above snippet, you can clearly observe that the selected database has been copied successfully.

Example 2: 'Other Users are Accessing the Source Database' ERROR in Postgres

While copying a database you may encounter a “Source Database is Being Accessed by Other Users” error, as shown in the following snippet:

In the above snippet, we encountered an error while creating a copy of the “postgres” database. The error states that multiple users are accessing/using the selected database. To rectify the stated error, you must terminate the other connections that are accessing the selected database. For this purpose, the below-provided query can be executed:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'postgres'
AND pid != pg_backend_pid();

The open connections have been terminated. Now, execute the CREATE DATABASE command to create the copy of the selected database:

CREATE DATABASE postgres_copy1
WITH TEMPLATE postgres
OWNER postgres;

The output shows that the copy of the selected database has been created successfully.

Conclusion

Postgres offers various commands to create a copy of a database, such as the CREATE DATABASE command, the CREATEDB command, etc. The most convenient way of copying or cloning a database is the “CREATE DATABASE” statement. To copy or clone a database, use the CREATE DATABASE command along with the “WITH TEMPLATE” parameter. This post presented a practical guide on how to create a copy of an already existing database in Postgres.