Managing PostgreSQL versions

The PostgreSQL Global Development Group typically releases one major version of PostgreSQL each year, usually in the third quarter. Our goal is to balance supporting and adopting new PostgreSQL features with the downtime and administrative costs of having our users upgrade. GitLab aims to support two versions of PostgreSQL at any given time. This means that prior to adding a new version of PostgreSQL, we will remove the oldest version of PostgreSQL that we support, and bump the minimally required PostgreSQL version up by one major version. PostgreSQL removals are only done in major GitLab releases.

Software definitions

The software definitions are in:

  • config/software/postgresql_old.rb
  • config/software/postgresql.rb
  • config/software/postgresql_new.rb

Default version

The version that should be installed by default is controlled by using the ’link bin files’ step. The software definition with this step will be used on a new installation.

default_version in files/gitlab-ctl-commands/pg-upgrade.rb must use the same software definition. It is the version gitlab-ctl pg-upgrade upgrades to when you do not pass --target-version.

Upgrading

The gitlab-ctl pg-upgrade command is used to upgrade from postgresql_old or postgresql to postgresql_new. See our documentation for how to use it.

Automatic upgrades

The gitlab-ctl pg-upgrade command is executed during every upgrade. To change the default PostgreSQL version used during the upgrade and revert processes, you must adjust the configuration to specify the desired PostgreSQL version that will be automatically upgraded during installation.

Removing an older version

When it is time to remove an older version, create an epic with issues to track the following:

  1. Remove the old version from the Linux package.
  2. Remove the old version from the Helm install.
  3. To minimize CI costs, remove the older version from test suites (before doing this, check that .com is not using the old version still).
  4. Remove references to that version of PostgreSQL in the GitLab user documentation.
  5. Start printing deprecation notices in the release post three GitLab versions before a major PostgreSQL version is removed. If the PostgreSQL version is a version that will be removed within three releases, print deprecation notices in the Admin UI and during the GitLab upgrade process, regardless of whether it is an Linux package-managed PostgreSQL database or an external database.

For removals, perform the following steps:

  1. Run git rm config/software/postgresql_old.rb
  2. Run git mv config/software/postgresql{,_old}.rb
  3. Edit config/software/postgresql_old.rb and change name from postgresql to postgresql_old
  4. Run git mv config/software/postgresql{_new,}.rb
  5. Edit config/software/postgresql.rb and change name from postgresql_new to postgresql

Adding a new version

  1. Run git cp config/software/postgresql{,_new}.rb

  2. Edit config/software/postgresql_new.rb. Update:

    1. name to postgresql_new
    2. default_version to the new version
    3. version to have the new version, and the sha256
    4. major_version if necessary
  3. Add the new PostgreSQL version to the full test suite.

  4. Run a nightly test of the gitlab-org/gitlab repo against the new version of PostgreSQL.

  5. Ensure that the package build includes both versions of PostgreSQL.

  6. For Helm installs, update the default PostgreSQL chart version if the default is changing.

  7. Update user documentation.

Testing

  1. GitLab runs on the new version of PostgreSQL.
  2. Test running GitLab on the new PostgreSQL version at the 10k reference architecture scale and check for performance regressions.

Test upgrades and fresh installs for the following environments:

  1. Single node.
  2. Install with a separate database node managed by the Linux package.
  3. HA database cluster with 3 or more database nodes in the cluster.
  4. Geo installations with a single node primary and single node secondary (postgresql and geo-postgresql on the same secondary node).
  5. Geo installations with a HA database cluster on the primary.
  6. Geo installations with a separate database and a separate tracking database on the secondary.
  7. Helm installs.
  8. After testing that upgrades to the newest version work, confirm that revert-pg-upgrade successfully downgrades to the previously used version, including on a Geo secondary standalone tracking database.
  9. If the default PostgreSQL version changes, test GitLab upgrades with external PostgreSQL databases.
  10. Back up and restore.

If the default PostgreSQL version is changing:

  1. Auto upgrades on a single node install, separate database node, HA cluster.
  2. Auto upgrades where an external PostgreSQL database is being used.
  3. Geo installs are not auto upgraded.

If the minimally required version is changing:

  1. GitLab upgrade errors out if an old version of Linux package-managed PostgreSQL is still installed.

If the above tests are manual, we risk missing a breaking change that is introduced after the manual tests have been performed. We should automate as many of these tests as possible.

  1. The package build includes both versions of PostgreSQL
  2. Running gitlab-ctl pg-upgrade works

Automated upgrade tests

The Trigger:ee-package downstream pipeline runs these jobs against the Docker image built in that pipeline:

Job Test script What it tests
single-node-pg-upgrade-test ha-tests/single-node-pg-upgrade/test A fresh install runs the default version. An install pinned to the postgresql version is upgraded to the postgresql_new version with gitlab-ctl pg-upgrade, then reverted with gitlab-ctl revert-pg-upgrade. Data and the data_checksums setting are kept.
patroni-pg-upgrade-test ha-tests/patroni-pg-upgrade/test A Patroni cluster on the postgresql version is upgraded to the postgresql_new version on the leader and the replica.

The tests read both major versions from config/software/postgresql.rb and config/software/postgresql_new.rb. The single node test reads the default version from the software definition with the link bin files step, and fails if default_version in files/gitlab-ctl-commands/pg-upgrade.rb uses a different one. When postgresql_new is the default, the test runs gitlab-ctl pg-upgrade without a target version, like the Docker entrypoint. When postgresql_new is not the default yet, the test passes --target-version. The jobs run automatically when config/software/postgresql_new.rb exists, and are manual otherwise. When only one major version is shipped, the tests skip.

When you change PostgreSQL versions, check the tests:

  • When you add postgresql_new or make it the default, the tests need no changes. Move the link bin files step and default_version in pg-upgrade.rb in the same merge request.
  • When you move the link bin files step, change dependency 'postgresql' or dependency 'postgresql_new' in config/software/gitlab-exporter.rb and config/software/psycopg2.rb to the definition that has the step. These definitions need embedded/bin/pg_config. A wrong dependency can still build when the build order puts the other definition first, so the upgrade tests do not find it reliably.
  • When you turn on automatic upgrades, the single node test reads ATTEMPT_AUTO_PG_UPGRADE in docker/assets/init-container. When it is 'true' and postgresql_new is the default, the test expects the container to upgrade on restart. The tests do not cover the Linux package automatic upgrade (attempt_auto_pg_upgrade? in files/gitlab-ctl-commands/upgrade.rb), so change both settings together.
  • When you remove a version, the tests need no changes. Until the next postgresql_new is added, the jobs are manual and the tests skip.

The tests do not cover upgrades from postgresql_old, Geo, external databases, or a new node that joins a cluster on the old version. Test these manually.

To run the tests locally, use the Docker image from your merge request pipeline. The tests read the PostgreSQL versions from your checkout, so the image must be built from the same commit:

shell
IMAGE=<image> ha-tests/single-node-pg-upgrade/test
IMAGE=<image> ha-tests/patroni-pg-upgrade/test

To keep the containers after the test for debugging, set CLEANUP=0. To remove them afterward, run docker compose down -v in the test directory.

The case of libpq

Some modules, including pyscopg2, depend on PostgreSQL client library, i.e. libpq. It should be always linked to the latest bundled version. By using the latest version we rely on backward compatibility of libpq.

Known issues

Geo uses streaming replication, which resynchronizes the entire secondary database after a major PostgreSQL upgrade. Because this resynchronization can cause hours or days of downtime, you should not do automatic upgrades if you use Geo.

Automatic PostgreSQL upgrades are disabled in some, but not all, cases when Geo is also configured. Therefore you should opt out of automatic PostgreSQL upgrades and only perform PostgreSQL upgrades deliberately.