collation “…” has version mismatch

Symptoms

PostgreSQL detected that a collation’s version differs from the version recorded when dependent indexes were built. It warns (SQLSTATE 01000) that affected indexes may be corrupt and should be rebuilt.

• The OS/ICU collation library changed versions.
• Indexes built under the old version may sort differently.
• A warning prompting REINDEX.

What the server log shows

WARNING:  collation "en_US" has version mismatch
DETAIL:  The collation in the database was created using version 2.28, but the operating system provides version 2.31.
HINT:  Rebuild all objects affected by this collation and run ALTER COLLATION ... REFRESH VERSION.

Why PostgreSQL raises this — what the manual says

Section 24.2 Collation Support:

“ICU support is required to use this collation, and behavior may change if PostgreSQL is built with a different version of ICU.”

Indexes on text are physically ordered by a collation’s sort rules. If the underlying ICU/libc library is upgraded and its pathkeys/">sort order changes, those indexes may no longer be correctly ordered. PostgreSQL records the version and warns (01000) so you can rebuild before relying on them.

Common causes

• An OS upgrade changing the libc/ICU collation version.
• Restoring/migrating onto a host with a different collation library.
• ICU library upgrade altering sort rules.

How to fix it

1. Rebuild affected indexes: REINDEX (database or specific indexes).
2. Then clear the warning: ALTER COLLATION "en_US" REFRESH VERSION; (or per-database).
3. Standardize collation library versions across hosts in a cluster.

Related & next steps

• SQLSTATE 01000 — Warning (full reference)
• Message: index contains unexpected zero page
• Glossary: Collation

Reference: PostgreSQL 18 Section 24.2 “Collation Support”.