Upgrade Percona Distribution for PostgreSQL¶

Considerations¶

Starting from the Operator 2.4.0 you can do a minor upgrade (for example, from 15.5 to 15.7, or from 16.1 to 16.3) and a major upgrade (for example, upgrade from PostgreSQL 15.5 to PostgreSQL 16.3) of Percona Distribution for PostgreSQL. Before the Operator version 2.4.0, you could only do a minor upgrade of Percona Distribution for PostgreSQL.
Starting with the Operator 2.6.0, PostgreSQL images are based on Red Hat Universal Base Image (UBI) 9 instead of UBI 8. UBI 9 has a different version of collation library glibc and this introduces a collation mismatch in PostgreSQL. Collation defines how text is sorted and compared based on language-specific rules such as case sensitivity, character order and the like. PostgreSQL stores the collation version used at database creation. When the collation version changes, this may result in corruption of database objects that use it like text-based indexes. Therefore, you need to identify and reindex objects affected by the collation mismatch.
Upgrading a PostgreSQL cluster may result in downtime, as well as failover caused by updating the primary instance.

Before you start¶

We recommend to update PMM Server before upgrading PMM Client.
If you are using PMM server version 2, use a PMM client image compatible with PMM 2. If you are using PMM server version 3, use a PMM client image compatible with PMM 3. See PMM upgrade documentation for how to migrate from version 2 to version 3.

Minor version upgrade¶

To make a minor upgrade of Percona Distribution for PostgreSQL (for example, from 17.5.2 to 17.6.1) , do the following:

Check the version of the Operator you have in your Kubernetes environment. If you need to update it, refer to the Operator upgrade guide
Check the current version of the Custom Resource and what versions of the database and cluster components are compatible with it. Replace the Operator version with your value in the following command:
```
$ curl https://check.percona.com/versions/v1/pg-operator/2.6.0 |jq -r '.versions[].matrix'
```
You can also find this information in the Versions compatibility matrix.
Update the database, the backup and PMM Client image names with a newer version tag. Find the image names in the list of certified images.

We recommend to update the PMM Server before the upgrade of PMM Client. If you haven’t done it yet, exclude PMM Client from the list of images to update.

Since this is a working cluster, the way to update the Custom Resource is to apply a patch with the kubectl patch pg command.

This example command updates the cluster with the name cluster1 in the namespace postgres-operator to the 2.8.0 version:
With PMM ClientWithout PMM Client
$ kubectl -n postgres-operator patch pg cluster1 --type=merge --patch '{ "spec": { "crVersion":"2.8.0", "image": "docker.io/percona/percona-distribution-postgresql:17.6-1, "proxy": { "pgBouncer": { "image": "docker.io/percona/percona-pgbouncer:1.24.1-1" } }, "backups": { "pgbackrest": { "image": "docker.io/percona/percona-pgbackrest:2.56.0-1" } }, "pmm": { "image": "docker.io/percona/pmm-client:3.4.1" } }}'
The following image names in the above example were taken from the list of certified images:
- docker.io/percona/percona-distribution-postgresql:17.6-1,
- docker.io/percona/percona-pgbouncer:1.24.1-1,
- docker.io/percona/percona-pgbackrest:2.56.0-1,
- docker.io/percona/pmm-client:3.4.1.
$ kubectl patch pg cluster1 -n postgres-operator --type=merge --patch '{ "spec": { "crVersion":"2.8.0", "image": "docker.io/percona/percona-distribution-postgresql:17.6-1", "proxy": { "pgBouncer": { "image": "docker.io/percona/percona-pgbouncer:1.24.1-1" } }, "backups": { "pgbackrest": { "image": "docker.io/percona/percona-pgbackrest:2.56.0-1" } } }}'
The following image names in the above example were taken from the list of certified images:
- docker.io/percona/percona-distribution-postgresql:17.6-1,
- docker.io/percona/percona-pgbouncer:1.24.1-1,
- docker.io/percona/percona-pgbackrest:2.56.0-1,

After you applied the patch, the deployment rollout will be triggered automatically. The update process is successfully finished when all Pods have been restarted.

Expected output

NAME                                           READY   STATUS      RESTARTS   AGE
cluster1-backup-4vwt-p5d9j                     0/1     Completed   0          97m
cluster1-instance1-b5mr-0                      4/4     Running     0          99m
cluster1-instance1-b8p7-0                      4/4     Running     0          99m
cluster1-instance1-w7q2-0                      4/4     Running     0          99m
cluster1-pgbouncer-79bbf55c45-62xlk            2/2     Running     0          99m
cluster1-pgbouncer-79bbf55c45-9g4cb            2/2     Running     0          99m
cluster1-pgbouncer-79bbf55c45-9nrmd            2/2     Running     0          99m
cluster1-repo-host-0                           2/2     Running     0          99m
percona-postgresql-operator-79cd8586f5-2qzcs   1/1     Running     0          120m

Scan for indexes that rely on collations other than C or POSIX and whose collations were provided by the operating system (c) or dynamic libraries (d). Connect to PostgreSQL and run the following query:

SELECT DISTINCT
    indrelid::regclass::text,
    indexrelid::regclass::text,
    collname,
    pg_get_indexdef(indexrelid)
FROM (
    SELECT
        indexrelid,
        indrelid,
        indcollation[i] coll
    FROM
        pg_index,
        generate_subscripts(indcollation, 1) g(i)
) s
JOIN pg_collation c ON coll = c.oid
WHERE
    collprovider IN ('d', 'c')
    AND collname NOT IN ('C', 'POSIX');

If you see the list of affected images, find the database names where indexes use a different collation version:
```
SELECT * FROM pg_database;
```
Sample output

```{.text .no-copy} oid | datname | datdba | encoding | datlocprovider | datistemplate | datallowconn | datconnlimit | datfrozenxid | datminmxid | dattablespace | datcollate | datctype | daticulocale | daticurules | datcollversion | datacl

-------+-----------+--------+----------+----------------+---------------+--------------+--------------+--------------+------------+---------------+-------------+-------------+--------------+-------------+----------------+------------------------------------------------------------

5 | postgres | 10 | 6 | c | f | t | -1 | 722 | 1 | 1663 | en_US.utf-8 | en_US.utf-8 | | | 2.28 | 1 | template1 | 10 | 6 | c | t | t | -1 | 722 | 1 | 1663 | en_US.utf-8 | en_US.utf-8 | | | 2.28 | {=c/postgres,postgres=CTc/postgres} 4 | template0 | 10 | 6 | c | t | f | -1 | 722 | 1 | 1663 | en_US.utf-8 | en_US.utf-8 | | | | {=c/postgres,postgres=CTc/postgres} 16466 | cluster1 | 10 | 6 | c | f | t | -1 | 722 | 1 | 1663 | en_US.utf-8 | en_US.utf-8 | | | 2.28 | {=Tc/postgres,postgres=CTc/postgres,cluster1=CTc/postgres} (4 rows)
Refresh collection metadata and rebuild affected indexes. This command requires the privileges of a superuser or a database owner:
```
ALTER DATABASE cluster1 REFRESH COLLATION VERSION;
```

Major version upgrade¶

Major version upgrade allows you to jump from one database major version to another (for example, upgrade from PostgreSQL 15.x to PostgreSQL 16.x).

Major version upgrades feature is currently a tech preview, and it is not recommended for production environments.

The upgrade is triggered by applying the YAML file which refers to the special Operator upgrade image and contains the information about the existing and desired major versions. An example of this file is present in deploy/upgrade.yaml:

apiVersion: pgv2.percona.com/v2
kind: PerconaPGUpgrade
metadata:
  name: cluster1-15-to-16
spec:
  postgresClusterName: cluster1
  image: docker.io/percona/percona-postgresql-operator:2.8.0-upgrade
  fromPostgresVersion: 15
  toPostgresVersion: 16
  toPostgresImage: docker.io/percona/percona-distribution-postgresql:16.10-1
  toPgBouncerImage: docker.io/percona/percona-pgbouncer:1.24.1-1
  toPgBackRestImage: docker.io/percona/percona-pgbackrest:2.56.0-1

As you can see, the manifest includes image names for the database cluster components (PostgreSQL, pgBouncer, and pgBackRest). You can find them in the list of certified images for the current Operator release. For older versions, please refer to the old releases documentation archive ).

After you apply the YAML manifest as usual (by running kubectl apply -f deploy/upgrade.yaml command), the actual upgrade takes place:

The Operator pauses the cluster, so the cluster will be unavailable for the duration of the upgrade,
The cluster is specially annotated with pgv2.percona.com/allow-upgrade: <PerconaPGUpgrade.Name> annotation,
Jobs are created to migrate the data,
The cluster starts up after the upgrade finishes.

Scan for indexes that rely on collations other than C or POSIX and whose collations were provided by the operating system (c) or dynamic libraries (d). Connect to PostgreSQL and run the following query:

SELECT DISTINCT
    indrelid::regclass::text,
    indexrelid::regclass::text,
    collname,
    pg_get_indexdef(indexrelid)
FROM (
    SELECT
        indexrelid,
        indrelid,
        indcollation[i] coll
    FROM
        pg_index,
        generate_subscripts(indcollation, 1) g(i)
) s
JOIN pg_collation c ON coll = c.oid
WHERE
    collprovider IN ('d', 'c')
    AND collname NOT IN ('C', 'POSIX');

If you see the list of affected images, find the database names where indexes use a different collation version:
```
SELECT * FROM pg_database;
```
Sample output

```{.text .no-copy} oid | datname | datdba | encoding | datlocprovider | datistemplate | datallowconn | datconnlimit | datfrozenxid | datminmxid | dattablespace | datcollate | datctype | daticulocale | daticurules | datcollversion | datacl

-------+-----------+--------+----------+----------------+---------------+--------------+--------------+--------------+------------+---------------+-------------+-------------+--------------+-------------+----------------+------------------------------------------------------------

5 | postgres | 10 | 6 | c | f | t | -1 | 722 | 1 | 1663 | en_US.utf-8 | en_US.utf-8 | | | 2.28 | 1 | template1 | 10 | 6 | c | t | t | -1 | 722 | 1 | 1663 | en_US.utf-8 | en_US.utf-8 | | | 2.28 | {=c/postgres,postgres=CTc/postgres} 4 | template0 | 10 | 6 | c | t | f | -1 | 722 | 1 | 1663 | en_US.utf-8 | en_US.utf-8 | | | | {=c/postgres,postgres=CTc/postgres} 16466 | cluster1 | 10 | 6 | c | f | t | -1 | 722 | 1 | 1663 | en_US.utf-8 | en_US.utf-8 | | | 2.28 | {=Tc/postgres,postgres=CTc/postgres,cluster1=CTc/postgres} (4 rows)
Refresh collection metadata and rebuild affected indexes. This command requires the privileges of a superuser or a database owner:
```
ALTER DATABASE cluster1 REFRESH COLLATION VERSION;
```

Note

If the upgrade fails for some reason, the cluster will stay in paused mode. Resume the cluster manually to check what went wrong with upgrade (it will start with the old version). You can check the PerconaPGUpgrade resource with kubectl get perconapgupgrade -o yaml command, and check the logs of the upgraded Pods to debug the issue.

During the upgrade data are duplicated in the same PVC for each major upgrade, and old version data are not deleted automatically. Make sure your PVC has enough free space to store data. You can remove data at your discretion by executing into containers and running the following commands (example for PostgreSQL 15):

$ rm -rf /pgdata/pg15
$ rm -rf /pgdata/pg15_wal

You can also delete the PerconaPGUpgrade resource (this will clean up the jobs and Pods created during the upgrade):

$ kubectl delete perconapgupgrade cluster1-15-to-16

Last update: 2025-11-13