EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Clone Database

PostgreSQL Clone Database

Updated May 12, 2023

PostgreSQL Clone Database

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.

ADVERTISEMENT
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

PostgreSQL Clone Database Example

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:

PostgreSQL Clone Database Example 1

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:

PostgreSQL Clone Database Example 2

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:

using createdb command Example 3.1

Command:

psql -U postgres -d test_copy2
\dt

Output:

using createdb command Example 3.2

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
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
63+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW