将 PostgreSQL 视图中的结果从一个数据库复制到另一个表格

Question

完全的 PostgreSQL 新手。

我需要将 db1 中价值为 7-8 视图的数据复制到具有匹配模式的表中，这些表位于不同的数据库 db2 中。目标数据库可以是相同的 PostgreSQL 实例，也可以是另一台完全不同的计算机中。

在我熟悉的数据库中，我知道 2-3 种实现这个目标的不同方法，但对于这个问题，我感到无助。请问是否有人能够为我提供一些基本策略呢？

在理想情况下，我希望不必采取任何感觉过于 ETL 的操作 - 我更愿意选择某种方式。

SELECT FROM instance1.db1.viewname INTO instance2.db5.tablename

然后将数据从视图中导出为文本文件，并重新加载到目标表中。

由于我不了解PostgreSQL，所以我不知道可能的范围。

- Russell Christopher

---

谢谢你们俩。dlink_connect 看起来正是我需要的东西。然而，这个函数似乎没有被自动安装在我刚刚放在我的盒子上的发行版中。我是否需要从 pgAdmin 内部触发某种命令才能将其安装？ - Russell Christopher

没关系，我已经找到了解决方法。我在使用 pgAdmin 对目标数据库安装了一个扩展程序。现在一切都正常运行。 - Russell Christopher

Answer 1

任何查询都可以成为COPY TO的来源，自Postgres 8.2起可用。

COPY (SELECT * FROM view1) TO '/var/lib/postgres/myfile1.csv';

为了快速复制表结构，请在本地使用以下命令创建所需的表格：

CREATE table tbl1 AS
SELECT * FROM view1
LIMIT 0;   -- no data, just the schema.

然后复制（反向工程）DDL指令并在目标数据库中创建所有表。 pgAdmin 是一个方便的GUI工具。之后删除源数据库中的空表。使用以下命令加载数据：

COPY tbl1 FROM '/var/lib/postgres/myfile1.csv';

如果您缺乏必要的权限或无法访问服务器文件系统，请考虑使用 \copy in psql。参见：

• 将PostgreSQL表中特定行导出为INSERT SQL脚本
• 如何从CSV文件中更新Postgres中的选定行？

转储/还原（如@wildplasser所描述的）是另一种方法。

对于一次性的转移，建议使用其中一种方法。对于重复应用，dblink或SQL/MED （管理外部数据）可能更合适。
或者考虑复制。

- Erwin Brandstetter

---

糟糕，我的错。但是从文件中复制视图仍然是不可能的 :-) [这实际上很遗憾，因为规则系统允许构建可更新的视图] - wildplasser

Answer 2

CREATE TEMPORARY TABLE mytmp
AS SELECT * from myview
WHERE 1=1
;


COPY mytmp TO '/tmp/test.csv'
;

更好的方法是：

• 将视图复制到表中（例如：create table1 as select * from view1; ...）
• 使用pg_dump -t table1 -t table2 ... mydbname >myfile.out命令
• 使用myfile.out重新创建和填充表。

- wildplasser

---

这个额外复制的原因是视图不能用作COPY语句的源或目标。CREATE TABLE AS会处理（大部分）数据类型。约束将不会被复制到新表中。 - wildplasser