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

Solution 1

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

Solution 2

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

Solution 3

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.

Comments

[Gordon Linoff]

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: postgresql.org/docs/current/static/postgres-fdw.html.

[fabvys]

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.

[Robert Casey]

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.

[Shiva]

If there's where clause in SELECT a, b, c FROM t, how to escape single quotes?

[Prashant Parekh]

@voytech, How to pass password for both connection?

[ML_Engine]

@PrashantParekh when copying from a remote db to a local db, I did this by: psql "host=<host> user=<user> database=<database> password=<password>" -c 'COPY(SELECT * FROM source_schema.source_table) TO stdout' | psql local_db_name -c 'COPY local_table FROM stdin'

[Kavin Raju S]

This works well for me... But the data has not been copied... How to copy the data too?

[schoetbi]

Don't forget to load dblink CREATE EXTENSION dblink;