SQL PostgreSQL 将数据从一个数据库复制/传输到另一个数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36476192/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL copy/transfer data from one database to another
提问by fabvys
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
我试试这个，我得到跨数据库的错误......未实现
回答by voytech
This is a really straightforward task. Just use dblink for this purpose:
这是一项非常简单的任务。只需为此目的使用 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 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')
回答by voytech
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:
还有另一种方法可以做到。如果 dblink 扩展不可用，可以直接在命令行中复制数据，使用管道连接标准输入和输出：
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
但这仅适用于 postgres 9.4 或更高版本
回答by dpneumo
If you are on postgresql 9.0 or later (and probably 8.0 or later) in a psql session you can also use:
如果您在 psql 会话中使用的是 postgresql 9.0 或更高版本（可能是 8.0 或更高版本），您还可以使用：
CREATE DATABASE new_database TEMPLATE original_database;
The new_database will be a clone of original_database including tables, table schema, encodings, and data.
new_database 将是 original_database 的克隆，包括表、表架构、编码和数据。
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.
然而，重要的是要理解，这（还）不是作为通用的“复制数据库”工具。
