Postgis extension upgrade issue

[kurdyshev]

Description

I followed the example from the PostGIS extension container documentation (https://github.com/cloudnative-pg/postgres-extensions-containers/blob/main/postgis/README.md) and created a CloudNativePG cluster using PostGIS extension version 3.6.3.
After that, I tried to upgrade the PostGIS extension to 3.6.4 by changing the extension image version and updating the Database resource.
The new extension version is available, but the database extension upgrade fails.

~ kubectl exec -it cluster-postgis-1 -- psql app -c "\dx"
Defaulted container "postgres" out of: postgres, bootstrap-controller (init)
                                                                                List of installed extensions
             Name             | Version | Default version |   Schema   |                                                     Description            
                                         
------------------------------+---------+-----------------+------------+----------------------------------------------------------------------------
-----------------------------------------
 address_standardizer         | 3.6.3   | 3.6.4           | public     | Used to parse an address into constituent elements. Generally used to suppo
rt geocoding address normalization step.
 address_standardizer_data_us | 3.6.3   | 3.6.4           | public     | Address Standardizer US dataset example
 fuzzystrmatch                | 1.2     | 1.2             | public     | determine similarities and distance between strings
 plpgsql                      | 1.0     | 1.0             | pg_catalog | PL/pgSQL procedural language
 postgis                      | 3.6.3   | 3.6.4           | public     | PostGIS geometry and geography spatial types and functions
 postgis_raster               | 3.6.3   | 3.6.4           | public     | PostGIS raster types and functions
 postgis_sfcgal               | 3.6.3   | 3.6.4           | public     | PostGIS SFCGAL functions
 postgis_tiger_geocoder       | 3.6.3   | 3.6.4           | tiger      | PostGIS tiger geocoder and reverse geocoder
 postgis_topology             | 3.6.3   | 3.6.4           | topology   | PostGIS topology spatial types and functions
(9 rows)

Error

The Database resource reports:

~ kubectl get database cluster-postgis-app -o yaml | grep -A 6 status
status:
  applied: false
  extensions:
  - applied: false
    message: 'altering version: ERROR: could not access file "$libdir/postgis-3":
      No such file or directory (SQLSTATE 58P01)'
    name: postgis

Additional testing

Manually recreating the extension works:

DROP EXTENSION postgis CASCADE;
CREATE EXTENSION postgis;

After recreation, the extension is created with version 3.6.4.

Expected behavior

It should be possible to upgrade an existing PostGIS extension from 3.6.3 to 3.6.4 without dropping and recreating the extension.
The extension upgrade should work through:

ALTER EXTENSION postgis UPDATE TO '3.6.4';

Question

Is this expected behavior with the current PostGIS extension container image?
If no, could the extension image be adjusted so that ALTER EXTENSION UPDATE can find the required shared library during the upgrade?

[NiccoloFei]

Hi @kurdyshev, thanks for the detailed report.
We are aware of this issue, this is a bug in PostgreSQL that has also been discovered by @gbartolini and it has already been reported upstream, see: https://www.postgresql.org/message-id/flat/19506-9478f3012ecc2328%40postgresql.org
Gabriele also proposed a patch, which is attached in the mailing thread.

What happens is that PostGIS upgrade scripts hardcode a literal LOAD '$libdir/postgis-3', where $libdir is the value for the standard compiled-in package library directory (usually /usr/lib/postgresql/18/lib in debian).
With CNPG, image-volume extensions are mounted as separate volumes and found via extension_control_path / dynamic_library_path. Upgrade scripts (like postgis--ANY--3.6.4.sql) execute a literal LOAD statement which wrongly looks for the extension in the $libdir instead of consulting dynamic_library_path, so the library can't be found.

Most extensions also hardcode the libdir as part of the module_pathname on their .control file, like
module_pathname = '$libdir/postgis-3'.
A fresh CREATE EXTENSION resolves the library through the module_pathname function-load path, which strips $libdir correctly and consults dynamic_library_path (which is why your workaround of dropping and re-creating the extension succeeds).

This issue currently affects all extensions that contain a literal $libdir inside their upgrade scripts.
Extensions that don't do that are unaffected, and currently the only extension affected that's part of this project is PostGIS.

I'll keep this issue open to track the upstream resolution and link back here once a fix is released. Thanks again for surfacing it!

[kurdyshev]

Hi @NiccoloFei, thanks for the detailed explanation and for investigating this issue. I appreciate the clarification and the upstream report. I’ll keep an eye on the progress there. Thanks again for your help!