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!
