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 onlyC” 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.

  • Document ID:000020305
  • Creation Date: 28-Jun-2021
  • Modified Date:29-Jun-2021
    • SUSE Linux Enterprise Server

< Back to Support Search

For questions or concerns with the SUSE Knowledgebase please contact: tidfeedback@suse.com

SUSE Support Forums

Get your questions answered by experienced Sys Ops or interact with other SUSE community experts.

Join Our Community

Support Resources

Learn how to get the most from the technical support you receive with your SUSE Subscription, Premium Support, Academic Program, or Partner Program.


SUSE Customer Support Quick Reference Guide SUSE Technical Support Handbook Update Advisories
Support FAQ

Open an Incident

Open an incident with SUSE Technical Support, manage your subscriptions, download patches, or manage user access.

Go to Customer Center