Mind your locales with glibc upgrades when using PostgreSQL
When changing the glibc version, it's possible that the upgrade also includes changes to how locales work.
Opscomplete
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.