Search code examples
postgresqlperformanceclone

Cloning a Postgres table, including indexes and data

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";

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.

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.

Solution

  • 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.