Controlling the location where a Postgres instance will look for extension `.control` files

Question

Before I set off to convert some of the PostgreSQL code that I share between projects from migrations into extensions proper, I somehow become convinced that it was feasible to set up the path where Postgres will look for extension .control files per cluster. However, I am now afraid that, during my pre-takeoff checks, I might have confused dynamic_libray_path with SHAREDIR (retrievable via pg_config --sharedir).

I was counting on (something like) dynamic_library_path also working for extensions, because without a path that I can add custom locations to:

1. it's a bit difficult to install extensions at all from a CI deployment, because it requires a more complicated permission setup; and
2. and rolling out experimental extension changes on the same server becomes tricky to impossible if all my postgres clusters/processes have to share the same SHAREDIR.

To recap what I do know so far (to keep people from just copying back the official docs to me):

• dynamic_libray_path can contain a $libdir entry, which refers to pg_config --pkglibdir, but can also contain custom entries.
• However, the documentation mentions the dynamic_libray_path GUC only in the context of “dynamically loadable module[s]”.
• Unlike CREATE FUNCTION and LOAD, CREATE EXTENSION doesn't allow one to specify a path, neither absolute nor relative.
• The CREATE EXTENSION documentation plainly states that:

  PostgreSQL will create the extension using details from the file SHAREDIR/extension/extension_name.control.

• Section 38.17.1 of the documentation mirrors this:

  [The] control file for each extension […] must be placed in the installation's SHAREDIR/extension directory.

• Within the extension .control file, directory can be set so that the extension's .sql files are retrieved from another location. (The default is the aforementioned SHAREDIR/extension.)
• I encountered a 2020 mailing list message by Craig Ringer: “RFC: extension_search_path to supplement dynamic_library_path”, which also suggest to me that, at least at the time, no such custom path mechanism existed for extensions.
  • Sadly, there do not appear to be any follow-ups to that message.

I wouldn't mind having to start postgres with some custom environment variable similar to $PGDATA even if this means that I have to bypass the OS's excellent default cluster configuration and systemd structure.

If it turns out that I really cannot control the extension file location per postgres cluster, I will probably try to use something like mergerfs to overlay the files from the deployed extension directories onto "$(pg_config --sharedir)/extension" so that the package manager can continue to write system-wide extensions to the “lower”, original SHAREDIR.

Answer 1

Like the documentation tells you, extension control files have to be in $(pg_config --sharedir)/extension, and there is no way to configure a different location.