# How to Create a Copy of a Database in PostgreSQL

> In PostgreSQL, use the CREATE DATABASE command along with the “WITH TEMPLATE” parameter to copy or clone a database.

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.

---
[View this page online](https://www.commandprompt.com/education/how-to-create-a-copy-of-a-database-in-postgresql/)