Introduction to PostgreSQL Clone Database
PostgreSQL clone database is defined as creating a new database by using the existing database; we can create a new database by using the existing database in PostgreSQL. We can create a clone by using the create database command; also, we can create a clone of the database by using the createdb command. We have also created a clone of an existing database by creating a dump of the existing database by 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 to create a database by using the existing database template. We can create a new database by using the existing database in PostgreSQL.
5. Createdb: This is defined as create a new database by 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 who was used to create a clone of the database by 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 have 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 by using the create database command and using the pg_dump command.
- To execute the create database and createdb command (create a clone of the database) in PostgreSQL, we need to have 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 first 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 have used the pg_dump command in PostgreSQL.
- We have also use the createdb command to create a clone of a database on an existing server in PostgreSQL.
Examples to Implement PostgreSQL Clone Database
Below is the example of a clone:
Example #1 – Clone the Database by using Create Database Command
We have created a clone of the existing database by using the create database command in PostgreSQL. Below is the 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 by using the createdb command in PostgreSQL. Below is the 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 by using the pg_dump command in PostgreSQL. Below is the 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
This is a guide to PostgreSQL Clone Database. Here we discuss the Introduction of PostgreSQL Clone Database and its Parameters along with practical examples and different subquery expressions. You can also go through our suggested articles to learn more –
