Is there any doc for migration to partman for native partitioning

[bidwalkarameya]

https://github.com/pgpartman/pg_partman/blob/master/doc/migration_to_partman.md

"This document is an aid for migrating an existing partitioned table set to using pg_partman. Please note that at this time, this guide is only for non-native, trigger-baed partitioning. Documentation for native partitioning is in the works, but it will be mostly focused on PostgreSQL 11 since 10 was very limited in its partitioning support. For now, the easiest way to migrate to a natively partitioned table is to create a brand new table and copy/move the data."

As stated above for natively partitioned table is to create a brand new table and copy/move the data. have we found a new way to do it ?

[keithf4]

That document is for migrating an existing non-native partition set to partman. I have a working document to migrate an existing, native partition set to pg_partman in the works here.

https://github.com/pgpartman/pg_partman/blob/5.0.0-beta/doc/migrate_to_partman.md

As long as it's already natively partitioned, there's no need to copy/move the data to a new table. Please let me know if this works for you.

[keithf4]

Just a note that that document is specifically for 5.0.0. There is a change in the child table suffix naming pattern, so please be aware of that if you are trying to use this with 4.x.

[bidwalkarameya]

Thank you @keithf4 for specifying the version .We are currently using 4.6.2.

We are currently stuck in a situation (human negligence ) where in order to migrate an existing native partition set to partman . partman partitioning was implemented on the same table instead of creating a new/separate table .Now we have a situation where both native partitions and partitions created by partman exist in same table . In order to match the child table suffix naming pattern we renamed the originally native partitions to have the name similar to those of partman. Along with constraints and indexes.

Now when we execute the run_maintenance functions its completes successfully but does not create new partitions .No error message is reported . We have infinite_time_partitions set to true and have also tried playing along with premake value.

Any suggestion / solution you would have for this?

[keithf4]

That's pretty much what that document says to do, so you're on the right path.

Would have to see the schema of the partition set (\d+ parent_tablename output) and the entire configuration table entry for it to try and help any further.

[bidwalkarameya]

yes true .Any ETA you have for 5.0.0 release ? We are using RDS with postgres 14.5 was thinking on lines how fast we can move up .

Original table

db=> \d+ foo.bar;
                                           Partitioned table "foo.bar"
            Column             |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
-------------------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id                            | integer                  |           | not null |         | plain    |             |              |
 foo_id                    | integer                  |           |          |         | plain    |             |              |
 start_time                    | timestamp with time zone |           | not null |         | plain    |             |              |
 end_time                      | timestamp with time zone |           |          |         | plain    |             |              |
 foo_requested_time             | timestamp with time zone |           |          |         | plain    |             |              |
 foo_type                   | character varying        |           |          |         | extended |             |              |
 version                       | integer                  |           | not null |         | plain    |             |              |
Partition key: RANGE (start_time)
Indexes:
    "bar_pkey" PRIMARY KEY, btree (id, start_time)
    "bar_start_time_idx" btree (start_time)
Partitions: bar_p2023_01 FOR VALUES FROM ('2023-01-01 00:00:00+00') TO ('2023-02-01 00:00:00+00'),
            bar_p2023_02 FOR VALUES FROM ('2023-02-01 00:00:00+00') TO ('2023-03-01 00:00:00+00'),
            bar_p2023_03 FOR VALUES FROM ('2023-03-01 00:00:00+00') TO ('2023-04-01 00:00:00+00'),
            bar_p2023_04 FOR VALUES FROM ('2023-04-01 00:00:00+00') TO ('2023-05-01 00:00:00+00'),
            bar_p2023_05 FOR VALUES FROM ('2023-05-01 00:00:00+00') TO ('2023-06-01 00:00:00+00'),
            bar_p2023_06 FOR VALUES FROM ('2023-06-01 00:00:00+00') TO ('2023-07-01 00:00:00+00'),
            bar_y2016 FOR VALUES FROM ('2014-12-31 23:00:00+00') TO ('2016-12-31 23:00:00+00'),
            bar_y2017 FOR VALUES FROM ('2016-12-31 23:00:00+00') TO ('2017-12-31 23:00:00+00'),
            bar_y2018 FOR VALUES FROM ('2017-12-31 23:00:00+00') TO ('2018-12-31 23:00:00+00'),
            bar_y2019 FOR VALUES FROM ('2018-12-31 23:00:00+00') TO ('2019-12-31 23:00:00+00'),
            bar_y2020_q1 FOR VALUES FROM ('2019-12-31 23:00:00+00') TO ('2020-03-31 22:00:00+00'),
            bar_y2020_q2 FOR VALUES FROM ('2020-03-31 22:00:00+00') TO ('2020-06-30 22:00:00+00'),
            bar_y2020_q3 FOR VALUES FROM ('2020-06-30 22:00:00+00') TO ('2020-09-30 22:00:00+00'),
            bar_y2020_q4 FOR VALUES FROM ('2020-09-30 22:00:00+00') TO ('2020-12-31 23:00:00+00'),
            bar_y2021_m01 FOR VALUES FROM ('2021-01-01 00:00:00+00') TO ('2021-02-01 00:00:00+00'),
            bar_y2021_m02 FOR VALUES FROM ('2021-02-01 00:00:00+00') TO ('2021-03-01 00:00:00+00'),
            bar_y2021_m03 FOR VALUES FROM ('2021-03-01 00:00:00+00') TO ('2021-04-01 00:00:00+00'),
            bar_y2021_m04 FOR VALUES FROM ('2021-04-01 00:00:00+00') TO ('2021-05-01 00:00:00+00'),
            bar_y2021_m05 FOR VALUES FROM ('2021-05-01 00:00:00+00') TO ('2021-06-01 00:00:00+00'),
            bar_y2021_m06 FOR VALUES FROM ('2021-06-01 00:00:00+00') TO ('2021-07-01 00:00:00+00'),
            bar_y2021_m07 FOR VALUES FROM ('2021-07-01 00:00:00+00') TO ('2021-08-01 00:00:00+00'),
            bar_y2021_m08 FOR VALUES FROM ('2021-08-01 00:00:00+00') TO ('2021-09-01 00:00:00+00'),
            bar_y2021_m09 FOR VALUES FROM ('2021-09-01 00:00:00+00') TO ('2021-10-01 00:00:00+00'),
            bar_y2021_m10 FOR VALUES FROM ('2021-10-01 00:00:00+00') TO ('2021-11-01 00:00:00+00'),
            bar_y2021_m11 FOR VALUES FROM ('2021-11-01 00:00:00+00') TO ('2021-12-01 00:00:00+00'),
            bar_y2021_m12 FOR VALUES FROM ('2021-12-01 00:00:00+00') TO ('2022-01-01 00:00:00+00'),
            bar_y2022_m01 FOR VALUES FROM ('2022-01-01 00:00:00+00') TO ('2022-02-01 00:00:00+00'),
            bar_y2022_m02 FOR VALUES FROM ('2022-02-01 00:00:00+00') TO ('2022-03-01 00:00:00+00'),
            bar_y2022_m03 FOR VALUES FROM ('2022-03-01 00:00:00+00') TO ('2022-04-01 00:00:00+00'),
            bar_y2022_m04 FOR VALUES FROM ('2022-04-01 00:00:00+00') TO ('2022-05-01 00:00:00+00'),
            bar_y2022_m05 FOR VALUES FROM ('2022-05-01 00:00:00+00') TO ('2022-06-01 00:00:00+00'),
            bar_y2022_m06 FOR VALUES FROM ('2022-06-01 00:00:00+00') TO ('2022-07-01 00:00:00+00'),
            bar_y2022_m07 FOR VALUES FROM ('2022-07-01 00:00:00+00') TO ('2022-08-01 00:00:00+00'),
            bar_y2022_m08 FOR VALUES FROM ('2022-08-01 00:00:00+00') TO ('2022-09-01 00:00:00+00'),
            bar_y2022_m09 FOR VALUES FROM ('2022-09-01 00:00:00+00') TO ('2022-10-01 00:00:00+00'),
            bar_y2022_m10 FOR VALUES FROM ('2022-10-01 00:00:00+00') TO ('2022-11-01 00:00:00+00'),
            bar_y2022_m11 FOR VALUES FROM ('2022-11-01 00:00:00+00') TO ('2022-12-01 00:00:00+00'),
            bar_y2022_m12 FOR VALUES FROM ('2022-12-01 00:00:00+00') TO ('2023-01-01 00:00:00+00'),
            bar_default DEFAULT

Current Table:

db=> \d+ foo.bar;
                                           Partitioned table "foo.bar"
            Column             |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
-------------------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id                            | integer                  |           | not null |         | plain    |             |              |
 foo_id                    | integer                  |           |          |         | plain    |             |              |
 start_time                    | timestamp with time zone |           | not null |         | plain    |             |              |
 end_time                      | timestamp with time zone |           |          |         | plain    |             |              |
 foo_requested_time         | timestamp with time zone |           |          |         | plain    |             |              |
 foo_type                   | character varying        |           |          |         | extended |             |              |

 version                       | integer                  |           | not null |         | plain    |             |              |
Partition key: RANGE (start_time)
Indexes:
    "bar_pkey" PRIMARY KEY, btree (id, start_time)
    "bar_start_time_idx" btree (start_time)
Partitions: foo.bar_p2021_01 FOR VALUES FROM ('2021-01-01 00:00:00+00') TO ('2021-02-01 00:00:00+00'),
            foo.bar_p2021_02 FOR VALUES FROM ('2021-02-01 00:00:00+00') TO ('2021-03-01 00:00:00+00'),
            foo.bar_p2021_03 FOR VALUES FROM ('2021-03-01 00:00:00+00') TO ('2021-04-01 00:00:00+00'),
            foo.bar_p2021_04 FOR VALUES FROM ('2021-04-01 00:00:00+00') TO ('2021-05-01 00:00:00+00'),
            foo.bar_p2021_05 FOR VALUES FROM ('2021-05-01 00:00:00+00') TO ('2021-06-01 00:00:00+00'),
            foo.bar_p2021_06 FOR VALUES FROM ('2021-06-01 00:00:00+00') TO ('2021-07-01 00:00:00+00'),
            foo.bar_p2021_07 FOR VALUES FROM ('2021-07-01 00:00:00+00') TO ('2021-08-01 00:00:00+00'),
            foo.bar_p2021_08 FOR VALUES FROM ('2021-08-01 00:00:00+00') TO ('2021-09-01 00:00:00+00'),
            foo.bar_p2021_09 FOR VALUES FROM ('2021-09-01 00:00:00+00') TO ('2021-10-01 00:00:00+00'),
            foo.bar_p2021_10 FOR VALUES FROM ('2021-10-01 00:00:00+00') TO ('2021-11-01 00:00:00+00'),
            foo.bar_p2021_11 FOR VALUES FROM ('2021-11-01 00:00:00+00') TO ('2021-12-01 00:00:00+00'),
            foo.bar_p2021_12 FOR VALUES FROM ('2021-12-01 00:00:00+00') TO ('2022-01-01 00:00:00+00'),
            foo.bar_p2022_01 FOR VALUES FROM ('2022-01-01 00:00:00+00') TO ('2022-02-01 00:00:00+00'),
            foo.bar_p2022_02 FOR VALUES FROM ('2022-02-01 00:00:00+00') TO ('2022-03-01 00:00:00+00'),
            foo.bar_p2022_03 FOR VALUES FROM ('2022-03-01 00:00:00+00') TO ('2022-04-01 00:00:00+00'),
            foo.bar_p2022_04 FOR VALUES FROM ('2022-04-01 00:00:00+00') TO ('2022-05-01 00:00:00+00'),
            foo.bar_p2022_05 FOR VALUES FROM ('2022-05-01 00:00:00+00') TO ('2022-06-01 00:00:00+00'),
            foo.bar_p2022_06 FOR VALUES FROM ('2022-06-01 00:00:00+00') TO ('2022-07-01 00:00:00+00'),
            foo.bar_p2022_07 FOR VALUES FROM ('2022-07-01 00:00:00+00') TO ('2022-08-01 00:00:00+00'),
            foo.bar_p2022_08 FOR VALUES FROM ('2022-08-01 00:00:00+00') TO ('2022-09-01 00:00:00+00'),
            foo.bar_p2022_09 FOR VALUES FROM ('2022-09-01 00:00:00+00') TO ('2022-10-01 00:00:00+00'),
            foo.bar_p2022_10 FOR VALUES FROM ('2022-10-01 00:00:00+00') TO ('2022-11-01 00:00:00+00'),
            foo.bar_p2022_11 FOR VALUES FROM ('2022-11-01 00:00:00+00') TO ('2022-12-01 00:00:00+00'),
            foo.bar_p2022_12 FOR VALUES FROM ('2022-12-01 00:00:00+00') TO ('2023-01-01 00:00:00+00'),
            foo.bar_p2023_01 FOR VALUES FROM ('2023-01-01 00:00:00+00') TO ('2023-02-01 00:00:00+00'),
            foo.bar_p2023_02 FOR VALUES FROM ('2023-02-01 00:00:00+00') TO ('2023-03-01 00:00:00+00'),
            foo.bar_p2023_03 FOR VALUES FROM ('2023-03-01 00:00:00+00') TO ('2023-04-01 00:00:00+00'),
            foo.bar_p2023_04 FOR VALUES FROM ('2023-04-01 00:00:00+00') TO ('2023-05-01 00:00:00+00'),
            foo.bar_p2023_05 FOR VALUES FROM ('2023-05-01 00:00:00+00') TO ('2023-06-01 00:00:00+00'),
            foo.bar_p2023_06 FOR VALUES FROM ('2023-06-01 00:00:00+00') TO ('2023-07-01 00:00:00+00'),
            foo.bar_default DEFAULT

part_config deatils:

parent_table | control | partition_type | partition_interval | constraint_cols | premake | optimize_trigger | optimize_constraint | epoch | inherit_fk | retention | retention_schema | retention_keep_table | retention_keep_index | infinite_time_partitions | datetime_string | automatic_maintenance | jobmon | sub_partition_set_full | undo_in_progress | trigger_exception_handling | upsert | trigger_return_null | template_table | publications | inherit_privileges | constraint_valid | subscription_refresh | drop_cascade_fk
-----------------------------------------+------------+----------------+--------------------+-----------------+---------+------------------+---------------------+-------+------------+-----------+------------------+----------------------+----------------------+--------------------------+-----------------+-----------------------+--------+------------------------+------------------+----------------------------+--------+---------------------+---------------------------------+--------------+--------------------+------------------+----------------------+-----------------
foo.bar| start_time | native | 1 mon | {} | 8 | 4 | 30 | none | t | | | t | t | t | YYYY_MM | on | t | f | f | f | | t | partman.bar_template | | f | t | | f
(1 row)

[keithf4]

So I currently only have a system with 4.7.3 (latest stable version) and I'm not able to reproduce maintenance not working with a matching schema and config table. However not seeing anything in the CHANGELOG that would attribute this being a bug that might've been fixed. The infinite_time_partitions option should allow this to work for you. Are you able to insert any data and see if that gets maintenance working again?

Sorry, you have me stumped on this one at the moment.

[keithf4]

As for the 5.0.0 release date, I don't have anything firmly in place. I'd like to get a public beta test released later this month to get feedback and then go from there.