Perform Major Upgrade for PostgreSQL

In this article, we explore how to perform a PostgreSQL major version upgrade using ClusterControl. A major upgrade involves moving from one major release series to another (for example, PostgreSQL 15 to 16). Because such upgrades may introduce new system catalog structures and changes to internal mechanisms, it is crucial to plan carefully and follow best practices to minimize downtime and avoid data corruption.

Use cases

Here are some common scenarios in which a major PostgreSQL upgrade is necessary:

Feature requirements: New PostgreSQL versions often include performance improvements, new SQL features, and improved security defaults.
End-of-Life (EOL): Upgrading ensures you stay on a supported version of PostgreSQL to receive security patches and critical bug fixes.
Regulatory and compliance: Some organizations have strict rules about running supported, up-to-date software versions.
Performance enhancements: Certain major versions bring substantial improvements in indexing, partitioning, and query optimizers.

Important considerations

Before undertaking a major upgrade, consider the following points:

Backup everything: Ensure you have a full backup of all databases.
Plan downtime: Major upgrades typically require some downtime or a strategic failover approach.
Validate extensions: Extensions, custom functions, and plugins might need new versions compatible with the target PostgreSQL release.
Test: Always test the upgrade on a non-production environment first.
Roll-back plan: The safest rollback approach is to restore from a full backup of the old version or keep an untouched replica on the old version until the new version is fully validated.

Deploying a PostgreSQL cluster

First, you need to have a PostgreSQL cluster deployed by ClusterControl or imported into ClusterControl.

After you have the PostgreSQL cluster in place, go to ClusterControl → Clusters → Cluster Actions → Upgrades where you can chose between minor or major upgrades.

Major upgrades for PostgreSQL

Choose Major Upgrades to performs an upgrade to a later major version for the database software and data files. The upgrade job will upgrade all the nodes in the cluster by default. During the steps, you have to specify temporary ports and upgrade methods.

Note

Depending on the upgrade method you might require the storage capacity to hold two data sets.

Upgrade methods

There are three methods for upgrading your PostgreSQL cluster in ClusterControl:

copy: This is the default method with pg_upgrade. The --copy option is used which will copy files at a time. It is faster than doing regular backup and restore. This requires disk storage capacity to hold both the old and the new files.
link: This method uses the --link option with pg_ugprade. Hard links will be used instead of copying files which is faster and requires no extra disk storage. It is crucial to refrain from starting the old cluster after this step to avert any potential data corruption with the new cluster since they share the data.
pgdumpall: It uses pgdumpall to copy files by performing a backup on the old cluster and then restoring it on the new cluster. This requires disk storage capacity to hold the backup.

Monitoring upgrade process

To monitor the upgrade job in ClusterControl, go to ClusterControl → Activity center → Jobs where you can see the running jobs in the UI.

When the job in completed, you will see the cluster upgraded in the ClusterControl UI.

Using the ClusterControl CLI

You can also perform major upgrades by using the ClusterControl CLI from the ClusterControl node.

s9s cluster \
    --upgrade-cluster \
    --upgrade-to-version=16 \
    --upgrade-method=link \
    --cluster-id=1 \
    --log

This command will upgrade cluster packages to a newer major version 16 for cluster ID 1 with link as upgrade method.

Monitoring the upgrade

s9s job \
    --list \
    --long

You will get something like this:

Note

During the upgrade process, the cluster will not be operational. A major upgrade is performed at your own risk. Severalnines will not support any breaking issues that might occur during this operation.