PostgreSQL - Upgrade

PaaS upgrade workflow

Request

The update of a PostgreSQL PaaS is the responsibility of cegedim.cloud and can be requested via a request ticket submitted from ITCare, specifying a time slot for the operation.

Please specify if the operation is to be carried out outside of business hours in order to plan an RFC.

It is recommended that you upgrade your non-production environments first in order to estimate the downtime generated by the operation and to test your applications using the new engine version.

Process

The upgrade of a PostgreSQL deployment (single-instance or high availability) takes place in two fully automated steps:

• Update the Operating system first if required

  • Multiple updates depending on the scenario: Debian 9 → Debian 10 → Debian 11 -> Debian 12

• Update of the PostgreSQL engine in the target version

Depending on the source and target versions of PostgreSQL, it may be necessary to first migrate the operating system to a version supported by cegedim.cloud (for more information, check OS / PostgreSQL support matrix).

Impacts

• The duration of an update is variable depending on:

  • The configured cpu and ram resources

  • The amount of data whose headers must be modified by the PostgreSQL engine.

  • The amount of data to be reindexed following a change of C library, after an OS update.

  • The amount of data on which to activate the checksum (data page checksum was activated since PaaS PostgreSQL 12 )

  • The amount of data to be vacuumed.

  • The amount of data to be backuped (a full backup is performed after migration process).

• The backup mode:

  • Point-in-time Recovery (PITR) from PostgreSQL 12 and higher.

    • The "dump" backup mode disappears in favour of the "PITR" and is only used in versions of PostgreSQL lower than version 12.

Time references

As an average guideline, durations for each steps of an upgrade in place of a 100 GB pgbench database:

• Debian upgrade: 10 minutes on average

• PostgreSQL reindexing: 5 minutes on average

• PostgreSQL upgrading: 1 minute on average

• PostgreSQL checksum: 3 minutes on average

• PostgreSQL vacuuming: 1 minute on average

• PostgreSQL full backup (PITR mode): 16 minutes on average

In PostgreSQL HA, we need to upgrade the replica too and synchronize this replica with the leader:

• PostgreSQL synchronizing: 4 minutes on average

Total average duration for a 100GB database: 40 minutes

OS / PostgreSQL support matrix

Linux distributions supported by cegedim.cloud depending on the PostgreSQL version:

PostgreSQL version	Debian distribution
PostgreSQL 10	Debian 9
PostgreSQL 11	Debian 10
PostgreSQL 12	Debian 10
PostgreSQL 13	Debian 11
PostgreSQL 14	Debian 11
PostgreSQL 15	Debian 11
PostgreSQL 16	Debian 12

Supported PostgreSQL update paths

If the operating system is updated, it may require a complete reindexing (also handled by cegedim.cloud) due to changes in the C library when the operating system is updated.

Depending on the amount of data, this operation may take some time.

Below are the update paths supported by cegedim.cloud:

Source Version	PostgreSQL 11	PostgreSQL 12	PostgreSQL 13	PostgreSQL 14	PostgreSQL 15	PostgreSQL 16
PostgreSQL 10	✅ * Debian 9 → Debian 10	✅ * Debian 9 → Debian 10	✅ ** Debian 9 → Debian 11	✅ ** Debian 9 → Debian 11	✅** Debian 9 → Debian 11	✅*** Debian 9 → Debian 12
PostgreSQL 11		✅ * Debian 9 → Debian 10	✅ ** Debian 9 → Debian 11	✅ ** Debian 9 → Debian 11	✅** Debian 9 → Debian 11	✅*** Debian 9 → Debian 12
PostgreSQL 12			✅ * Debian 10 → Debian 11	✅ * Debian 10 → Debian 11	✅* Debian 10 → Debian 11	✅** Debian 10 → Debian 12
PostgreSQL 13				✅	✅	✅* Debian 11 → Debian 12
PostgreSQL 14					✅	✅* Debian 11 → Debian 12
PostgreSQL 15						✅* Debian 11 → Debian 12

* An operating system upgrade is required

** Two operating system upgrades are required

*** Three operating system upgrades are required