PostgreSQL 将数据从一个数据库复制/转移至另一个数据库

Question

我需要将数据从一个表复制到另一个表。这两个表的结构几乎相同，但存储在不同的数据库中。

我尝试过

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

当我尝试这样做时，我遇到了错误“跨数据库未实现”。

- fabvys

---

在多个数据库中处理数据是Postgres比大多数其他数据库更困难的（少数）事情之一。一个好的起点是了解外部数据包装器：http://www.postgresql.org/docs/current/static/postgres-fdw.html。 - Gordon Linoff

Answer 1

这是一个非常简单的任务。只需使用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)

如果您需要定期从外部数据库中获取数据，最好定义一个服务器和用户映射。然后，您可以使用更短的语句：

dblink('yourdbname', 'your query')

- percy

---

谢谢你的回答。它有效。你查询中的第二个 t 是远程数据库中获取数据的表。 - fabvys

如果在 SELECT a, b, c FROM t 中有 where 子句，如何转义单引号？ - Shiva

不要忘记加载 dblink CREATE EXTENSION dblink; - schoetbi

一个“定义服务器和用户映射”的示例会很有帮助。 - machineghost

Answer 2

还有另一种做法。如果没有可用的dblink扩展，可以使用命令行直接复制数据，使用管道连接标准输入和输出：

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

但是这只能在Postgres 9.4或更高版本中使用。

- percy

---

这个方法非常有效。如果你想要从源数据库中取出子集（比如从一个非常大的表中），你可以在管道的源端使用select语句，只传输你想要的行。例如：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' -- 重要的规定是目标表上的列必须与源表匹配。否则，你必须在源和目标复制语句中明确指定列名。 - Robert Casey

@voytech，如何为两个连接传递密码？ - Prashant Parekh

@PrashantParekh 当从远程数据库复制到本地数据库时，我是这样做的：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' - ML_Engine

这对我很有效...但数据没有被复制...如何也复制数据？ - Kavin Raju S

Answer 3

如果您使用的是 PostgreSQL 9.0 或更高版本（可能也适用于 8.0 或更高版本），在 psql 会话中，您还可以使用以下命令：

CREATE DATABASE new_database TEMPLATE original_database;

新建的数据库将是原始数据库的克隆版本，包括表、表模式、编码和数据。 从文档中了解： 主要限制是在复制源数据库时，不能有其他会话连接到该数据库。

我建议您通过从新旧数据库表中进行审慎选择来验证克隆是否正确。文档还说：

然而，重要的是要理解，这并不是一个通用的“COPY DATABASE”工具。

- dpneumo