克隆 Postgres 表，包括索引和数据 [英] Cloning a Postgres table, including indexes and data

问题描述

我正在尝试使用 plpgsql 创建 Postgres 表的克隆.迄今为止，我只是简单地截断表 2 并重新插入表 1 中的数据.

I am trying to create a clone of a Postgres table using plpgsql. To date I have been simply truncating table 2 and re-inserting data from table 1.

 TRUNCATE TABLE "dbPlan"."tb_plan_next";
 INSERT INTO "dbPlan"."tb_plan_next" SELECT * FROM "dbPlan"."tb_plan";

作为代码，这按预期工作，但是dbPlan".tb_plan"包含大约 300 万条记录，因此在大约 20 分钟内完成.这太长了，并且会影响其他进程.

As code this works as expected, however "dbPlan"."tb_plan" contains around 3 million records and therefore completes in around 20 minutes. This is too long and has a knock on effects on other processes.

将所有约束、索引和数据准确复制到表 2 中很重要.

It's important that all constraints, indexes and data are copied exactly to table 2.

我曾尝试删除表并重新创建它，但这并没有提高速度.

I had tried dropping the table and re-creating it, however this did not improve speed.

DROP TABLE IF EXISTS "dbPlan"."tb_plan_next";
CREATE TABLE "dbPlan"."tb_plan_next" (LIKE "dbPlan"."tb_plan" INCLUDING ALL);
INSERT INTO "dbPlan"."tb_plan_next" SELECT * FROM "dbPlan"."tb_plan";

有没有更好的方法来实现这一目标?

Is there a better method for achieving this?

我正在考虑创建表，然后创建索引作为第二步.

I am considering creating the table and then creating indexes as a second step.

推荐答案

PostgreSQL 没有提供一种非常优雅的方式来执行此操作.您可以使用带有 -t 和 --section= 的 pg_dump 来转储表的前数据和后数据.然后，您将重播前数据以创建表结构和检查约束，然后从您获取数据的任何位置加载数据，然后重播后数据以添加索引和 FK 约束.

PostgreSQL doesn't provide a very elegant way of doing this. You could use pg_dump with -t and --section= to dump the pre-data and post-data for the table. Then you would replay the pre-data to create the table structure and the check constraints, then load the data from whereever you get it from, then replay the post-data to add the indexes and FK constraints.

这篇关于克隆 Postgres 表，包括索引和数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！