Installing partman via SQL scripts as a schema (not as an extension)

[ogurashi]

Hello! We're investigating cloud providers, and are potentially going to settle on one that does not support the pg_partman extension.

During testing, I was able to install pg_partman locally using the files in the sql directory by

• Adding a line creating the schema “partman” at the top of tables/tables.sql
• Changing all occurrences of @extschema@ to “partman”
• Removing the references to pg_catalog.pg_extension_config_dump (Only found in tables/tables.sql )

We then ran the sql code in the sql directory one by one.

Upgrading the functions using the upgrade scripts also works by running the sql in the upgrade script, and we're able to partition data and run the maintenance and partition_data procedures successfully.

Based on our assessment, installing the extension as a set of functions under a particular schema impacts

1. The background worker
2. Tables with public permissions - might have their permissions revoked during upgrades

For point 1. I think we can circumvent this issue by scheduling cron jobs to run the maintenance procedure.
For point2. I believe we're not currently using any PUBLIC permission tables or schemas

Are we missing something critical here? Or will it be fine to just install in this way?

[keithf4]

My development model is entirely around using the extension system, so that is how things are expected to work. So unfortunately I cannot really provide solid guidance on how to do installation outside that system. If I did, then I would be expected to support it and I do not want to do that. So far the extension system allows the method you're using here to work in most cases, but there is no guarantee that will be the case for the future. For instance, I'm not sure how the 5.0 upgrade will work with this.

The other big issue here is, unless you're carefully tracking it somehow, you won't know what version of the extension is installed.

So the answer here is I cannot provide any guarantees that this will work.

[keithf4]

Have you tried requesting your cloud provider to include it? Google Cloud and AWS RDS have it included. The latter actually took quite a while to get added, but people's persistence in requesting it finally got them to include it.

[ogurashi]

We have! Thank you.