Upgrading operating systems for PostgreSQL

WARNING: Geo cannot be used to migrate a PostgreSQL database from one operating system to another. If you attempt to do so, the secondary site may appear to be 100% replicated when in fact some data is not replicated, leading to data loss. This is because Geo depends on PostgreSQL streaming replication, which suffers from the limitations described in this document. Also see Geo Troubleshooting - Check OS locale data compatibility.

If you upgrade the operating system on which PostgreSQL runs, any changes to locale data might corrupt your database indexes. In particular, the upgrade to glibc 2.28 is likely to cause this problem. To avoid this issue, migrate using one of the following options, roughly in order of complexity:

• Recommended. Backup and restore.
• Recommended. Rebuild all indexes.
• Rebuild only affected indexes.

Be sure to backup before attempting any migration, and validate the migration process in a production-like environment. If the length of downtime might be a problem, then consider timing different approaches with a copy of production data in a production-like environment.

If you are running a scaled-out GitLab environment, and there are no other services running on the nodes where PostgreSQL is running, then we recommend upgrading the operating system of the PostgreSQL nodes by themselves. To reduce complexity and risk, do not combine the procedure with other changes, especially if those changes do not require downtime, such as upgrading the operating system of nodes running only Puma or Sidekiq.

For more information about how GitLab plans to address this issue, see epic 8573.

Backup and restore

Backup and restore recreates the entire database, including the indexes.

1. Take a scheduled downtime window. In all nodes, stop unnecessary GitLab services:

   gitlab-ctl stop
   gitlab-ctl start postgresql

2. Backup the PostgreSQL database with pg_dump or the GitLab backup tool, with all data types except db excluded (so only the database is backed up).

3. In all PostgreSQL nodes, upgrade the OS.

4. In all PostgreSQL nodes, update GitLab package sources after upgrading the OS.

5. In all PostgreSQL nodes, install the new GitLab package of the same GitLab version.

6. Restore the PostgreSQL database from backup.

7. In all nodes, start GitLab.

Advantages:

• Straightforward.
• Removes any database bloat in indexes and tables, reducing disk use.

Disadvantages:

• Downtime increases with database size, at some point becoming problematic. It depends on many factors, but if your database is over 100 GB then it might take on the order of 24 hours.

Backup and restore, with Geo secondary sites

1. Take a scheduled downtime window. In all nodes of all sites, stop unnecessary GitLab services:

   gitlab-ctl stop
   gitlab-ctl start postgresql

2. In the primary site, backup the PostgreSQL database with pg_dump or the GitLab backup tool, with all data types except db excluded (so only the database is backed up).

3. In all PostgreSQL nodes of all sites, upgrade the OS.

4. In all PostgreSQL nodes of all sites, update GitLab package sources after upgrading the OS.

5. In all PostgreSQL nodes of all sites, install the new GitLab package of the same GitLab version.

6. In the primary site, restore the PostgreSQL database from backup.

7. Optionally, start using the primary site, at the risk of not having a secondary site as warm standby.

8. Set up PostgreSQL streaming replication to the secondary sites again.

9. If the secondary sites receive traffic from users, then let the read-replica databases catch up before starting GitLab.

10. In all nodes of all sites, start GitLab.

Rebuild all indexes

Rebuild all indexes.

1. Take a scheduled downtime window. In all nodes, stop unnecessary GitLab services:

   gitlab-ctl stop
   gitlab-ctl start postgresql

2. In all PostgreSQL nodes, upgrade the OS.

3. In all PostgreSQL nodes, update GitLab package sources after upgrading the OS.

4. In all PostgreSQL nodes, install the new GitLab package of the same GitLab version.

5. In a database console, rebuild all indexes:

   SET statement_timeout = 0;
   REINDEX DATABASE gitlabhq_production;

6. After reindexing the database, the version must be refreshed for all affected collations. To update the system catalog to record the current collation version:

   ALTER COLLATION <collation_name> REFRESH VERSION;

7. In all nodes, start GitLab.

Advantages:

• Straightforward.
• May be faster than backup and restore, depending on many factors.
• Removes any database bloat in indexes, reducing disk use.

Disadvantages:

• Downtime increases with database size, at some point becoming problematic.

Rebuild all indexes, with Geo secondary sites

1. Take a scheduled downtime window. In all nodes of all sites, stop unnecessary GitLab services:

   gitlab-ctl stop
   gitlab-ctl start postgresql

2. In all PostgreSQL nodes, upgrade the OS.

3. In all PostgreSQL nodes, update GitLab package sources after upgrading the OS.

4. In all PostgreSQL nodes, install the new GitLab package of the same GitLab version.

5. In the primary site, in a database console, rebuild all indexes:

   SET statement_timeout = 0;
   REINDEX DATABASE gitlabhq_production;

6. After reindexing the database, the version must be refreshed for all affected collations. To update the system catalog to record the current collation version:

   ALTER COLLATION <collation_name> REFRESH VERSION;

7. If the secondary sites receive traffic from users, then let the read-replica databases catch up before starting GitLab.

8. In all nodes of all sites, start GitLab.

Rebuild only affected indexes

This is similar to the approach used for GitLab.com. To learn more about this process and how the different types of indexes were handled, see the blog post about upgrading the operating system on our PostgreSQL database clusters.

1. Take a scheduled downtime window. In all nodes, stop unnecessary GitLab services:

   gitlab-ctl stop
   gitlab-ctl start postgresql

2. In all PostgreSQL nodes, upgrade the OS.

3. In all PostgreSQL nodes, update GitLab package sources after upgrading the OS.

4. In all PostgreSQL nodes, install the new GitLab package of the same GitLab version.

5. Determine which indexes are affected.

6. In a database console, reindex each affected index:

   SET statement_timeout = 0;
   REINDEX INDEX <index name> CONCURRENTLY;

7. After reindexing bad indexes, the collation must be refreshed. To update the system catalog to record the current collation version:

   ALTER COLLATION <collation_name> REFRESH VERSION;

8. In all nodes, start GitLab.

Advantages:

• Downtime is not spent rebuilding unaffected indexes.

Disadvantages:

• More chances for mistakes.
• Requires expert knowledge of PostgreSQL to handle unexpected problems during migration.
• Preserves database bloat.

Rebuild only affected indexes, with Geo secondary sites

1. Take a scheduled downtime window. In all nodes of all sites, stop unnecessary GitLab services:

   gitlab-ctl stop
   gitlab-ctl start postgresql

2. In all PostgreSQL nodes, upgrade the OS.

3. In all PostgreSQL nodes, update GitLab package sources after upgrading the OS.

4. In all PostgreSQL nodes, install the new GitLab package of the same GitLab version.

5. Determine which indexes are affected.

6. In the primary site, in a database console, reindex each affected index:

   SET statement_timeout = 0;
   REINDEX INDEX <index name> CONCURRENTLY;

7. After reindexing bad indexes, the collation must be refreshed. To update the system catalog to record the current collation version:

   ALTER COLLATION <collation_name> REFRESH VERSION;

8. The existing PostgreSQL streaming replication should replicate the reindex changes to the read-replica databases.

9. In all nodes of all sites, start GitLab.

Checking glibc versions

To see what version of glibc is used, run ldd --version.

The following table shows the glibc versions shipped for different operating systems:

For example, suppose you are upgrading from CentOS 7 to RedHat Enterprise 8. In this case, using PostgreSQL on this upgraded operating system requires using one of the two mentioned approaches, because glibc is upgraded from 2.17 to 2.28. Failing to handle the collation changes properly causes significant failures in GitLab, such as runners not picking jobs with tags.

On the other hand, if PostgreSQL has already been running on glibc 2.28 or higher with no issues, your indexes should continue to work without further action. For example, if you have been running PostgreSQL on RedHat Enterprise 8 (glibc 2.28) for a while, and want to upgrade to RedHat Enterprise 9 (glibc 2.34), there should be no collations-related issues.

Verifying glibc collation versions

For PostgreSQL 13 and higher, you can verify that your database collation version matches your system with this SQL query:

SELECT collname AS COLLATION_NAME,
       collversion AS VERSION,
       pg_collation_actual_version(oid) AS actual_version
FROM pg_collation
WHERE collprovider = 'c';

Matching collation example

For example, on a Ubuntu 22.04 system, the output of a properly indexed system looks like:

gitlabhq_production=# SELECT collname AS COLLATION_NAME,
       collversion AS VERSION,
       pg_collation_actual_version(oid) AS actual_version
FROM pg_collation
WHERE collprovider = 'c';
 collation_name | version | actual_version
----------------+---------+----------------
 C              |         |
 POSIX          |         |
 ucs_basic      |         |
 C.utf8         |         |
 en_US.utf8     | 2.35    | 2.35
 en_US          | 2.35    | 2.35
(6 rows)

Mismatched collation example

On the other hand, if you've upgraded from Ubuntu 18.04 to 22.04 without reindexing, you might see:

gitlabhq_production=# SELECT collname AS COLLATION_NAME,
       collversion AS VERSION,
       pg_collation_actual_version(oid) AS actual_version
FROM pg_collation
WHERE collprovider = 'c';
 collation_name | version | actual_version
----------------+---------+----------------
 C              |         |
 POSIX          |         |
 ucs_basic      |         |
 C.utf8         |         |
 en_US.utf8     | 2.27    | 2.35
 en_US          | 2.27    | 2.35
(6 rows)

Streaming replication

The corrupted index issue affects PostgreSQL streaming replication. You must rebuild all indexes or rebuild only affected indexes before allowing reads against a replica with different locale data.

Additional Geo variations

The above upgrade procedures are not set in stone. With Geo there are potentially more options, because there exists redundant infrastructure. You could consider modifications to suit your use-case, but be sure to weigh it against the added complexity. Here are some examples:

To reserve a secondary site as a warm standby in case of disaster during the OS upgrade of the primary site and the other secondary site:

1. Isolate the secondary site's data from changes on the primary site: Pause the secondary site.
2. Perform the OS upgrade on the primary site.
3. If the OS upgrade fails and the primary site is unrecoverable, then promote the secondary site,
4. route users to it, and try again later.
5. Note that this leaves you without an up-to-date secondary site.

To provide users with read-only access to GitLab during the OS upgrade (partial downtime):

1. Enable Maintenance Mode on the primary site instead of stopping it.
2. Promote the secondary site but do not route users to it yet.
3. Perform the OS upgrade on the promoted site.
4. Route users to the promoted site instead of the old primary site.
5. Set up the old primary site as a new secondary site.

WARNING: Even though the secondary site already has a read-replica of the database, you cannot upgrade its operating system prior to promotion. If you were to attempt that, then the secondary site may miss replication of some Git repositories or files, due to the corrupted indexes. See Streaming replication.