PostgreSQL Clone Database

Updated May 12, 2023

Introduction to PostgreSQL Clone Database

PostgreSQL clone database is defined as creating a new database by using the existing one; we can create a new one using the existing one in PostgreSQL. You can use the createdb command to generate a database clone and the create database command to produce a database clone. We have also created a clone of an existing database by creating a dump of the existing database using the pg_dump command; we can create a backup of an existing database by using the pg_dump and restore it on another database.

Syntax of Clone Database

Below is the syntax of the clone database in PostgreSQL:

1. Clone Database by using Create Database Command 

create database new_database_name (Name of new database which we have cloning from another database) WITH TEMPLATE old_database_name (Create new database by using old database template.);

2. Clone Database by using Createdb Command 

create db –O name_of_user –T old_database_name (Create new database by using old database template.) new_database_name (Name of new database which we have cloning from another database);

3. Clone Database by using pg_dump Command

pg_dump –U name_of_user –W user_password –d name_of_database >backup_file_name.sql (Name of backup file which database we have cloning.)

psql –U name_of_user –W user_password –d (new database name) < backup_file_name.sql

Parameters

Below is the parameter description syntax:

1. Create Database: Create a database command is used to create a new database in PostgreSQL. We have created a clone of the database by using create database command in PostgreSQL.

2. New Database Name: This is defined as a new database name from which we have cloning from the other database.

3. Old Database Name: This is defined as the old database name from which we have cloning to the new database. This is an essential and useful parameter to clone the new database using the existing database.

4. With Template: This is defined as creating a database using the existing template. We can create a new database by using the existing database in PostgreSQL.

5. Createdb: This is defined as creating a new database using the createdb command. We have created a new database by using the createdb command.

6. Name of User: This is defined as the user’s name used to create a clone of the database using the existing database.

7. Pg_dump: This is defined as creating a dump of the database by using the pg_dump command. After creating a dump, we restored it in another database.

8. Backup File Name: This is defined as the backup file name from which we have created using the pg_dump command.

How to Clone a Database in PostgreSQL?

Below is the working of the clone database in PostgreSQL. We have created a clone of the existing database using the create database command and the pg_dump command.

• To execute the create database and createdb command (create a clone of the database) in PostgreSQL, we need superuser privileges to execute the statement.
• Below is an example of creating a database. The createdb command (create a clone of the database) requires superuser privileges to execute the create database and createdb command (create a clone of the database) PostgreSQL.

Example 1

Command:

psql -U db_test -d postgres
create database clone_test WITH TEMPLATE db_test;

Example 2

Command:

psql -U postgres -d postgres
create database clone_test WITH TEMPLATE db_test;

Output:

Explanation:

• In the above example, we have used the user as db_test; this user doesn’t have superuser privileges, so it will issue an error while executing the createdb(create a clone of the database).
• In the second example, we have created a clone of the database using the username Postgres; after using this user, we have created a clone of the db_test database.
• If we have to copy the same database on the same server for testing purposes at that time, we have used create database command; using this command, we have created a clone of the database.
• If we have a copy database on other servers simultaneously, we use the pg_dump command in PostgreSQL.
• We have also used the createdb command to create a clone of a database on an existing server in PostgreSQL.

Examples

Below is an example of a clone:

Example #1 – Clone the Database by using Create Database Command

We have created a clone of the existing database using the create database command in PostgreSQL. Below is an example of creating a clone of the existing database by using the create database command.

We have created database names as test_copy using a database of test_data.

Command:

\c test_data;
\dt
create database test_copy WITH TEMPLATE test_data;
\c test_copy;
\dt

Output:

Example #2 – Clone the Database by using Createdb Command

We have created a clone of the existing database using the createdb command in PostgreSQL. Below is an example of creating a clone of the existing database by using the createdb command.

We have created a database name as test_copy1 using a database of test_data.

Command:

\c test_data;
\dt
create db -U postgres -O postgres -T test_data test_copy1
psql -U postgres -d test_copy1
\dt

Output:

Example #3 – Clone the Database by using Createdb Command

We have created a clone of the existing database using the pg_dump command in PostgreSQL. Below is an example of creating a clone of the existing database by using the pg_dump command.

We have created a database name as test_copy2 using a database of test_data.

Command:

psql -U postgres -d test_data
\dt
\q
pg_dump -U postgres -W -d test_data > test_data.sql
psql -U postgres
create database test_copy2;
psql -U postgres -W -d test_copy2 < test_data.sql

Output:

Command:

psql -U postgres -d test_copy2
\dt

Output:

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Clone Database” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. Introduction to PostgreSQL Timestamp
2. How to Notify Works in PostgreSQL?
3. PostgreSQL JSON (Examples)
4. Introduction to PostgreSQL Timestamp