PostgreSQL copy/transfer data from one database to another

Question

I need to copy data from one table to another. the two tables have almost the same structure, but are in different databases.

i tried

INSERT INTO db1.public.table2(
  id,
  name,
  adress,
  lat,
  lng
)
SELECT
  id,
  name,
  adress,
  lat
  lng
FROM db2.public.table2;

wenn i try this, i get error cross database ... not implemented

Working with data in multiple databases is one of the (few) things that is more difficult in Postgres than in most other databases. A place to start is understanding foreign data wrappers: http://www.postgresql.org/docs/current/static/postgres-fdw.html. — Gordon Linoff, Apr 07 '16 at 12:29

score 36 · Accepted Answer · answered Apr 07 '16 at 12:55

36

This is a really straightforward task. Just use dblink for this purpose:

INSERT INTO t(a, b, c)
SELECT a, b, c FROM dblink('host=xxx user=xxx password=xxx dbname=xxx', 'SELECT a, b, c FROM t') AS x(a integer, b integer, c integer)

If you need to fetch data from external database on a regular basis, it would be wise to define a server and user mapping. Then, you could use shorter statement:

dblink('yourdbname', 'your query')

answered Apr 07 '16 at 12:55

percy

988
9
9

2

thanks for the answer. it works. the second t in your query is the table in the remote db where to get the data from. – fabvys Apr 08 '16 at 09:30
1

If there's `where` clause in `SELECT a, b, c FROM t`, how to escape single quotes? – Shiva Nov 08 '18 at 07:46
1

Don't forget to load dblink `CREATE EXTENSION dblink;` – schoetbi Oct 08 '20 at 10:13
An example of how one would "define a server and user mapping" would be handy. – machineghost Jun 27 '22 at 17:10

score 25 · Answer 2 · answered Apr 08 '16 at 09:43

25

There's also another way to do it. If dblink extension is not available, it's possible to copy data directly in command line, using pipe connecting standard input and ouput:

psql source_database -c 'COPY table TO stdout' | psql target_database -c 'COPY table FROM stdin'

But this is gonna work only in postgres 9.4 or higher

answered Apr 08 '16 at 09:43

percy

988
9
9

2

This works really well. If you are wanting to subset the source database (say from a very large table), you can use a select statement on the source side of the pipe to transfer only the rows you want. For instance: psql source_database -c 'COPY (SELECT * FROM source_schema.source_table where id > 45303692 and id < 45303792) TO stdout' | psql target_database -c 'COPY table FROM stdin' -- the important stipulation is that the columns on the destination table must match the source. Otherwise, you have to be explicit on the column names on both the source and destination copy statements. – Robert Casey Apr 25 '18 at 23:18
@voytech, How to pass password for both connection? – Prashant Parekh Sep 04 '19 at 10:19
2

@PrashantParekh when copying from a remote db to a local db, I did this by: `psql "host= user= database= password=" -c 'COPY(SELECT * FROM source_schema.source_table) TO stdout' | psql local_db_name -c 'COPY local_table FROM stdin'` – ML_Engine Feb 25 '20 at 18:06
This works well for me... But the data has not been copied... How to copy the data too? – Kavin Raju S May 07 '20 at 05:24

dpneumo · Answer 3 · 2017-01-29T16:39:38.540

If you are on postgresql 9.0 or later (and probably 8.0 or later) in a psql session you can also use:

CREATE DATABASE new_database TEMPLATE original_database;

The new_database will be a clone of original_database including tables, table schema, encodings, and data.

From the docs:

The principal limitation is that no other sessions can be connected to the source database while it is being copied.

I would recommend that you verify that the clone is in fact correct with judicious selects from the new and old db tables. The docs also say:

It is important to understand, however, that this is not (yet) intended as a general-purpose “COPY DATABASE” facility.

PostgreSQL copy/transfer data from one database to another

3 Answers3

Linked