Introduction to PostgreSQL Clone Database
PostgreSQL clone database is defined as the create 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 a very important 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 creating a new database by using the createdb command.
6. Name of User: This is defined as the name of the user which was used to create a clone of the database by using the existing database.
7. Pg_dump: This is defined as create 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 name of the backup file 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 create 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 the example of create a database and createdb command (create a clone of database) require superuser privileges to execute the create database and createdb command (create a clone of the database) in 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 user as db_test, this user doesn’t have superuser privileges so, it will issue 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 as 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 creating a clone of the database.
- If we have a copy database on other servers at the same time we have using pg_dump command in PostgreSQL.
- We have also use createdb command to create a clone of a database on an existing servers in PostgreSQL.
Examples to Implement PostgreSQL Clone Database
Below is the example of clone:
Example #1 – Clone the Database by using Create Database Command
We have creating a clone of the existing database by using the create database command in PostgreSQL. Below is the example of create 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 create a clone of the existing database by using the createdb command in PostgreSQL. Below is the example of create 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 create a clone of the existing database by using the pg_dump command in PostgreSQL. Below is the example of create 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 –
