Re: Updating a pre-10 partitioned table to use PG 10 partitioning

On Thu, Jan 11, 2018 at 8:40 PM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:

> On Thu, Jan 11, 2018 at 04:40:30PM +0100, Alban Hertroys wrote:
> > What is a good approach here?
>
> By having a some point an origin and target table in the same cluster,
> you would just need to define things properly. I have not checked, but
> perhaps this is the kind of use cases where pg_partman
> (https://github.com/keithf4/pg_partman/) can become handy? Perhaps
> Keith, who maintains the tool, has some insight on the matter.
> --
> Michael
>

The method that Igor mentioned already in this email thread is currently
the only way I know of to migrate an old trigger-based partition set to a
new natively partitioned one in 10. You can just stick to renaming instead
of doing any drops just to be safe, though. There are, as yet, no
statements to turn an existing table into the parent table of a natively
partitioned set in PG10. You have to create a new table, uninherit the
tables from the old set (either one at a time, or all in one transaction),
attach them to the new parent table, then do some table renaming to get
things back to where they were before.

If you can minimize any queries on the partitioned table, the above can all
be done in a single transaction so the client never knows the difference.
How long it takes will be entirely dependent on any active transactions on
the partition set at the time it runs since all the ALTER TABLE statements
on the parent and all children all require exclusive locks. Highly
recommend doing it all in one single transaction or not at all to avoid any
issues. Also be aware that the native partitioning may not support
everything you were doing in your old trigger based methods.

I do plan on making either a migration script or some HOWTO documentation
for pg_partman. Just need to get the time to do so. Thanks for keeping me
in mind!

Keith