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

22 浏览
0 Comments

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

我是一个完全不懂PostgreSQL的新手。

我在db1中有7-8个视图的数据，我需要将它们复制到具有匹配模式的不同数据库db2的表中。目标数据库可以是同一个PostgreSQL实例，也可以是完全不同的服务器。

我知道在我熟悉的数据库中可以用2-3种不同的方法完成这个任务，但这次我束手无策。有人能给我一些建议吗？

在理想的情况下，我希望不要做任何感觉太像ETL的操作，我宁愿做一些类似这样的操作：

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

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

由于我不了解PostgreSQL，我真的不知道可能实现的范围。

0

2 答案

0 Comments

问题的出现原因：

在PostgreSQL中，无法直接将视图作为COPY语句的源或目标。因此，为了将一个数据库中的视图复制到另一个数据库中的表中，需要采取其他方法。

解决方法：

一种解决方法是创建一个临时表，将视图的数据复制到该临时表中，然后再使用COPY语句将临时表的数据复制到目标表中。具体步骤如下：

1. 创建一个临时表mytmp，并从视图myview中选取数据插入到该临时表中。

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

2. 使用COPY语句将临时表mytmp的数据复制到指定的文件（例如/tmp/test.csv）中。

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

另一种更好的方法是将视图复制到表中，然后使用pg_dump命令将表的数据导出到文件中，再使用该文件重新创建和填充表。具体步骤如下：

1. 将视图复制到表中，创建表table1并将视图view1的数据插入到该表中。

create table table1 as select * from view1;

（根据需要，可以依次复制其他视图到其他表中）

2. 使用pg_dump命令将表table1、table2等导出到指定的文件（例如myfile.out）中。

pg_dump -t table1 -t table2 ... mydbname > myfile.out

（根据需要，可以指定导出的表）

3. 使用导出的文件myfile.out重新创建和填充表。

psql -d mydbname -f myfile.out

需要注意的是，这种方法只能复制表的数据，而不会复制表的约束。

0
0 Comments

问题的原因是需要将一个PostgreSQL数据库中的视图的结果复制到另一个数据库中的表中。解决方法是使用PostgreSQL的COPY TO语句将视图的结果导出为CSV文件，然后在目标数据库中创建相应的表，并使用COPY语句将CSV文件中的数据导入到表中。

具体的解决方法如下：

1. 使用COPY (SELECT * FROM view1) TO '/var/lib/postgres/myfile1.csv'语句将视图的结果导出为CSV文件。

2. 使用CREATE table tbl1 AS SELECT * FROM view1 LIMIT 0语句在目标数据库中创建一个与视图结构相同的表，但不包含数据。

3. 根据CSV文件中的数据，使用逆向工程方法创建目标数据库中的所有表的DDL语句。可以使用pgAdmin等工具来执行此操作。

4. 在源数据库中删除空表。

5. 使用COPY tbl1 FROM '/var/lib/postgres/myfile1.csv'语句将CSV文件中的数据导入到目标数据库的表中。

如果没有足够的权限或无法访问服务器文件系统，可以考虑在psql中使用\copy命令代替COPY命令。此外，还可以使用Dump/Restore方法进行数据迁移。对于一次性的数据迁移，可以使用上述方法之一。对于重复应用，可以考虑使用dblink或SQL/MED来管理外部数据，或者使用数据复制技术。

尽管PostgreSQL的规则系统允许构建可更新的视图，但遗憾的是，无法将视图的结果直接从文件中复制。

0