INSERT INTO child SELECT FROM parent WHERE ....;
Once you have fully allocated all the records on the parent table you can
then:
TRUNCATE parent;
Then install the triggers.
The only real downside is that the queries are going to take forever to run.
If you can naturally sub-divide the child tables (say into timestamp ranges)
you might try moving one month at a time just to keep your eye on query
progress. Make use of functions to perform the actual INSERT INTO SELECT;
and probably have it capture and log statistics regarding each "INSERT INTO
SELECT" statement that is fired (data start time, data end time, target
child, number of records, execution start/end times).
David J.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Cédric Villemain
Sent: Monday, April 25, 2011 6:58 AM
To: Phoenix Kiula
Cc: PG-General Mailing List
Subject: Re: [GENERAL] Partitioning an existing table
2011/4/25 Phoenix Kiula <phoenix.kiula@gmail.com>:
> Hi.
>
> The partitioning documentation in PG is very clear on how to partition
> a new table. Create child tables, and have triggers that manage
> INSERT, UPDATE and DELETE commands.
>
> How about doing this with existing massive tables? (Over 120 million
> rows)
>
> I could create a new parent table with child tables, and then INSERT
> all these millions of rows to put them into the right partition. But
> is that recommended?
It will be faster to INSERT directly in the good child tables and you can
make multiple INSERT in parrallel (depend of your IO system and number of
tables)
>
> Thanks
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To
> make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/
PostgreSQL : Expertise, Formation et Support
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
