How to migrate from inheritance-based partitioning to declarative partitioning in PostgreSQL

Partitioning is one of the coolest features in the latest PostgreSQL versions.
PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature.

This article provides a guide to move from inheritance based partitioning to declarative partitioning, using the native features found in PostgreSQL 11+.

The process itself moves the child tables, detaching them from the inheritance partition and attaching to the new declarative partitioned table. Note these instructions do not cover moving to declarative partitioning on PostgreSQL 10 since some key features that make this migration easier were not yet implemented.
Also, this procedure assumes that the parent table in the inheritance partitioning is empty.
The following steps are tested on a PostgreSQL 12 instance, but can also be used on PostgreSQL 11.

Initial notes used in this article

Suppose we have a table partitioned with the old method of inheritance, with child tables, triggers, constraints and so on, as in the following example:

sales=# \d+ orders
                                               Table "public.orders"
   Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 product_id | integer                     |           | not null |         | plain    |              |
 address    | text                        |           | not null |         | extended |              |
 order_time | timestamp without time zone |           | not null |         | plain    |              |
Triggers:
    orders_insert_trigger BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION orders_insert()
Child tables: orders_2018,
              orders_2019,
              orders_2020
Access method: heap

A child table looks like this:

sales=# \d+ orders_2018
                                            Table "public.orders_2018"
   Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 product_id | integer                     |           | not null |         | plain    |              |
 address    | text                        |           | not null |         | extended |              |
 order_time | timestamp without time zone |           | not null |         | plain    |              |
Indexes:
    "orders_2018_idx" btree (order_time)
Check constraints:
    "orders_2018_order_time_check" CHECK (order_time >= '2018-01-01'::date AND order_time < '2019-01-01'::date)
Inherits: orders
Access method: heap

Overview of the steps to move child partitions

The way we recommend moving this partitioning schema to a declarative one is by following these steps:

1. Create a new table with the same columns as the parent table from the inheritance schema
2. For each child table in the inheritance schema perform these steps
   • Detach the child table from its parent using ALTER TABLE with NO INHERIT
   • Attach the former child table to the new declarative partition using the ATTACH PARTITION keyword
3. Create needed indexes on the new parent table. Also views, functions and other objects
4. Drop the original parent table used with inheritance partitioning
5. Rename the new parent table so it replaces the old one

Preparing for the migration to declarative partitioning

Before starting the migration process, it’s important to halt any processes that may be automatically maintaining your partitions sets (creating new child tables and/or applying retention policies).
Make sure you disable all maintenance jobs that could perform changes directly or indirectly on the set of tables to migrate, be that through cronjobs or execution of tools like pg_partman.

As first step, we suggest to take a logical backup at database level of the table partitioned through inheritance and all the child tables. For example, you could run something like that:

pg_dump -F c -f sales.dmp sales -v

This is not mandatory, but it’s strongly suggested in order to be able to restore the data in case something goes wrong while performing the next steps.

Initial step

Next, we need to create a new parent table using native partitioning, taking care to use the same definition of the old parent table.
We have to specify the column we want to use as the partition key in the PARTITION BY clause. This choice should be natural, once we have checked the constraints on the child tables.

In the example above the constraints are based on the order_time field. So we can partition FOR RANGE using order_time as the partition key, in this way:

CREATE TABLE orders_part (
     product_id    INT NOT NULL,
     address       TEXT NOT NULL,
     order_time    TIMESTAMP NOT NULL
 ) PARTITION BY RANGE (order_time);

Moving child tables to the new declarative schema

It is a good idea to take the next steps in a single transaction. By doing so, if you run into any issues before you’ve completed the migration process, you can simply rollback and return to the state your database was before the transaction started.
So, please, run:

BEGIN;

The first major step in this migration process now is to remove all the child tables from the old parent, using the NO INHERIT clause, using the following statement in the case of our example above:

ALTER TABLE orders_2018 NO INHERIT orders;

This has to be done for each child table.
Now we need to insert the boundary values for the existing child tables using the ATTACH PARTITION command in the declarative partitioning, as below:

ALTER TABLE orders_part
ATTACH PARTITION orders_2018 
FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00');

Again, we need to perform such a step for each child table for our declarative partitioning.

You may also need to create indexes on the new parent table. From PostgreSQL 11 this can be done by adding the index only once for the partitioned table and it automatically applies to all partitions, existing and future.
In this example, all the child-tables already had the required index and PostgreSQL was intelligent enough not to create a new index similar to another index already existing. So, it will suffice just creating an index on the partitioned table, so that the optimizer knows about it.

CREATE INDEX orders_time ON orders (order_time);

If you have some other SQL objects (such as views, constraints, etc…) on the inheritance-based parent table, don’t forget to recreate them on the new parent table.

And remember that if you still have data in the inheritance parent table you will have to move those rows separately. You can set a DEFAULT partition for that, although that’s discouraged. Instead we suggest finding such rows and creating new child tables which they can fit in. You’ll have to move them manually to the appropriate partition.

Finally, you can drop the inheritance parent table and all the objects created on it, and rename the new partitioned table with the same name of the inheritance parent, in order that all the queries, functions, etc. continue to work properly on the new partitioned table instead of on the old one:

DROP TABLE orders CASCADE;
ALTER TABLE orders_part RENAME TO orders;

At this point you can drop the constraints on the child tables:

ALTER TABLE orders_2018 DROP CONSTRAINT orders_2018_order_time_check;

Run the \d+ command on the parent table and on the individuals partitions to check that everything went fine:

sales_part=# \d+ orders
                                         Partitioned table "public.orders"
   Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description
------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 product_id | integer                     |           | not null |         | plain    |              |
 address    | text                        |           | not null |         | extended |              |
 order_time | timestamp without time zone |           | not null |         | plain    |              |
Partition key: RANGE (order_time)
Indexes:
    "orders_time" btree (order_time)
Partitions: orders_2018 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00'),
            orders_2019 FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2020-01-01 00:00:00'),
            orders_2020 FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2021-01-01 00:00:00')

The migration process should be completed now but don’t forget to commit your work:

COMMIT;

A special “Thank you” to my new colleague Ashutosh Bapat, who wrote this blog article, from which I took a cue and that I adapted to PostgreSQL 12.