Convert Existing Table to a Partition Table in PG10

From: Clifford Snow <clifford(at)snowandsnow(dot)us>
To: pgsql-general(at)postgresql(dot)org
Subject: Convert Existing Table to a Partition Table in PG10
Date: 2018-06-23 23:06:19
Message-ID: CADAoPLp9yGEF=o=E0DpTw2BKt=iT1ETwh-pcj2=2+gQsBPExsg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a large table that needs converting to the native partitioning
available in PG10. I'm looking for a strategy to safely move the data. I
was considering a CREATE TABLE new LIKE old PARTITION ON(my_timestamp).
But I figured postgres would execute that before I could create the child
tables. Does the new table and all of it;s child tables need to be created
first and then move the data over?

If I use the strategy of creating the new table and child tables, is it
faster to directly populate the child tables with data or copy the data in
to the new table and let the native partitioning move the data to the
correct child partition?

One other question. My table gets updated every minute. I use a trigger to
add a subset of some of the data to another table. Will that still work?

Thanks in advance,
Clifford

--
@osm_seattle
osm_seattle.snowandsnow.us
OpenStreetMap: Maps with a human touch

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ravi Krishna 2018-06-24 01:42:32 Re: Convert Existing Table to a Partition Table in PG10
Previous Message Andres Freund 2018-06-22 19:07:59 Re: Can PostgreSQL create new WAL files instead of reusing old ones?