Easy Postgres Major Version Upgrades Using PGO v5.1

Whether upgrading PGO itself, or upgrading the PostgreSQL databases PGO manages, seamless upgrades should be a core feature for any cloud or Kubernetes-based database solution. As a result, one of the goals when we set out to build version five of PGO, the Postgres Operator from Crunchy Data, was to provide a seamless and user-friendly upgrade experience.

Today we’re excited to introduce support for major version PostgreSQL upgrades in PGO v5.1. Using the new PGUpgrade API, you can now seamlessly upgrade your clusters across major versions of PostgreSQL. This means upgrading Postgres is now as easy as submitting a simple custom resource, with PGO handling everything else.

Please join me in walking through an example of this powerful new capability, and see just how easy PGO makes the major version upgrade process!

Create a Cluster

The first step is to create a PostgresCluster that can then be upgraded.  For this example we will first deploy a PostgreSQL 13 cluster, which we will then upgrade to PostgreSQL 14. A simple PostgreSQL 13 cluster can be created as follows:

$ kubectl create -f - <<EOF
apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-13.6-1
  postgresVersion: 13
  instances:
    - name: instance1
      dataVolumeClaimSpec:
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.38-0
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 1Gi
EOF
postgrescluster.postgres-operator.crunchydata.com/hippo created

Once the cluster is up and running, add some data to the database. In a later step, specifically following the completion of the major PostgreSQL version upgrade, we will confirm this data is still present.

First, find the name of the primary PostgreSQL instance:

$ kubectl get pod -o name -l \
postgres-operator.crunchydata.com/role=master,postgres-operator.crunchydata.com/cluster=hippo
pod/hippo-instance1-5clq-0

Then, insert some data using psql.

$ kubectl exec -it -c database \
    pod/hippo-instance1-5clq-0 -- psql
psql (13.6)

CREATE TABLE upgradedata(id int);
CREATE TABLE
INSERT INTO upgradedata(id) SELECT id FROM generate_series(1, 10000) AS id;
INSERT 0 10000

Note: It is recommended that a backup always be created prior to performing a major PG upgrade.  Please see the PGO backup tutorial for more information.

Upgrade the Cluster

With the cluster up and running and some test data inserted, we can now perform the major version PostgreSQL upgrade!  As described above, this will be done using the new PGUpgrade API.

To initiate the upgrade process, proceed with creating a PGUpgrade custom resource called “hippo-upgrade”:

$ kubectl create -f - <<EOF
apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PGUpgrade
metadata:
  name: hippo-upgrade
spec:
  postgresClusterName: hippo
  fromPostgresVersion: 13
  toPostgresVersion: 14
  image: registry.developers.crunchydata.com/crunchydata/crunchy-upgrade:ubi8-5.1.0-0
EOF
pgupgrade.postgres-operator.crunchydata.com/hippo-upgrade created

As you can see, initiating a major PostgreSQL upgrade is a simple as providing the following information in a PGUpgrade spec:

• The name of the PostgresCluster we want to upgrade
• The major versions of PostgreSQL we are upgrading to and from
• The crunchy-upgrade image to use for the upgrade

However, at this point you will notice that even though a PGUpgrade custom resource has been created, nothing is occurring within the running cluster.  By inspecting the conditions of “hippo-upgrade” custom resource, you can see the specific reason why:

$ kubectl describe pgupgrade hippo-upgrade
Name:     hippo-upgrade
Namespace: postgres-operator
…
Status:
  Conditions:
Last Transition Time:  2022-03-16T01:37:32Z
Message:           PostgresCluster instances still running
Observed Generation:   1
Reason:            PGClusterNotShutdown
Status:            False
Type:              Progressing
Events:                <none>

As the above condition indicates, the upgrade is not progressing because the “hippo” cluster we are attempting to upgrade has not yet been shut down.  This is one way in which PGO allows those managing a cluster to retain complete control over the upgrade process and prevent unexpected outages, even once a PGUpgrade resource for that cluster has been created.

At this point we can therefore proceed with shutting down the “hippo” cluster:

$ kubectl patch postgrescluster hippo --type=merge \
    -p '{"spec":{"shutdown":true}}'
postgrescluster.postgres-operator.crunchydata.com/hippo patched

However, even once the “hippo” cluster has been shutdown, the conditions “hippo-upgrade” custom resource will still indicate that the upgrade is unable progress:

$ kubectl describe pgupgrade hippo-upgrade
Name:     hippo-upgrade
Namespace: postgres-operator
…
Status:
  Conditions:
Last Transition Time:  2022-03-16T01:37:32Z
Message:           PostgresCluster hippo lacks annotation for upgrade hippo-upgrade
Observed Generation:   1
Reason:            PGClusterMissingRequiredAnnotation
Status:            False
Type:              Progressing
Events:                <none>

In this instance the condition is telling us that the “hippo” PostgresCluster is missing the annotation that is needed to initiate the upgrade process.  This is yet another way in which PGO allows the upgrade process to be controlled by those managing a PostgreSQL cluster, even if that cluster is currently shut down when the PGUpgrade custom resource is created.

Therefore, as a final step, add the required annotation to the “hippo” PostgresCluster.  The value of the annotation should match the name of the PGUpgrade custom resource, i.e., “hippo-upgrade” for this specific example:

$ kubectl annotate postgrescluster hippo  \
    postgres-operator.crunchydata.com/allow-upgrade=hippo-upgrade

At this point a Job will be run to upgrade the “hippo” cluster to PostgreSQL v14.  And once the Job completes, we can once again check the conditions for the “hippo-upgrade” PGUpgrade custom resource to determine the status of the upgrade.

$ kubectl describe pgupgrade hippo-upgrade
Name:         hippo-upgrade
Namespace:    postgres-operator
…
Status:
 Conditions:
   Last Transition Time:  2022-03-16T02:17:26Z
   Message:               PostgresCluster hippo is running version 14
   Observed Generation:   1
   Reason:                PGUpgradeCompleted
   Status:                False
   Type:                  Progressing
   Last Transition Time:  2022-03-16T02:19:25Z
   Message:               PostgresCluster hippo is ready to complete upgrade to version 14
   Observed Generation:   1
   Reason:                PGUpgradeSucceeded
   Status:                True
   Type:                  Succeeded
Events:                    <none>

As you can see above, the Succeeded condition is now true, indicating that the upgrade completed successfully!  We can therefore now start the cluster back up as follows:

$ kubectl patch postgrescluster hippo --type "json" -p '\[
{"op":"replace","path":"/spec/shutdown","value":false},
{"op":"replace","path":"/spec/postgresVersion","value":14},
{"op":"replace","path":"/spec/image","value":"registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-14.2-1"}\]'
postgrescluster.postgres-operator.crunchydata.com/hippo patched

Once the cluster is back up and running, we can verify that the data we added above survived the upgrade.  Therefore, once again find the name of the primary Postgres instance:

$ kubectl get pod -o name -l \ postgres-operator.crunchydata.com/role=master,postgres-operator.crunchydata.com/cluster=hippo
pod/hippo-instance1-79ff-0

Then, verify that the data is present.

$ kubectl exec -it -c database \
    pod/hippo-instance1-79ff-0 -- psql
psql (14.2)
Type "help" for help.

SELECT * FROM upgradedata;
 id    
-------
    1
    2
    3
    4
    5
…

And finally, run the following query to check the current version of PostgreSQL:

SHOW server_version;
 server_version
----------------
 14.2
(1 row)

As the above clearly indicates, the cluster is now on PostgreSQL version 14! This therefore confirms that the PGUpgrade API successfully orchestrated and completed a major PostgreSQL upgrade for the “hippo” cluster.

Major PostgreSQL Upgrades Made Easy

As the example above clearly demonstrates, PGO now makes upgrading across major versions of PostgreSQL easier than ever before.   For a full list of all the great features included in PGO v5.1, please see the full release notes. Additionally, if you are not a Crunchy customer but would like to learn more major PostgreSQL upgrade support in PGO, please feel free to contact us to help answer your questions.