partition_data.py not included in postgresql-12-partman?

[dtseiler]

This might be an issue with the Ubuntu packager, but here goes.

Installing postgresql-12-partman on Ubuntu 18.04 LTS, and there is no partition_data.py under /usr/lib/postgresql/12/bin. There is dump_partition.py. I tested an install with postgresql-10-partman and both scripts are there as expected. There are a few other differences as well but missing partition_data.py was the most obvious:

$ ls -1 /usr/lib/postgresql/10/bin/*.py
/usr/lib/postgresql/10/bin/check_unique_constraint.py
/usr/lib/postgresql/10/bin/dump_partition.py
/usr/lib/postgresql/10/bin/partition_data.py
/usr/lib/postgresql/10/bin/reapply_constraints.py
/usr/lib/postgresql/10/bin/reapply_foreign_keys.py
/usr/lib/postgresql/10/bin/reapply_indexes.py
/usr/lib/postgresql/10/bin/undo_partition.py
/usr/lib/postgresql/10/bin/vacuum_maintenance.py

$ ls -1 /usr/lib/postgresql/12/bin/*.py
/usr/lib/postgresql/12/bin/check_unique_constraint.py
/usr/lib/postgresql/12/bin/dump_partition.py
/usr/lib/postgresql/12/bin/reapply_indexes.py
/usr/lib/postgresql/12/bin/vacuum_maintenance.py

[dtseiler]

Installed package is postgresql-12-partman/bionic-pgdg,now 4.3.0-1.pgdg18.04+1 amd64

[dtseiler]

Hmm it's missing in PG11 as well:

$ ls -1 /usr/lib/postgresql/11/bin/*.py
/usr/lib/postgresql/11/bin/check_unique_constraint.py
/usr/lib/postgresql/11/bin/dump_partition.py
/usr/lib/postgresql/11/bin/reapply_indexes.py
/usr/lib/postgresql/11/bin/vacuum_maintenance.py

$ apt list --installed | grep partman

postgresql-11-partman/bionic-pgdg,now 4.3.0-1.pgdg18.04+1 amd64 [installed]

[dtseiler]

Ran dpkg -L on all 3 versions and it confirms what I've stated. postgresql-10-partman includes all those files but 11 and 12 only have the 4 listed above.

[keithf4]

This is intentional. They have been replaced by procedures in 11+. Please see the documentation for the scripts https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md#scripts

[dtseiler]

Ah my mistake (again). I'll check out that function.

[dtseiler]

https://github.com/pgpartman/pg_partman/blob/master/sql/functions/partition_data_time.sql#L103

p_source_table is not set

Should that read "p_source_table is set"?

I'm trying to understand what the process is to go from an unpartitioned table with data to a native-partitioned table managed by partman. What references I've seen here start with a native-partitioned table already, and create_parent() requires native partitioning already have a native partitioned table.

Do I create a new partitioned table manually similar to the source table? Indexes and everything? Then set it as a parent and then call partition_data_proc()?

[keithf4]

Yes, you have to first create the new parent table as a native parent. Then run create_parent on the new native table. You can then use the partition_data_*() functions with the p_source_table parameter to tell it where the original data is. Note that will MOVE the data, not copy it

From the doc for partition_data_id/time()

p_source_table - This option can be used when you need to move data into a natively partitioned set. Pass a schema qualified tablename to this parameter and any data in that table will be MOVED to the partition set designated by p_parent_table, creating any child tables as needed.

If you're trying to avoid extensive downtime, you could potentially make the original, unpartitioned table the "default" table in the new native set. Would then just need a smaller DDL transaction to rename and attach things appropriately.

I realize I really do need to do another howto doc for native partitioning setup. Just haven't had the time.

[dtseiler]

Thanks for the update. Yeah the key is minimal downtime, which is what made partitioning with "partman" type great. I'll try to sort out what you proposed here with the default table and renaming method.

Let me know if you need help testing as well.

[dtseiler]

Closing issue since it was PEBKAC.

[dtseiler]

@keithf4 Would I first have to run create_parent() on the partitioned table, and then detach the default partition from there and then attach my source table as default partition? I tried attaching source table prior to create_parent() but then create_parent() fails because the default partition already exists.

[dtseiler]

I think I've got a basic process working with this script: https://gist.github.com/dtseiler/febe7971723a21d8998c171776d3b140

Is this close to what you'd suggest?

[dtseiler]

I tried attaching source table prior to create_parent() but then create_parent() fails because the default partition already exists.

@keithf4 would you say this is a bug? I can open an issue if you agree.

[keithf4]

Not necessarily a bug. It always creates a default partition. So you'll just have to drop the one it creates for you then add your source table. Then once you've emptied the table out, you can just rename that old table to the "default" name.

[dtseiler]

Fair enough. Perhaps a feature request would be appropriate for create_parent() to recognize pre-existing default partitions, and/or allow me to specify an existing table to be set as a default partition?

[keithf4]

Perhaps as a feature when non-native partitioning finally gets dropped. The parameter list on create_parent() is already a lot longer than I'd like it to be.