Read more

Mind your locales with glibc upgrades when using PostgreSQL

Avatar
Florian Heinle
December 16, 2022Software engineer at makandra GmbH

When changing the glibc version, it's possible that the upgrade also includes changes to how locales work.

Illustration money motivation

Opscomplete powered by makandra brand

Save money by migrating from AWS to our fully managed hosting in Germany.

  • Trusted by over 100 customers
  • Ready to use with Ruby, Node.js, PHP
  • Proactive management by operations experts
Read more Show archive.org snapshot

This is especially relevant when using PostgreSQL databases (of any version) Show archive.org snapshot , since those depend on the locales functionality provided by glibc as well. It's recommended to re-build all indexes, to be sure no index that depends on collation features is left in an inconsistent state.

Am I affected?

There has been a big change in glibc regarding locales between the minor releases 2.27 and 2.28. Databases that have been created on operating systems with glibc < 2.28 and later upgraded to any version >= 2.28 are most likely affected.

To find out which glibc version you're running, use the following command:

$ ldd --version
ldd (Ubuntu GLIBC 2.35-0ubuntu3.1) 2.35
#[…]

For example, Ubuntu 18.04 LTS ships with 2.27 and the next Ubuntu LTS version 20.04 ships with 2.31. Upgrading operating systems between those versions will include this change in locales.

If there has not been any upgrade to the OS hosting the database server at all, you're not affected.

Only indexes that rely on the collation feature, e.g. with strings, are affected. Indexes that do not, i.e. those based solely on integers or other numbers are most likely not affected.

How to fix the problems

Postgres documentation recommends to recreate indexes involving string-like data types Show archive.org snapshot :

  • All indexes involving columns of type text, varchar, char, and citext should be reindexed before the instance is put into production.
  • Range-partitioned tables using those types in the partition key should be checked to verify that all rows are still in the correct partitions. (This is quite unlikely to be a problem, only with particularly obscure partitioning bounds.)
  • To avoid downtime due to reindexing or repartitioning, consider upgrading using logical replication.
Posted by Florian Heinle to makandra Operations (2022-12-16 15:13)