Question on CloudNative-PG Upgrade

[reefland]

When you have a moment, I tried to upgrade CNPG from 0.18.2 (1.20.2 / PG 14.10) to either 0.19.1 or 0.20.1, while the coordinator upgrades just fine, the cluster itself does not. I would get messages such as:

invalid: spec.imageName: Invalid value: "ghcr.io/cloudnative-pg/postgresql:16.0": can't upgrade between ghcr.io/cloudnative-pg/postgresql:14.10 and ghcr.io/cloudnative-pg/postgresql:16.0

So I changes the various name: postgres to name: postgres16. I have barmanObjectStore configured much like yours to MinIO running on TrueNAS backing up to postgres14-v1 and my cluster16.yaml was set to use a new postgres16-v1.

Upon trying to create the postgres16 cluster the Job postgres16-1-full-recovery reports good things like:

"msg":"Recovering from external cluster"
"msg":"Target backup found"
"msg":"Starting barman-cloud-restore"
"msg":"Restore completed"
....
"msg":"Starting up instance"

But fails with The data directory was initialized by PostgreSQL version 14, which is not compatible with this version 16.1:

16:23:57.467 UTC [38] FATAL:  database files are incompatible with server","pipe":"stdout","logging_pod":"postgres16-1-full-recovery"}
{"level":"info","ts":"2024-02-05T16:23:57Z","logger":"pg_ctl","msg":"2024-02-05 16:23:57.467 UTC [38] DETAIL:  The data directory was initialized by PostgreSQL version 14, which is not compatible with this version 16.1 (Debian 16.1-1.pgdg110+1).","pipe":"stdout","logging_pod":"postgres16-1-full-recovery"}

I went through the various release notes and upgrade docs and it seems pretty straight forward, so don't understand why this happens (I'm not PostgreSQL expert), not seeing something specific in the upgrade steps needed.

Anyway, I put the names back and went back to cluster14.yaml and the restore from MinIO via barmanObjectStore worked perfect, now using postgres14-v2. I did notice none of the restored accounts had incorrect passwords, had to manually fix that and then my applications were back on-line.

I tried to review your git history on your PG cluster files for clues on how you handled this, didn't find it. I also found this thread where you previously had a restore failure (different error) cloudnative-pg/cloudnative-pg#1163

I'm wondering if the barmanObjectStore cannot be used for version upgrades and you have to some kind of manual pg_dump backup and restore with cloudnative-pg?

[onedr0p]

This was discussed in our discord

https://discord.com/channels/673534664354430999/1159134548274188390/1172918476839604435

https://github.com/Rodent1/home-ops/blob/0cfe2c9ede87f182b5362c177a1b25d1edbe1516/kubernetes/apps/database/cloudnative-pg/cluster/cluster16.yaml I used this thing
Specifically these parts

  bootstrap:
    initdb:
      import:
        type: monolith
        databases: ["*"]
        roles: ["*"]
        source:
          externalCluster: cnpg-6

and

  externalClusters:
    - name: cnpg-6
      connectionParameters:
        # Use the correct IP or host name for the source database
        host: postgres-rw.database.svc.cluster.local
        user: postgres
        dbname: postgres
        sslmode: require
      password:
        name: cloudnative-pg-secret
        key: password

While the pg14 cluster was still running
Ref: https://cloudnative-pg.io/documentation/current/database_import/

[onedr0p]

You basically want to spin up a new cluster side by side the old one and then have cnpg bootstrap the new one from the old one. Make sure the old database is not being used by any workloads first.