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
  • Log in
  • Sign up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Copy Database

PostgreSQL Copy Database

Sohel Sayyad
Article bySohel Sayyad
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 18, 2023

PostgreSQL Copy Database

Introduction to PostgreSQL Copy Database

We can copy the database whenever needed. There are various scenarios where we need In PostgreSQL Copy Database, from one server to another server or within the same server. The database copy operation is useful in many cases, such as testing, where we make a copy of the database and work on it without affecting the original database copy. We can create a dump file for a database if it has a big size, and later we can restore it to the remote server as per the requirements. The time required to create a copy depends upon the size of the database, it may take longer time if the original database is having a bigger size. We can dump the database into the file using the utility program ‘pg_dump’ provided by PostgreSQL. This will not block any other database operations while we are using the pg_dump command.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

How to copy the database in PostgreSQL?

We will understand the process of the PostgreSQL COPY DATABASE operation within the same server or in the different servers as follows:

1. PostgreSQL COPY DATABASE operation within the same server

Step 1: We can use the PostgreSQL CREATE DATABASE statement as follows to do the copy of the database:

Code:

CREATE DATABASE target_database
WITH TEMPLATE source_database;

Output:

PostgreSQL Copy Database1

Explanation This above statement will copy the source_database database to the target_database database. If the given template does not exist in PostgreSQL, then it will throw an exception on error. In order to make an empty database, we can use the template0 to avoid local additions, otherwise, we can use the templates.

Step 2: Consider the following example, where we will copy the ‘eduCBA’ database, which has been created with templates, to the ‘NewEduCBA’ by using the following statement:

Code:

CREATE DATABASE NewEduCBA
WITH TEMPLATE template0;

Output:

NewEduCBA

Illustrate the database list to verify the copy of the ‘eduCBA’ database is created with a new database named ‘NewEduCBA’ using the ‘\ l’ command and the snapshot.

Step 3: If the given template does not exist in PostgreSQL, then it will throw an exception on error. Consider the following example where we will get an error or exception, as template ‘educba’ does not exist.

Code:

CREATE DATABASE NewEduCBA
WITH TEMPLATE educba;

Output:

PostgreSQL Copy Database3

2. PostgreSQL COPY DATABASE operation from a server to another

Now consider if the database file is larger and the connection between the server is slow. We can perform the following steps, The pg_dump utility program is used to back up a single part or all parts of the PostgreSQL database. The pg_dump utility program makes a consistent database copy, which is an image of the database when it has been started the pg_dump program. The pg_dump utility program does not restrict the users for accessing the database (reading or writing the database objects) while performing the backup.

In order to use the pg_dump program, it is unnecessary to have superuser access, But the user should have read access to all database objects.

Step 1: Use the following command for making the dump file from the source database. The dump file in which we export the data can be an archive file or script format (e.g., .sql. It is a script file containing the SQL commands/ statements). The SELECT statements are getting executed by the pg_dump command.

Code:

pg_dump -U postgres -O source_database source_database.sql

Step 2: The dump file created in step 1 has to be copied to the remote server.

Step 3: Now, create a new database in the remote server in which we will restore the dump file.

Code:

CREATE DATABASE target_database;

Step 4: Now restore the dump file copied on the remote server in the database created in step 3.

Code:

psql -U postgres -d target_database -f source_database.sql

Now consider the following example where we will ‘eduCBA’ database to the ‘remote_educba.’

1. Now we, by using the following command, create a dump file ‘eduCBA.sql’ from the’eduCBA’ database

Code:

pg_dump -U postgres -O eduCBA eduCBA.sql;

2. The dump file created in step 1 has to be copied to the remote server.

3. Now, create a new database named ‘remote_educba’ in the remote server in which we will restore the dump file.

Code:

CREATE DATABASE remote_educba;

4. Now restore the dump file copied on the remote server in the database created in step 3.

Code:

psql -U postgres -d remote_educba -f eduCBA.sql

Output:

server to another

Conclusion

We hope from the above article, you have understood how the PostgreSQL COPY DATABASE operation work. Also, we have added several examples of the PostgreSQL COPY DATABASE operation to understand it in detail.

Recommended Articles

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

  1. PostgreSQL REINDEX
  2. Array in PostgreSQL
  3. hstore in PostgreSQL
  4. PostgreSQL UPDATE JOIN
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Blog as Guest
Courses
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

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?

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

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