Support Major Version Upgrade of Postgres

[dougkirkley]

Currently If you do a Major version upgrade of Postgres, when the upgraded pods come back up pgBackrest fails to archive the WAL files due to the stanza version mismatch with the old and new version of Postgres.

{"level":"info","ts":"2025-11-05T00:24:13.860925294Z","logger":"archive-push","msg":"2025-11-05 00:24:13.860 P00  ERROR: [044]: WAL file '/var/lib/postgresql/data/pgdata/pg_wal/00000001000000000000008D' version 16, system-id 7569025189936418850 do not match stanza version 15, system-id 7568844977113780323","pipe":"stdout","logging_pod":"postgres-infra-1"}

I am not sure if there are any hooks we can use in the CNPG-I lifecycle to add in a post upgrade container that just runs stanza-upgrade or not.

[bmironov]

Hi @dougkirkley ,

I don't think that plugin can help you here. This is purely pgBackRest utility behaviour. It makes sense to send you such warning and refusal to take backup because your attempt to use existing backup repository jeopardizes recoverability of your DB.
You can't restore v15 backup into v16 PostgreSQL binary similarly to v16 backup to v15 binary. Therefore your action doens't make sense.

As a solution, you should use separate backup repository for each version of PostgreSQL, as the least measure. Then with major PostgreSQL version upgrade in your CRD you have to redirect its backup to new place.

Cheers

[Agalin]

Considering that pgbackrest itself provides a command for that, it definitely should be supported in some way although probably not triggered automatically with the cluster upgrade.

I agree with @bmironov that creating a second stanza is safer but, to be honest, I don't have experience with this command - and pgbackrest docs don't provide any details. I'd expect the upgraded stanza not to be usable with the older version of Postgresql although it's unclear to me if old WAL data is somehow preserved or not.

[dougkirkley]

I agree that it could be safer use a second repo, but considering that CNPG automatically does the postgres upgrade for you when bumping the image tag, it seems odd to not at least provide a way to handle that ton this side as well. Even if there is no automatic way to handle it we should at least provide a way to do it without requiring shell access to the sidecar.

I propose maybe an annotation that the instance controller could be triggered to run stanza-upgrade and then the annotation would be removed afterwards.

[dougkirkley]

I think I will close this issue in favor of changing stanzas per postgres version after I put in a PR to fix the stanza configuration issue. Thanks

[Agalin]

I don't think that plugin should do anything by itself. Ideally there would be a CNGP-I API that would inform the plugin (then plugin can have different options) and that would integrate with CNPG CLI, enabling an imperative call to upgrade the stanza.

I'd keep it open as it's definitely something actionable (as said earlier - there is an option for that in pgbackrest).