After an OS upgrade on a PostgreSQL host, a dictionary app's headwords sort differently and logs warn of a collation version mismatch; what happened, and how do you fix it safely?
answer
- new C library, new sort rules
- indexes keep the old order
- datcollversion versus the actual version
- rebuild first, refresh last
basics
~20 sThe upgrade brought a C library that orders some strings differently, so B-tree indexes built under the old order are now wrong. Rebuild every index depending on the collation, resolve duplicate keys, then run ALTER DATABASE ... REFRESH COLLATION VERSION.
solid answer
~50 sPostgreSQL records the provider's collation version when a database or collation is created, in `pg_database.datcollversion` and `pg_collation.collversion`; for libc on glibc that is the C library's version. The warning `database "dictionary" has a collation version mismatch` means the system now reports a different version, so text comparisons follow new rules while every B-tree index on text still holds the old order. Index scans can then miss rows and a unique index can accept a duplicate headword. I would list the affected indexes (every index with a key under the default collation, found through `pg_index.indcollation`, plus `pg_depend` for named collations), `REINDEX` them, `CONCURRENTLY` on a busy system, clean up duplicates a broken unique index let in, and only then run `ALTER DATABASE dictionary REFRESH COLLATION VERSION`. Refreshing first only hides the warning. Afterwards: identical C library on primary and standbys, or a builtin default.
code
console · 3 linesWARNING: database "dictionary" has a collation version mismatch
DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.36.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE dictionary REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.go deeper
Recall that an OS upgrade can change how text sorts, that PostgreSQL warns about a collation version mismatch, and that indexes on text must then be rebuilt.
Explain where the version is recorded, datcollversion and collversion, how glibc's version stands in for libc collations, and why REFRESH VERSION alone fixes nothing.
Walk the runbook in order: scope, find indexes through indcollation and pg_depend, reindex concurrently, resolve duplicates, refresh last, and remember standbys on another OS.
Own the prevention policy: OS upgrades treated as collation events, aligned C library and ICU versions across all hosts, and a builtin default that keeps exposed indexes few.
## What PostgreSQL records, and when it warns When a database or a collation object is created, PostgreSQL stores the version its provider reports. On every new connection it compares the database's recorded version with what the provider reports now, and the first time a session uses a named collation it does the same for that collation. A difference produces a `WARNING`, not an error, so the application keeps running on possibly corrupt indexes. | | database default collation | a named collation object | |---|---|---| | Recorded in | `pg_database.datcollversion` | `pg_collation.collversion` | | Current version from | `pg_database_collation_actual_version(oid)` | `pg_collation_actual_version(oid)` | | Warning | `database "dictionary" has a collation version mismatch` | `collation "de-x-icu" has version mismatch` | | Clear it with | `ALTER DATABASE ... REFRESH COLLATION VERSION` | `ALTER COLLATION ... REFRESH VERSION` | What "version" means depends on the provider. For libc on glibc, PostgreSQL uses the **C library's version as a proxy**, because distributions usually change collation data only with the C library (the manual warns that back-ported locale changes can slip past this). FreeBSD and Windows also report libc versions; other platforms report none, so no warning can appear there. ICU reports its own collation version on every platform. The builtin provider always reports `1`, because code-point order does not change. ## Why a changed sort order corrupts indexes A B-tree is correct only if comparisons are **immutable**: each key was placed according to the old rules. After the upgrade the same comparison gives different answers for some pairs, typically strings with punctuation, spaces, hyphens or accents, so: - an **index scan** descends to where the new rules say a key belongs, misses rows stored where the old rules put them, and returns too few; - a **unique index** does not find the existing equal key, so a duplicate headword can be inserted; - a query whose order comes from the index and one that sorts afresh can disagree, which users see as the sort order changing; - **range partitions** keyed on text can hold rows that the new rules would route to another partition. The same failure appears without any upgrade when a physical standby runs a different OS major version than its primary: it replays the primary's index pages but compares keys with its own C library, so queries on the standby can go wrong while the primary is healthy. ## Estimating the blast radius - Unique and primary-key indexes on text come first: they are where wrong answers turn into duplicate data. - Expression indexes such as `lower(headword)` and indexes on `citext` columns count too, because their keys are still compared under the collation. - Large indexes decide the window: a concurrent rebuild of a multi-gigabyte index on a busy table takes real time and I/O. - Pure ASCII letters and digits rarely move between C library releases; strings with spaces, punctuation, hyphens and accents are where orders typically differ, which a multilingual dictionary has plenty of. ## A safe remediation runbook 1. **Confirm the scope.** In each database compare `datcollversion` with `pg_database_collation_actual_version(oid)`, and `collversion` with `pg_collation_actual_version(oid)` for named collations. 2. **List affected indexes.** PostgreSQL does not record dependencies on the default collation in `pg_depend`, so use `pg_index.indcollation`, which holds one collation OID per key column; the default collation is OID 100. For named collations, the query in the `ALTER COLLATION` reference page walks `pg_depend`. 3. **Triage if useful.** amcheck's `bt_index_check()`, or the `pg_amcheck` program, reports order violations, and with `checkunique` duplicate visible entries in unique indexes. 4. **Rebuild.** `REINDEX` each affected index, `CONCURRENTLY` on a live system. A unique index that now contains duplicates will not rebuild until those rows are resolved. 5. **Refresh last.** Only then run `ALTER DATABASE dictionary REFRESH COLLATION VERSION`, and `ALTER COLLATION ... REFRESH VERSION` for each named collation. PostgreSQL does not check that anything was rebuilt. ```sql SELECT i.indexrelid::regclass AS index_name FROM pg_index i WHERE 100::oid = ANY (i.indcollation); ``` ## Preventing the next one - Treat an OS major upgrade as a collation event: either build a new host and reload logically, or schedule the reindex inside the upgrade window. - Keep the primary and every physical standby on the same OS major and C library build. - Use the builtin provider for the database default and confine linguistic ICU collations to the columns that need them, so far fewer indexes are exposed. - Pin the ICU package version too, since ICU collations carry the same risk when ICU is upgraded. ## Where this answer stops How `REINDEX CONCURRENTLY` works, its locks and the invalid index it leaves after a failure are index maintenance. Carrying ICU collations through `pg_upgrade` belongs to major-version upgrades. This answer covers recognising the mismatch and deciding what must be rebuilt before the warning is silenced.
- Why can a physical standby return wrong results while its primary is healthy?A physical standby replays the primary's index pages byte for byte but compares keys with its own C library or ICU. If its OS major differs, its comparisons disagree with the order the primary built, so index scans on the standby can miss rows. Keep primary and standbys on the same OS and library versions, and check a suspect standby with amcheck.
- Why does REFRESH COLLATION VERSION fix nothing on its own?It only overwrites the recorded version with the provider's current one, which stops the warning. It neither rebuilds nor checks indexes; the manual says it does not verify that affected objects were rebuilt. Run it last, after every dependent index has been reindexed and duplicate keys have been resolved.
- Why can't pg_depend list the indexes that use the database default collation?PostgreSQL deliberately skips recording dependencies on the default collation, so `pg_depend` only covers named collations. `pg_index.indcollation` holds one collation OID per key column, with 100 for the default, and finds every affected index, including expression indexes such as `lower(headword)`.
It is like a library whose filing rule for hyphenated titles changes overnight while every book stays on its old shelf: a librarian following the new rule looks in the wrong place, reports the book missing, or shelves a second copy. Re-shelving the books (REINDEX) fixes the library; stamping a new date on the rulebook (REFRESH VERSION) only stops the complaints.
- Running ALTER DATABASE ... REFRESH COLLATION VERSION is the fix for the warning.
- Only ORDER BY output changes; index lookups and uniqueness are unaffected.
- PostgreSQL marks the affected indexes invalid automatically after the OS upgrade.
- pg_depend lists every index that uses the database default collation.
- A physical standby is safe on any OS version because it replays the primary's WAL.
- ICU collations never change, so only libc databases ever need a reindex.