PostgreSQL – Copy Database

In this article, we will discuss the process of copying a PostgreSQL database on the same server or from a server to another. 

PostgreSQL copy database within the same server:

If a situation arises where one needs to copy a PostgreSQL database within a database server for testing purposes. PostgreSQL makes it simple to do so using the CREATE DATABASE statement as follows: 

Syntax:
CREATE DATABASE target_database 
WITH TEMPLATE source_database;

This statement copies the source_database to the target_database. For instance, to copy the dvdrental sample database which is described here and can be downloaded from here, to the dvdrental_test database, you use the following statement: 

CREATE DATABASE dvdrental_test 
WITH TEMPLATE dvdrental;

It may take a while to complete copying depending upon the size of the original database. 

PostgreSQL copy database from a server to another:

There are many ways to copy a database between various PostgreSQL database servers. The connection between servers grows slower as the database gets larger. One way of doing so is to create a database dump and restore the same dump to another server. To do so the following commands need to be followed: 

• Step 1: Create a Dump file of the source database.

pg_dump -U postgres -d source_database -f source_database.sql

• Step 2: Copy the dump file to the remote server. 
• Step 3: Create a new database in the remote server where you want to restore the database dump:

CREATE DATABASE target_database;

• Step 4: Restore the dump file on the remote server: 

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

Example:
Here we will copy the dvdrental database from the local server to the remote server. First, we will dump the dvdrental database into a dump file e.g., dvdrental.sql: 

pg_dump -U postgres -O dvdrental dvdrental.sql

Then we will copy the dump file to a remote server and we will create the dvdrental database on the remote server: 

CREATE DATABASE dvdrental;

Now, we will restore the dump file that we just created into the remote server: 

psql -U postgres -d dvdrental -f dvdrental.sql

For high-speed connections between servers or for smaller databases, you can also use the following command: 

pg_dump -C -h local -U localuser source_database | psql -h remote -U remoteuser target_database

For instance, if one desires to copy the dvdrental database from the localhost to the remote server, you do it as follows: 

pg_dump -C -h localhost -U postgres dvdrental | psql -h remote -U postgres dvdrental

My Personal Notes arrow_drop_up

Save