How to Copy/Transfer Data from One Database to Another in PostgreSQL

Sometimes you may need to copy or transfer data from one database to another in PostgreSQL. It is very easy to do this using dblink function in PostgreSQL. In this article, we will learn how to copy/transfer data from one database to another in PostgreSQL.

How to Copy/Transfer Data from One Database to Another in PostgreSQL

Here are the steps to copy/transfer data from one database to another in PostgreSQL. We will look at two ways to do this – using dblink and using copy table statement.

1. Using dblink

dblink allows you to easily fetch data from another database local or remote. Here is its syntax.

dblink('connection string', 'your query')

Here is an example to use dblink to fetch data from table products in remote database sales. Replace hhh with databse host IP address or URL, xxx with database username , yyy with its password, sales with your database name, and modify the select query as per your requirement.

dblink('host=hhh user=xxx password=yyy dbname=sales', 'SELECT id, name, price FROM products')

Please note, in above case, you need to specify connection parameters in a space-separated manner.

The result of above dblink function will be just like any select statement. You can use it in a query to insert the fetched remote data into your local table, as shown below.

INSERT INTO local_products(id, name, price)
SELECT a, b, c FROM dblink('host=hhh user=xxx password=xxx dbname=sales', 'SELECT id, name, price FROM products') AS x(id integer, name varchar(10), price integer)

If you need to fetch the remote data on a regular basis, it is advisable to store connection string, as a variable for re-user, or create server-user mapping. Then you can easily use the shortcut.

dblink('yourdbname','query')

2. Using Copy Data Command

Here is the basic syntax to copy data from source_db to destination_db using copy data command. Please note the following command will work for PostgreSQL 9.4 or higher.

$ psql source_db -c 'COPY data_table TO stdout' | psql destination_db -c 'COPY data_table FROM stdin'

In the above command, source_db is the source database from where data is to be copied, and destination_db is the destination database to which data is to be copied. Here is an example to copy products table from sales database to new_sales database.

$ psql sales -c 'COPY products TO stdout' | psql new_sales -c 'COPY products FROM stdin'

3. Using Template Clause

If you are running PostgreSQL 9.0 or higher, you can also use TEMPLATE keyword to copy another database. You need to be logged into PostgreSQL session to be able to execute the following command.

psql> CREATE DATABASE new_database TEMPLATE original_database;

In this case, the new database will be clone of the original database. It will have the same table, schema, encodings and data.

However, it is important to remember that no other session can be connected to your original database while you are running the above command.

In this article, we have learnt 3 different ways to copy/transfer data from one database to another in PostgreSQL.

How to Copy/Transfer Data from One Database to Another in PostgreSQL