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 just make a copy of the database and work on it without affecting 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 use the utility program named ‘pg_dump ‘ provided by PostgreSQL for dumping the database to the file. This will not block any other database operations while we are using pg_dump command.
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:
Explanation This above statement will copy the source_database database to the target_database database. If the given template does not exist in the 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 template0 to the ‘NewEduCBA’ by using the following statement:
Code:
CREATE DATABASE NewEduCBA
WITH TEMPLATE template0;
Output:
Illustrate the list of the database to verify the copy of the ‘eduCBA’ database is created with a new database named ‘NewEduCBA’ by using the ‘\l’ command and the snapshot.
Step 3: If the given template does not exist in the 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:
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 used to back up the 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 not necessary 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 (for e.g. .sql It is a script file which is 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:
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
This is a guide to PostgreSQL Copy Database. Here we discuss an introduction to PostgreSQL Copy Database, and how does it work with various methods. You can also go through our other related articles to learn more –
