Missing libraries in upgrader image

[CPinhoK]

Missing libraries in the upgrader image

Overview

Upon attempting to upgrade a cluster from postgres version 13 to version 14 wherein the cluster there is a database with the postgis extension the upgrade will result in a failure due to missing libraries in the upgrader image

Environment

• Platform: (Kubernetes)
• Platform Version: (1.24.10)
• PGO Image Tag: (ubi8-5.3.0-0)
• Postgres Version (13, 14)
• Postgres Operator (v5.3.0)
• Storage: (local volumes)

Steps to Reproduce

Install operator version 5.3.0 CRDS
Install operator version 5.3.0

Create a cluster using postgres version 13, image (registry.developers.crunchydata.com/crunchydata/crunchy-postgres-gis:ubi8-13.9-3.1-2)
This cluster is initialized with a user and database

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo-ha
  namespace: test
spec:
  users:
    - name: xpto
      databases:
        - xpto
      options: "SUPERUSER"
      password:
        type: AlphaNumeric
  shutdown: false
  databaseInitSQL:
    key: init.sql
    name: trans-init-sql
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres-gis:ubi8-13.9-3.1-2
  postgresVersion: 13
  instances:
    - name: pgha1
      replicas: 2
      dataVolumeClaimSpec:
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 1
            podAffinityTerm:
              topologyKey: kubernetes.io/hostname
              labelSelector:
                matchLabels:
                  postgres-operator.crunchydata.com/cluster: hippo-ha
                  postgres-operator.crunchydata.com/instance-set: pgha1

In file init.sql (Creating extensions)

\c xpto
CREATE EXTENSION IF NOT EXISTS postgis;

Wait for Cluster to be up and running

Shutdown the cluster / Label it to perform the upgrade

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo-ha
  namespace: test
spec:
  users:
    - name: xpto
      databases:
        - xpto
      options: "SUPERUSER"
      password:
        type: AlphaNumeric
  shutdown: true
  databaseInitSQL:
    key: init.sql
    name: trans-init-sql
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres-gis:ubi8-13.9-3.1-2
  postgresVersion: 13
  instances:
    - name: pgha1
      replicas: 2
      dataVolumeClaimSpec:
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 1
            podAffinityTerm:
              topologyKey: kubernetes.io/hostname
              labelSelector:
                matchLabels:
                  postgres-operator.crunchydata.com/cluster: hippo-ha
                  postgres-operator.crunchydata.com/instance-set: pgha1

kubectl -n test annotate postgrescluster hippo-ha postgres-operator.crunchydata.com/allow-upgrade="upgrade13-to-14"

Create the upgrade

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PGUpgrade
metadata:
  name: upgrade13-to-14
  namespace: test
spec:
  fromPostgresVersion: 13
  postgresClusterName: hippo-ha
  toPostgresVersion: 14
  toPostgresImage: registry.developers.crunchydata.com/crunchydata/crunchy-postgres-gis:ubi8-14.6-3.1-2

The Job will start and fail

EXPECTED

The Job that performs the upgrade to be successful

ACTUAL

The Job starts and results in failure

Logs

The upgrade job has the following log

Step 5: Running pg_upgrade check...
2023-02-13T12:53:07.857347656Z
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions ok
Checking for presence of required libraries fatal

Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
 loadable_libraries.txt

Upon restarting the cluster in version 13 the file contains the following information

bash-4.4$ cat pgdata/loadable_libraries.txt 
could not load library "$libdir/postgis-3": ERROR:  could not access file "$libdir/postgis-3": No such file or directory
In database: xpto

[andrewlecuyer]

@CPinhoK as noted in the Postgres Major Version Upgrade section of the Crunchy Postgres for Kubernetes documentation, PostGIS major upgrades are not currently supported:

Please note the following prior to performing a PostgreSQL major version upgrade: - Any Postgres cluster being upgraded must be in a healthy state in order for the upgrade to complete successfully. If the cluster is experiencing issues such as Pods that are not running properly, or any other similar problems, those issues must be addressed before proceeding. - Major PostgreSQL version upgrades of PostGIS clusters are not currently supported.

However, we do have an item in our backlog to add major version upgrade support for PostGIS in a future Crunchy Postgres for Kubernetes release.

[CPinhoK]

@andrewlecuyer How can i extend your crunchy upgrader image to include the same version of postgis so we can try to migrate from version 13 to 14 using your upgrade process

[shusaan]

I am trying to upgrade from 13 to 14 and same issue with timescale db extension.

Here is the error,

SQL command failed SELECT pg_catalog.set_config('search_path', '', false); ERROR: could not access file "$libdir/timescaledb-2.9.3": No such file or directory
Even i Installed timescaledb on container image.

@andrewlecuyer

[dsessler7]

@shusaan, would you mind opening a new GitHub issue for your timescale problem? While they seem related, these are really different problems and having them in separate GH issues helps us to better coordinate support for them.