How To Migrate From Trigger-Based Partitioning...

PostgreSQL 10 introduced native partitioning and more recent versions have continued to improve upon this feature. However, many people set up partition sets before native partitioning was available and would greatly benefit from migrating to it. This article will cover how to migrate a partition set using the old method of triggers/inheritance/constraints to a partition set using the native features found in PostgreSQL 11+. Note these instructions do not cover migrating to PG10 since some key features that make this migration easier were not yet implemented. It is highly recommended to move to PG11 or higher if you want to migrate existing partition sets.

Also note that while this migration article is specific to pg_partman, most of the process can be adapted to any partition sets that you may have made for yourself. For pg_partman users, this requires at least version 4.3.0. The show_partition_info() function was fixed in this version to give the proper boundary values needed for easier migration.

We will be using the partition set generated by the trigger-based test/test-time-daily.sql pgtap test that comes with pg_partman. This is how our partition set currently looks before migration:

\d+ partman_test.time_taptest_table
                                  Table "partman_test.time_taptest_table"
 Column |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 col1   | integer                  |           | not null |         | plain    |              |
 col2   | text                     |           |          |         | extended |              |
 col3   | timestamp with time zone |           | not null | now()   | plain    |              |
Indexes:
    "time_taptest_table_pkey" PRIMARY KEY, btree (col1)
Triggers:
    time_taptest_table_part_trig BEFORE INSERT ON partman_test.time_taptest_table FOR EACH ROW EXECUTE FUNCTION partman_test.time_taptest_table_part_trig_func()
Child tables: partman_test.time_taptest_table_p2020_01_27,
              partman_test.time_taptest_table_p2020_01_28,
              partman_test.time_taptest_table_p2020_01_29,
              partman_test.time_taptest_table_p2020_01_30,
              partman_test.time_taptest_table_p2020_01_31,
              partman_test.time_taptest_table_p2020_02_01,
              partman_test.time_taptest_table_p2020_02_02,
              partman_test.time_taptest_table_p2020_02_03,
              partman_test.time_taptest_table_p2020_02_04,
              partman_test.time_taptest_table_p2020_02_05,
              partman_test.time_taptest_table_p2020_02_06,
              partman_test.time_taptest_table_p2020_02_07,
              partman_test.time_taptest_table_p2020_02_08,
              partman_test.time_taptest_table_p2020_02_09,
              partman_test.time_taptest_table_p2020_02_10,
              partman_test.time_taptest_table_p2020_02_11,
              partman_test.time_taptest_table_p2020_02_12
Access method: heap

Before migration starts, it’s important to halt any processes that may be automatically maintaining your partitions sets (new child table creation and/or retention). If your partition set is managed by pg_partman, it's best to turn off automatic maintenance during this process to avoid any issues. If you are manually calling maintenance directly on this parent set via some other scheduler (cron, etc), ensure that it is turned off there as well.

UPDATE partman.part_config SET automatic_maintenance = 'off' WHERE parent_table = 'partman_test.time_taptest_table';

If this table is also sub-partitioned, turn maintenance off there as well for all sub-parents. For sub-partitioning you should be able to follow all the same processes here, but you will have to work from the lowest level upward and perform the migration on each sub-parent all the way to the top-level parent.

UPDATE partman.part_config_sub SET sub_automatic_maintenance = 'off' WHERE sub_parent = 'partman_test.time_taptest_table';
UPDATE partman.part_config_sub SET sub_automatic_maintenance = 'off' WHERE sub_parent = 'partman_test.time_taptest_table_p2019_12_08';
[...]

Next, we need to create a new parent table using native partitioning since you cannot currently convert an existing table into a native partition parent. Note in this case our original table had a primary key on col1. Since col1 is not part of the partition key, native partitioning does not allow us to declare it as a primary key on the top level table. If you still need this as a primary key, pg_partman provides a template table you can set this on, but it will still not enforce uniqueness across the entire partition set, only on a per-child basis similar to how it worked before native.

Please see the Child Table Property Inheritance section of docs/pg_partman.md for which properties can be set on the native parent and which must be managed via the template table since this varies between PG versions. These include things like indexes, foreign keys and other table properties.

CREATE TABLE partman_test.time_taptest_table_native
    (col1 int, col2 text default 'stuff', col3 timestamptz NOT NULL DEFAULT now())
    PARTITION BY RANGE (col3);

CREATE INDEX ON partman_test.time_taptest_table_native (col3);

Next check what the ownership and privileges on your original table were and ensure they exist on the new parent table. This will ensure all access to the table works the same after the migration. By default with native partitioning, privileges are no longer granted on child tables to provide direct access to them. If you'd like to keep that behavior, pg_partman provides a means to do that by setting the inherit_privileges column in _part_config* (and _part_config_sub* if needed) to true.

\dt partman_test.time_taptest_table
                     List of relations
    Schema    |        Name        | Type  |     Owner
--------------+--------------------+-------+---------------
 partman_test | time_taptest_table | table | partman_owner
(1 row)

\dp+ partman_test.time_taptest_table
                                               Access privileges
    Schema    |        Name        | Type  |          Access privileges          | Column privileges | Policies
--------------+--------------------+-------+-------------------------------------+-------------------+----------
 partman_test | time_taptest_table | table | partman_owner=arwdDxt/partman_owner+|                   |
              |                    |       | partman_basic=arwd/partman_owner   +|                   |
              |                    |       | testing=r/partman_owner             |                   |
(1 row)

ALTER TABLE partman_test.time_taptest_table_native OWNER TO partman_owner;
GRANT SELECT, INSERT, UPDATE, DELETE ON partman_test.time_taptest_table_native TO partman_basic;
GRANT SELECT ON partman_test.time_taptest_table_native TO testing;

It is best to halt all activity on the original table during the migration process to avoid any issues. This can be done by either revoking all permissions to the table temporarily or by taking out an exclusive lock on the parent table and running all of these steps in a single transaction. The transactional method is highly recommended for the simple fact that 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 in before the migration started.

BEGIN;
LOCK TABLE partman_test.time_taptest_table IN ACCESS EXCLUSIVE MODE NOWAIT;

If this is a subpartitioned table, the lock on the top-level parent should lock out access on all child tables as long as you don't use the ONLY clause.

The first major step in this migration process is now to uninherit all the child tables from the old parent. You can use a query like the one below to generate the ALTER TABLE statements to uninherit all child tables from the given parent table. It's best to use generated SQL like this to avoid typos, especially with very large partition sets:

SELECT 'ALTER TABLE '||inhrelid::regclass||' NO INHERIT '||inhparent::regclass||';' FROM pg_inherits WHERE inhparent::regclass = 'partman_test.time_taptest_table'::regclass;

                                              ?column?
-----------------------------------------------------------------------------------------------------
 ALTER TABLE partman_test.time_taptest_table_p2019_12_08 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_09 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_10 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_11 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_12 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_13 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_14 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_15 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_16 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_17 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_18 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_19 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_20 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_21 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_22 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_23 NO INHERIT partman_test.time_taptest_table;
 ALTER TABLE partman_test.time_taptest_table_p2019_12_24 NO INHERIT partman_test.time_taptest_table;

DO NOT RUN THESE STATEMENTS YET. The following query will not work if the child tables are no longer part of the inheritance set.

For any partition sets, even those not managed by pg_partman, the next step is that you need to figure out the boundary values of your existing child tables and feed those to the ATTACH PARTITION command used in native partitioning. Since pg_partman uses set naming patterns for all the partition types it manages, there is a built-in function (show_partition_info()) that can return the boundary values based on the child table's name.

For non-pg_partman partition sets you will have to use some other method to figure out these child boundaries.

Again, we can use some sql to generate statements to re-attach the children to the new parent. As mentioned before, show_partition_info() makes this easy for pg_partman managed partition sets:

SELECT (
    SELECT 'ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION '||x.child_tablename||' FOR VALUES FROM ('||quote_literal(y.child_start_time)||') TO ('||quote_literal(y.child_end_time)||');'
    FROM partman.show_partition_info(x.child_tablename, p_parent_table := 'partman_test.time_taptest_table') y )
FROM (SELECT inhrelid::regclass::text AS child_tablename FROM pg_inherits WHERE inhparent::regclass = 'partman_test.time_taptest_table'::regclass) x;


 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_01_27 FOR VALUES FROM ('2020-01-27 00:00:00-05') TO ('2020-01-28 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_01_28 FOR VALUES FROM ('2020-01-28 00:00:00-05') TO ('2020-01-29 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_01_29 FOR VALUES FROM ('2020-01-29 00:00:00-05') TO ('2020-01-30 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_01_30 FOR VALUES FROM ('2020-01-30 00:00:00-05') TO ('2020-01-31 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_01_31 FOR VALUES FROM ('2020-01-31 00:00:00-05') TO ('2020-02-01 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_02_01 FOR VALUES FROM ('2020-02-01 00:00:00-05') TO ('2020-02-02 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_02_02 FOR VALUES FROM ('2020-02-02 00:00:00-05') TO ('2020-02-03 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_02_03 FOR VALUES FROM ('2020-02-03 00:00:00-05') TO ('2020-02-04 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_02_04 FOR VALUES FROM ('2020-02-04 00:00:00-05') TO ('2020-02-05 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_02_05 FOR VALUES FROM ('2020-02-05 00:00:00-05') TO ('2020-02-06 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_02_06 FOR VALUES FROM ('2020-02-06 00:00:00-05') TO ('2020-02-07 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_02_07 FOR VALUES FROM ('2020-02-07 00:00:00-05') TO ('2020-02-08 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_02_08 FOR VALUES FROM ('2020-02-08 00:00:00-05') TO ('2020-02-09 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_02_09 FOR VALUES FROM ('2020-02-09 00:00:00-05') TO ('2020-02-10 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_02_10 FOR VALUES FROM ('2020-02-10 00:00:00-05') TO ('2020-02-11 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_02_11 FOR VALUES FROM ('2020-02-11 00:00:00-05') TO ('2020-02-12 00:00:00-05');
 ALTER TABLE partman_test.time_taptest_table_native ATTACH PARTITION partman_test.time_taptest_table_p2020_02_12 FOR VALUES FROM ('2020-02-12 00:00:00-05') TO ('2020-02-13 00:00:00-05');

We can now run these two sets of ALTER TABLE statements to first uninherit them from the old trigger-based parent and attach them to the new native parent. After doing so, the old trigger-based parent should have no longer have children:

\d+ partman_test.time_taptest_table
                                  Table "partman_test.time_taptest_table"
 Column |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
--------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 col1   | integer                  |           | not null |         | plain    |              |
 col2   | text                     |           |          |         | extended |              |
 col3   | timestamp with time zone |           | not null | now()   | plain    |              |
Indexes:
    "time_taptest_table_pkey" PRIMARY KEY, btree (col1)
Triggers:
    time_taptest_table_part_trig BEFORE INSERT ON partman_test.time_taptest_table FOR EACH ROW EXECUTE FUNCTION partman_test.time_taptest_table_part_trig_func()
Access method: heap

And our new native parent should have now adopted all its new children:

\d+ partman_test.time_taptest_table_native
                            Partitioned table "partman_test.time_taptest_table_native"
 Column |           Type           | Collation | Nullable |    Default    | Storage  | Stats target | Description
--------+--------------------------+-----------+----------+---------------+----------+--------------+-------------
 col1   | integer                  |           |          |               | plain    |              |
 col2   | text                     |           |          | 'stuff'::text | extended |              |
 col3   | timestamp with time zone |           | not null | now()         | plain    |              |
Partition key: RANGE (col3)
Indexes:
    "time_taptest_table_native_col3_idx" btree (col3)
Partitions: partman_test.time_taptest_table_p2020_01_27 FOR VALUES FROM ('2020-01-27 00:00:00-05') TO ('2020-01-28 00:00:00-05'),
            partman_test.time_taptest_table_p2020_01_28 FOR VALUES FROM ('2020-01-28 00:00:00-05') TO ('2020-01-29 00:00:00-05'),
            partman_test.time_taptest_table_p2020_01_29 FOR VALUES FROM ('2020-01-29 00:00:00-05') TO ('2020-01-30 00:00:00-05'),
            partman_test.time_taptest_table_p2020_01_30 FOR VALUES FROM ('2020-01-30 00:00:00-05') TO ('2020-01-31 00:00:00-05'),
            partman_test.time_taptest_table_p2020_01_31 FOR VALUES FROM ('2020-01-31 00:00:00-05') TO ('2020-02-01 00:00:00-05'),
            partman_test.time_taptest_table_p2020_02_01 FOR VALUES FROM ('2020-02-01 00:00:00-05') TO ('2020-02-02 00:00:00-05'),
            partman_test.time_taptest_table_p2020_02_02 FOR VALUES FROM ('2020-02-02 00:00:00-05') TO ('2020-02-03 00:00:00-05'),
            partman_test.time_taptest_table_p2020_02_03 FOR VALUES FROM ('2020-02-03 00:00:00-05') TO ('2020-02-04 00:00:00-05'),
            partman_test.time_taptest_table_p2020_02_04 FOR VALUES FROM ('2020-02-04 00:00:00-05') TO ('2020-02-05 00:00:00-05'),
            partman_test.time_taptest_table_p2020_02_05 FOR VALUES FROM ('2020-02-05 00:00:00-05') TO ('2020-02-06 00:00:00-05'),
            partman_test.time_taptest_table_p2020_02_06 FOR VALUES FROM ('2020-02-06 00:00:00-05') TO ('2020-02-07 00:00:00-05'),
            partman_test.time_taptest_table_p2020_02_07 FOR VALUES FROM ('2020-02-07 00:00:00-05') TO ('2020-02-08 00:00:00-05'),
            partman_test.time_taptest_table_p2020_02_08 FOR VALUES FROM ('2020-02-08 00:00:00-05') TO ('2020-02-09 00:00:00-05'),
            partman_test.time_taptest_table_p2020_02_09 FOR VALUES FROM ('2020-02-09 00:00:00-05') TO ('2020-02-10 00:00:00-05'),
            partman_test.time_taptest_table_p2020_02_10 FOR VALUES FROM ('2020-02-10 00:00:00-05') TO ('2020-02-11 00:00:00-05'),
            partman_test.time_taptest_table_p2020_02_11 FOR VALUES FROM ('2020-02-11 00:00:00-05') TO ('2020-02-12 00:00:00-05'),
            partman_test.time_taptest_table_p2020_02_12 FOR VALUES FROM ('2020-02-12 00:00:00-05') TO ('2020-02-13 00:00:00-05')

Next is to swap the names of your old trigger-based parent and the new native parent.

ALTER TABLE partman_test.time_taptest_table RENAME TO time_taptest_table_old;
ALTER TABLE partman_test.time_taptest_table_native RENAME TO time_taptest_table;

PG11+ supports the feature of a default partition to catch any data that doesn't have a matching child. pg_partman does create this default partition for you when it sets up new partition sets, but since we're migrating we'll have to create one manually if desired. If your table names are particularly long, ensure that adding the "_default" suffix doesn't get truncated unexpectedly. The suffix isn't required for functionality, but provides good context for what the table is for, so it's better to shorten the table name itself to fit the suffix.

CREATE TABLE partman_test.time_taptest_table_default (LIKE partman_test.time_taptest_table INCLUDING ALL);
ALTER TABLE partman_test.time_taptest_table ATTACH PARTITION partman_test.time_taptest_table_default DEFAULT;

For pg_partman, a template table is used to handle certain inheritance properties, so this table will need to be created. This table can be located in any schema and named whatever you wish, but by default it is created in the same schema that pg_partman was installed to and just prepends "template*" onto the name of the current table including its schema. Also it's good to set the owner to be the same as the parent table owner. Again, if your table names are particularly long, make sure you account for any name truncation if it occurs.

As mentioned above, view the Child Table Property Inheritance section of the documentation for what properties are managed via this template table depending on your version of PostgreSQL.

CREATE TABLE partman.partman_test_time_taptest_table (LIKE partman_test.time_taptest_table);
ALTER TABLE partman.partman_test_time_taptest_table OWNER TO partman_owner;

Because we had a primary key on our original table, and we can't set that on the native parent table, this template table can manage that for us.

ALTER TABLE partman.partman_test_time_taptest_table ADD PRIMARY KEY (col1);

And lastly, for pg_partman, you will need to update the _part_config* (and _part_config_sub* if subpartitioned) tables to account for now being natively partitioned. The value for _template_table* must match the name of the table that was created above.

UPDATE partman.part_config SET partition_type = 'native', template_table = 'partman.partman_test_time_taptest_table' WHERE parent_table = 'partman_test.time_taptest_table';

If you've run this process inside a transaction, be sure to commit your work now:

COMMIT;

This should complete the migration process. If you'd like general calls to run_maintenance() with pg_partman to work with this partition set again, be sure to update the _part_config* table to set _automatic_maintenance* back to "on".

Native partitioning has long been a must-have feature for PostgreSQL and with its latest 3 major releases, the partitioning capabilities keep growing. So, again, if possible we highly recommend taking the time to migrate to native partitioning to take advantage of these benefits!

Latest Articles

How To Migrate From Trigger-Based Partitioning To Native in PostgreSQL

Related Articles