Running run_maintenance fails when table was migrated to a new db schema

[Rapol]

I inherited a project that uses partman_pg and Im currently trying to update the retention policy.

UPDATE partman.part_config SET retention_keep_table = false, retention = '6 month'
WHERE parent_table = 'x.students';

After making that update I then run

SELECT partman.run_maintenance(p_analyze := false);

but fails with:

ERROR:  Child table given does not exist (x.students_p2023_08_16)

I then check the partitions

SELECT partman.show_partitions('x.students');

The partitions are actually in the public schema

(public,students_p2022_12_25)

I assume that the partition was created in public schema and then migrated to the x schema. I do know that this migration from public to x schema happened at some point.

So the question is, how can I update the pg partman partition to point to x instead of public schema? As far as I can say, the partitioning is working in the x schema. I can see child tables with data in it and also pgadmin does shows that it is a partitioned table.

Is the best way to solve this is to recreate the partition?

[keithf4]

At this time, pg_partman does not support the parent table existing in a different schema than the child tables. So you can fix this in one of two ways:

1. Move all the child tables into the x schema. See the ALTER TABLE ... SET SCHEMA ... command https://www.postgresql.org/docs/15/sql-altertable.html. You will have to do this for every child table. If your application is expecting this table to exist in the x schema, this is likely the best way to go.
2. Move the parent table to the public schema using the same ALTER TABLE command. This is the technically simpler fix, but you'll then have to ensure anyone using this table knows that the schema changed.

How it got like this, I wouldn't be able to tell you. One thing to double-check would be to see if the retention_schema column in part_config was set to move any of the child tables to a different schema once the retention period was met.

[Rapol]

Thanks for the quick response! This is really helpful. After checking again I realize that the partitions are indeed in public schema. In pgadmin the parent table appears under the x schema hierarchy tree and the partitions appear under that it if I expand it but if I check a partition property (right click properties) it says public schema. Seeing the partitions under the table in x schema made me believe it was in the x schema, whoops should have double checked that.

To answer how it got like this, I know for a fact that at some point the parent table got moved by us to the x schema, it was not pg_partman. They just didnt realize it was also need to move the partitions. Also,retention_schema is null.

I should be able to fix it by doing option 1.