PostgreSQL Copy Database

Updated May 18, 2023

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.

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 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:

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:

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:

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