After upgrading a PostgreSQL server to SLES 15 SP3, a REINDEX is required.

This document (000020305) is provided subject to the disclaimer at the end of this document.

Environment

SUSE Linux Enterprise Server 15 SP3

Situation

System is running PostgreSQL database server and is to be upgraded to SLES 15 SP3.
Before using the database productively again after the upgrade, text indices must be re-indexed, or there may be corruption on the first write access to the database.
Reading from the DB does not do any harm to the stored data, but the results may be unexpected or even wrong.

Resolution

Reindex every text index after the upgrade before going productive again.
Note: This can take considerable time depending on size and number of the indices!

Reindexing can be done via "reindexdb" wrapper packaged with postgresql.
Example:

To reindex the table foo in a database named bar:

$ reindexdb --table=foo bar

Reindex all tables in a database:

$ reindexdb -a

Before using, please read the manpages

$ man reindexdb 
$ man 7 reindex

Cause

With glibc 2.28 collations have changed. SLES15 SP3 comes with glibc 2.31 and is the first Service Pack affected.
All subsequent Service Packs and Products will require the same precaution if the DB had been created on a system with a glibc older than 2.28!
Actually there is no bug, but it is a change in behavior which can cause a great deal of trouble.

Additional Information

Databases using only “C” or “POSIX” collations should not be affected.
It does not matter which PostgreSQL version is in use, as all versions can be affected.

This collation issue also affects physical (WAL) replication which is supported by the PostgreSQL core itself, where all systems in a replicated cluster must have the same collations in place!

See also:

https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html
https://wiki.postgresql.org/wiki/Collations
https://simply.name/pg-lc-collate.html

Since version 10, PostgreSQL does not only support “libc collations” but also “ICU collations” which are not affected by this particular problem. See:
https://www.cybertec-postgresql.com/en/icu-collations-against-glibc-2-28-data-corruption/

SUSE Manager Users: Please read:
https://www.suse.com/support/kb/doc/?id=000020290

Disclaimer

This Support Knowledgebase provides a valuable tool for SUSE customers and parties interested in our products and solutions to acquire information, ideas and learn from one another. Materials are provided for informational, personal or non-commercial use within your organization and are presented "AS IS" WITHOUT WARRANTY OF ANY KIND.