Repartitioning old data

[ImperiousEnterpriseLLC]

I have a question regarding the creation of a table and enabling native partitioning on it. Then detaching old partitions and finally deleting them. What I would like to understand is, what is the process of after reimporting old data to the parent table, how do I trigger a recreation for older partitions? So for example say if I detached and deleted partitions ab_r2021q1 and ab_r2021q2 from parent table ab but kept partitions ab_r2021q3 and ab_r2021q4 because they were less than 6 months old. Then on a future date after the creation of new partition ab_r2022q1, reimported data from the deleted ab_r2021q1 back to the parent table ab, is possible to recreate partition ab_r2021q1 with run_maintenance_proc()?

[keithf4]

If I'm understanding, you're wanting to drop old partitions via retention then add them back? Not sure I understand the reasoning for that, but if that's the design pattern you want, I would suggest that you leave the retention settings at their default so that the child table is simply detached vs getting fully dropped. Then if you need to add it back again later, you can simply reattach that child.

pg_partman never automatically recreates old partitions after a partition set has already been made. It is only worried about future table creation. If you need to do that, you will have to do so manually.

[ImperiousEnterpriseLLC]

@keithf4 Thanks for your response.

If I'm understanding, you're wanting to drop old partitions via retention then add them back?

Exactly. So I that I can basically free up space from the db and if I need analytic reimport it back to the database.

I would suggest that you leave the retention settings at their default so that the child table is simply detached vs getting fully dropped.

Currently I have retention = '6 months' and retention_keep_table=true so that when I run the maintenance proc, the table isn't dropped merely detached.

pg_partman never automatically recreates old partitions after a partition set has already been made. It is only worried about future table creation. If you need to do that, you will have to do so manually.

I'll keep this in mind.

[keithf4]

If you don't want to keep the detached child tables in the database, you can also set them to be moved into a different schema with the retention_schema option in the config table, then use dump_partition.py python script to dump the tables in that schema out with a hash file.

[keithf4]

Also just FYI, if you are using the quarterly partitioning pattern right now, that is going away in 5.x. You can still partition in 3 month intervals, but it's just a generalized 3 month interval, not the special q# pattern that had existed before. PostgreSQL still never fully supported that time interval in its functions, so it was really hacky to keep supporting it.

[ImperiousEnterpriseLLC]

If you don't want to keep the detached child tables in the database, you can also set them to be moved into a different schema with the retention_schema option in the config table, then use dump_partition.py python script to dump the tables in that schema out with a hash file.

Currently I have installed aws_s3 export tool to my rds instance so export to s3 as csv is just stored_procedure call now.

Also just FYI, if you are using the quarterly partitioning pattern right now, that is going away in 5.x. You can still partition in 3 month intervals, but it's just a generalized 3 month interval, not the special q# pattern that had existed before. PostgreSQL still never fully supported that time interval in its functions, so it was really hacky to keep supporting it.

I'll check with rds to see when they will update pg_partman to the latest version.

[keithf4]

Version 5.x isn't out yet. It's still in beta. Just a heads up.

[ImperiousEnterpriseLLC]

Thanks for all of your insight. I'm going to mark this question as answered.