Showing posts from November, 2017

Upgrading inheritance partitioning made easy.

An alternative way to upgrade to PostgreSQL 10 native partitioning using ALTER TABLEs and PLPGSQL.Today's post is a bit spontaneous, because a few days ago I read the post about PostgreSQL's new native table partitioning explaining how to migrate from an old inheritance partitioning to the new one.

Author of the post proposed using pg_dump and pg_restore utilities. This way is quite simple and requires minimal effort. However, in some instances, especially in case of large partitions, dumping partition's data, restoring and creating indexes might take a long time.

In this post, I would like to offer an alternative way for partitioning upgrade. This method based on some ALTER commands wrapped into PLPGSQL procedure. Of course, I should note this technique only work if you already upgraded to PostgreSQL 10.