It's also very relevant for local development and testing.
Yep, which is the original goal of Christoph IIRC.
It may be possible to weaken this restriction somewhat thanks to the upcoming https://kubernetes.io/blog/2024/08/16/kubernetes-1-31-image-volume-source/ feature that permits additional OCI images to be mounted as read-only volumes on a workload. This would still only permit mounting at Pod-creation time, not runtime mounting and unmonuting, but means the base postgres image could be supplemented by mounting additional images for extensions.
I'm really excited about that feature, but it's still in the alpha stage. However, I don't anticipate any issues for the future general availability (GA) release. Regardless, we may need to consider a temporary solution that is compatible with existing Kubernetes and possibly Postgres versions (but that's beyond the purpose of this thread).
For example, one might mount image "postgis-vX.Y.Z" image onto base image "postgresql-16" if support for PostGIS is desired, without then having to bake every possible extension anyone might ever want into the base image. This solves all sorts of messy issues with upgrades and new version releases.
Yep.
But for it to work, it must be possible to tell postgres to look in _multiple places_ for extension .sql scripts and control files. This is presently possible for modules (dynamic libraries, .so / .dylib / .dll) but without a way to also configure the path for extensions it's of very limited utility.
Agree.
So IMO this should be a _path_ to search for extension control files and SQL scripts.
I like this. I also prefer the name `extension_search_path`.
For safety, it might make sense to impose the restriction that if an extension control file is found in a given directory, SQL scripts will also only be looked for in that same directory. That way there's no chance of accidentally mixing and matching SQL scripts from different versions of an extension if it appears twice on the extension search path in different places. The rule for loading SQL scripts would be:
* locate first directory on path contianing matching extension control file * use this directory as the extension directory for all subsequent SQL script loading and running actions
It could work, but it requires some prototyping and exploration. I'm willing to participate and use CloudNativePG as a test bed.
