Beyond the Version Bump: Running PostgreSQL Major Upgrades in Percona Operator for PostgreSQL

It was quite challenging to perform postgres major upgrade, e.g., v17 to v18, in a Percona operator/K8s environment. There was no convenient approach to accomplish this task. The only viable solutions left were a Manual Dump/Restore to a new environment or using Logical replication. 

But these options were not free from challenges. Procuring a new Infrastructure/Cluster or running the manual process is complex and error-prone. Logical replication has its own caveats; not everything can be replicated smoothly using the native logical option.

Moreover, performing a manual pg_upgrade (In-Place) in the Operator/K8S environment is risky and unsupported.

This problem was addressed effectively by our engineering team, and they released a technical release of the Major Upgrade option under Postgres Operator v2.4.0.  After a couple of releases, the Major Upgrade feature finally became generally available (GA) under operator v2.9.0.

• Under the hood, the major upgrade activity in the operator runs InPlace(pg_upgrade) triggered by the PerconaPGUpgrade resource.
• The Operator then pauses the cluster and shuts it down. 
• The upgrade Job runs “pg_upgrade –link” on the primary’s volume, creating the new data directory (pg17) alongside the old one (pg16).
•  Replicas are then re-synced from the upgraded primary. 
• Finally, the cluster restarts on the new version.

Minimum Requirements and Considerations

• Percona Operator for PostgreSQL v2.4.0 (Tech Release); however, it’s better to upgrade to v2.9.0, which is officially GA.
• If the OS version and the glibc library change after the upgrade, they need to be addressed or fixed.
• The extensions need to be cross-validated, whether or not they are upgraded. Some extensions, such as the pgaudit extension, are not automatically upgraded. That needs to be dropped and recreated in each database where it is installed.
• The major upgrade activity invalidates (removes) any existing logical replicas that are already in place. After the upgrade, verify and reseed the logical replicas.

If you are still running a pretty old Operator version, it must be upgraded to at least the latest Operator v2.4.0-1 https://docs.percona.com/percona-operator-for-postgresql/3.0.0/update-operator.html to avail the “Major Upgrade” feature.

Let’s see, step by step and in detail, how we can accomplish the major upgrade.

Existing Operator/DB Environment:

Target Operator/DB Major Version:

Points to Note:

• Postgres v17 image will be available since operator v2.6.0:- https://docs.percona.com/percona-operator-for-postgresql/latest/versions.html#versions-compatibility, so we have to upgrade from any previous operator version to 2.6.0 incrementally  (2.4.0 → 2.5.1 → 2.6.0).
• Since operator v2.6.0, the base OS image has changed from ubi8 to ubi9: https://perconadev.atlassian.net/browse/K8SPG-652 & https://docs.percona.com/percona-operator-for-postgresql/3.1.0/ReleaseNotes/Kubernetes-Operator-for-PostgreSQL-RN2.6.0.html#improvements, which will result in a change in the glibc version from v2.28 to v2.34. Considering this, we have to address the impact of the collation change after the upgrade: https://docs.percona.com/percona-operator-for-postgresql/3.0.0/update-db-major.html#check-collation.
• The custom-built images (https://github.com/percona/percona-docker/blob/main/postgresql-containers/README.md) could be an option for using the existing UBI8 images with v2.6.0 (pg17); however, that is not a recommended path and may lead to errors or failures unless thoroughly tested. The recommended approach is to switch to the supported operator version and fix the collation changes accordingly.

Path to Operator upgrade (2.4.0 → 2.5.1 → 2.6.0)

1. We already have a running postgres setup with the details. 

Cluster/Pod:

OS/Glibc:

Important consideration:

We should always compare the complete patch version, as it might happen that we have the same Major/Minor version, say RHEL(8.x); however, the Patched version (8.x.x) is different and ultimately affects the underlying glibc. So even if only the patch version changes, we should take it seriously and verify the collation changes and, at a minimum, rebuild the indexes: https://docs.percona.com/percona-operator-for-postgresql/3.0.0/update-db-major.html#check-collation to avoid any corruption or consistency issues.

Database:

   2.  Now we will upgrade the Operator and CRD version from 2.4.0 → 2.5.1.

Once the rollout is complete, we can see the upgraded operator image to v2.5.1. 

Note: The postgres pods will remain intact, as we only upgraded the operator and CRD. As we are not upgrading/patching the underlying database version at this stage, the OS/Glibc will also remain the same.

OS/Glibc:

3. Next, we will upgrade the Operator and CRD version from 2.5.1 → 2.6.0.

Once the rollout is complete, we can see the upgraded operator image to v2.6.0. 

The OS/Glibc version remains unchanged because we haven’t yet patched the database version. 

Here, we have completed the required operator upgrade activity. We can now perform the database “Major Upgrade” task.

Postgres Major Upgrade (v16 to v17) 

We need to create a PerconaPGUpgrade resource (deploy/upgrade.yaml) by defining the source and target versions as below.

…

Downtime Phase:

Once we apply the above upgrade custom resource changes, the cluster will enter a termination state, resulting in database downtime. During this period, all database pods will be terminated, and the application will lose connectivity to the database until the Major Upgrade is successfully completed and the cluster is back online.

Note: The operator uses “pg_upgrade –link”, which avoids copying the existing database files into the new PostgreSQL data directory. Instead, it creates hard links to the existing data files, significantly reducing the time and additional disk space required for a major version upgrade. This will greatly reduce downtime, especially when dealing with a large database.

During this major upgrade process, we can see new pods created to handle the upgrade, such as pg_upgrade and cleanup of old data directories. 

This pod “cluster1-16-to-17-pgdata-8mmkz” is responsible for performing the actual upgrade of the database data directory from PostgreSQL 16 to PostgreSQL 17 using pg_upgrade –link and then preserving the Patroni configurations, etc.

The logs can further provide more details about each stage of the execution process.

Below is the individual cleanup pod created for each secondary node to remove the old postgres 16 data directory after the upgrade completes. 

Please note: for Primary/Leader, there will be no cleanup pod, as the old data is still left there for recovery purposes. When we use pg_upgrade –link, the old data directory is not usable in the event of a rollback or recovery. 

There is already a Jira ticket reported – https://perconadev.atlassian.net/browse/K8SPG-1235- to fix this behaviour and clean the old data directory once the activity is completed, to avoid any confusion. This will most likely be fixed in the next Operator release.

E.g,

Once the upgrade activity is finished on all nodes, the cluster will be back online with all existing pods.

Validations/Checking:

1. As we patched/upgraded the database version this time, we will now notice that the OS image and Glibc versions have changed to “ubi9” and “2.34”. That means we need to verify the collation changes and deal with them.

     2 . As we mentioned earlier, on all Secondary nodes, the old pg16 directory will be removed except the Leader. That was just kept there for any rollback or recovery purposes (data, WALs, etc.). Later on, you can remove the old data directory at your discretion.

 cluster1-instance1-7gpc-0(Replica) :

 cluster1-instance1-smbw-0(Replica):

cluster1-instance1-nxcr-0(Leader):

3. Existing data and tables verification from applications or manual process to ensure no anomalies.

Now, when we access the database, we first encounter the collation change warning, as expected, since the glibc version changed from 2.28 to 2.34.

Based on the collation check below, we performed REINDEX on the affected single index. After that, we refreshed the collation metadata. In a complex environment with a large number of indexes, a collation change could have a greater impact and cause data corruption; identify and fix them before using the database.

Production Readiness(Before you upgrade):

Although most upgrades are expected to deliver improvements, even a small number of edge cases can introduce regressions with significant business impact, including production outages. New PostgreSQL versions may also introduce behavioural changes or depreciate or remove existing features.

Therefore, for production environments, we strongly recommend at least three trial upgrade rounds, followed by thorough application validation before the final upgrade and cutover.

Conclusion:

The PostgreSQL major upgrade process we discussed above is not limited to upgrading between consecutive major versions. We can also perform in-place upgrades across larger version gaps, such as PostgreSQL 15 to 17, provided the applicable version compatibility requirements are met.

This feature significantly simplifies major version upgrade activities and reduces the associated operational effort. However, particularly when upgrading to Operator version 2.4.0 (where this feature was first introduced as a technical release), or when adopting it for the first time in subsequent versions, it is strongly recommended to thoroughly validate the process in a non-production or staging environment. As discussed earlier, several compatibility considerations and potential post-upgrade fixes need to be evaluated and addressed before allowing application traffic to resume in the upgraded environment.

If the upgrade fails or gets stuck for any reason, it can be resumed using the older PostgreSQL version: https://docs.percona.com/percona-operator-for-postgresql/3.0.0/pause.html. Further, the PerconaPGUpgrade resource and cluster/pod-level logs (see https://docs.percona.com/percona-operator-for-postgresql/3.0.0/update-db-major.html#troubleshooting-upgrade-issues) can be checked to identify the issue.