Dropped pg_partman extension during Postgres upgrade

[jknipp]

While upgrading a Postgres 9.6 instance hosted by Heroku to Postgres 10, we accidentally dropped the pg_partman extension (v3.0.2) and recreated the extension (v4.5.0).

It appears that partitioning is partially working. The trigger to insert data into pre-made tables (daily tables) still exists and works for now - it will presumably stop working once we exhaust the pre-made tables. However, the partition is no longer recognized and the run_maintenance command no longer works to pre-make tables.

select show_partitions('public.api_requests');
ERROR:  Given parent table not managed by pg_partman: public.api_requests
CONTEXT:  PL/pgSQL function show_partitions(text,text,boolean) line 41 at RAISE

What is the best path forward for us to fix partitioning?

[keithf4]

There's nothing technically tying the partitioned table to any specific entry in the config table other than a matching parent table name.
So you just need to get the same configuration entered back into the part_config table that it had before. If you know all the settings you used for the create_parent() call for these tables, you could just create another partitioned table with a different name but the same config. Then either insert another row with the same values but with the old parent_table value, or you could maybe even just update the row for the new table to have the old table's name.

Would recommend testing this out first, but I'm pretty sure this should work.

[keithf4]

Not that I can think of off the top of my head. It looks at the last child table that currently exists and the data in the table to determine whether to create new child tables. And the trigger function is regenerated as needed as well. Could potentially be gaps in the children, but there is a function to fix that. Or if that data is in the parent, you should be able to run the partition_data_*() functions to fix it.

Like I said, though, I would definitely try and test this out first if at all possible. I haven't actually run into this situation before myself.

May also use this as an opportunity to convert to native partitioning? I'd probably recommend getting to at least PG11 for that, though. Any reason you only upgraded to PG10 and not the latest? You'll just be in this same boat again around this time next year when 10 goes EOL.

[jknipp]

@keithf4 The system in question is marked to be sunset, fingers crossed in the next year. This is the first time we have performed an upgrade so we wanted to upgrade with the least amount of risk. There was a bit of an unknown with upgrading a Heroku-managed DB of substantial size (~4TB). If we see this particular app being used beyond the next EOL date, I see us moving to native partitioning.

[jknipp]

Fwiw, because of how Heroku Postgres upgrades work, we still have a copy of all the old settings available in a detached DB.

[keithf4]

Should just be able to insert the rows from that old table into the new one and hopefully things will return to working again.

[jknipp]

Awesome - thanks so much for your help!!