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