PostgreSQL copies / transfers data from one database to another

Question

I need to copy data from one table to another. 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'm trying to do this, I get a cross-database of errors ... not implemented

Answer 1

This is a very simple task. To do this, use dblink:

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 regularly receive data from an external database, it would be wise to determine the mapping of servers and users. Then you can use a shorter statement:

dblink('yourdbname', 'your query')

Answer 2

. dblink , , :

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

postgres 9.4

Answer 3

postgresql 9.0 (, , 8.0 ) psql, :

CREATE DATABASE new_database TEMPLATE original_database;

new_database _, , , .

:

, .

, db. :

, , " ".