postgresql - Postgres - 将数据从一个表批量传输到另一个表

Question

我需要将大量数据(几百万行)从一个表传输到另一个表。到目前为止，我已经尝试过这样做......

INSERT INTO TABLE_A (field1, field2) 
SELECT field1, field2 FROM TABLE_A_20180807_BCK;

这(最终)对包含大约 1000 万行的表有效(耗时 24 小时)。问题是我还有其他几个表需要应用相同的过程，而且它们都大得多(最大的是 2000 万行)。我曾尝试对包含 1200 万行的表进行类似的加载，但未能在 48 小时内完成，因此我不得不取消它。

其他可能影响性能的问题是 1) TABLE_A 有一个基于自动生成序列的字段，2) TABLE_A 有一个 AFTER INSERT 触发器，它解析每个新记录并将第二条记录添加到 TABLE_B

许多其他线程建议执行 TABLE_A_20180807_BCK 的 pg_dump，然后将数据加载回 TABLE_A。我不确定 pg_dump 是否真的适合我，因为我只对 TABLE_A 中的几个字段感兴趣，而不是全部。

相反，我想知道以下......

导出为 CSV 文件......

COPY TABLE_A_20180807_BCK (field1,field2) to 'd:\tmp\dump\table_a.dump' DELIMITER ',' CSV;

导入回所需的表......

COPY TABLE_A(field1,field2) FROM 'd:\tmp\dump\table_a.dump' DELIMITER ',' CSV

导出/导入方法可能会更快吗？在我开始另一项可能需要数天才能完成的工作之前，我希望得到一些这方面的指导，甚至可能不会做得更好! “试一试看看”这个显而易见的答案并不是真正的选择，我无法承受更多的停机时间!

(这是来自 this 的后续问题，如果需要任何背景详细信息)

更新....我认为触发器没有任何重大问题。在正常情况下，记录以大约 1000/秒(包括触发时间)的速率输入到 TABLE_A 中。我认为问题很可能是事务的大小，在正常情况下，记录被插入到每个 INSERT 100 条记录的 block 中，上面显示的语句试图在单个事务中添加 1000 万条记录，我的猜测是这是问题，但我无法知道它是否真的存在，或者是否有合适的解决方法(或者我提出的导出/导入方法是否会更快)

也许我应该早点强调这一点，每次插入 TABLE_A 都会触发一个触发器，将记录添加到 TABLE_B。 TABLE_B 中的数据是最终目标，因此禁用触发器不是一个选项!整个问题的出现是因为我不小心将触发器禁用了几天，而“如何在现有行上运行触发器”这个问题的首选解决方案似乎是“删除行并再次将它们添加回来”——请参阅原文发布(上面的链接)以获取详细信息。

我目前的尝试涉及使用带有 WHERE 子句的 COPY 命令将 TABLE_A_20180807_BCK 的内容拆分为十几个小文件，然后一次重新加载一个。这可能不会给我节省任何整体时间，但虽然我无法承受 24 小时的连续停机时间，但我可以承受 4 晚 6 小时的停机时间。

Answer 1

准备(如果您有权访问并且可以重新启动您的服务器)将 checkpoint_segments 设置为 32 或更多。这将减少此操作期间检查点的频率和数量。您可以在完成后撤消它。此步骤并非完全必要，但应该会大大加快写入速度。

编辑 postgresql.conf 并将 checkpoint_segments 设置为 32 或更多

第 1 步:删除/删除表 A 上的所有索引和触发器。

编辑:步骤 1a

alter table_a set unlogged;

(对要插入的每个表重复第 1 步)

第 2 步。(如果一次只做一张 table 则不需要)

 begin transaction;

第 3 步。

   INSERT INTO TABLE_A (field1, field2) 
   SELECT field1, field2 FROM TABLE_A_20180807_BCK;

(对所有插入的表重复步骤 3)

第 4 步。(如果您一次只做一张 table 则不需要)

 commit;

第 5 步在所有表上重新启用索引和触发器。

步骤 5a。

 Alter table_a set logged;