database "postgres" has a collation version mismatch fix

TL;DR

After a glibc or ICU upgrade, Postgres warns that a database's collation version no longer matches the libraries, and indexes built under the old collation can return wrong sort orders. Reindex the database, then run ALTER DATABASE ... REFRESH COLLATION VERSION to record the new version. Do the reindex first; refreshing without it just silences the warning while the bad indexes stay bad.

Error / query

database "postgres" has a collation version mismatch

Steps

Step 1: Confirm which databases are affected

SELECT datname,
       datcollversion AS recorded_version,
       pg_collation_actual_version(datcollate) AS library_version
FROM pg_database;

Expected: rows where recorded_version differs from library_version are the affected databases. The postgres database will be one of them; template1 often is too. Leave template0 alone.

Step 2: Connect as a superuser or the database owner

psql -h [db-host] -U [admin-user] -d postgres

Expected: a working psql session. You need enough privilege to reindex; a plain read-only role wont cut it.

Step 3: Reindex the database

REINDEX DATABASE postgres;

Expected: the command completes and reports success. This rebuilds every index under the current library collation. It takes access locks, so run it in a maintenance window, and note it cannot run inside a transaction block.

Step 4: Refresh the recorded collation version

ALTER DATABASE postgres REFRESH COLLATION VERSION;

Expected: ALTER DATABASE confirmation. This tells Postgres the database now matches the installed libraries, which clears the warning.

Step 5: Repeat for template1 and any other affected databases

Run steps 3 and 4 with each affected database name from step 1 in place of postgres. Expected: every mismatched row from step 1 now shows matching versions.

Step 6: Verify the warning is gone

Re-run the query from step 1 and check the server logs for new connections. Expected: recorded and library versions match everywhere, and the mismatch warning stops appearing.

When to use

• Postgres logs or clients warn about a collation version mismatch after an OS, glibc, or ICU upgrade
• You upgraded Postgres in place (pg_upgrade) or the host libraries changed underneath it
• pg_database shows datcollversion differing from the actual library version

When not to use

• Indexes are corrupt for other reasons (disk issues, crashes); reindexing helps but the collation steps are irrelevant
• The database was created with the wrong locale or encoding from the start; that needs a dump and reload, not a refresh
• You see collation errors on a version of Postgres older than 15; REFRESH COLLATION VERSION doesnt exist there

Tool compatibility

PostgreSQL 15 and newer (the REFRESH COLLATION VERSION command and datcollversion tracking were added in 15); psql or any SQL client that can run one statement per session; applies to self-hosted Postgres, Docker images rebuilt on newer base OS layers, and managed instances where you hold superuser or owner rights.

Variant phrasings

"WARNING: database postgres has a collation version mismatch, but the operating system provides version X"

Same fix. The version numbers in the warning just confirm what step 1 shows; the reindex-then-refresh sequence is unchanged.

"collation version mismatch after apt upgrade postgres"

Classic trigger: the OS upgraded glibc underneath a running Postgres. Steps 3 and 4, during a quiet window.

"should I reindex after glibc upgrade postgres"

Yes. Indexes on text columns can silently misorder after a collation change, which is worse than the warning. Reindex first, then refresh the recorded version.

Why it happens

String sorting and comparison depend on the OS collation libraries (glibc or ICU), not just on Postgres. When those libraries change version, the sort rules can change, so indexes built under the old rules may return rows in the wrong order. Postgres 15 and newer record the library version per database and warn when it no longer matches, telling you to rebuild the affected objects and then record the new version.

Edge cases

• REINDEX DATABASE cannot run inside a transaction block; some migration tools wrap everything in one, so run these statements directly in psql.
• Large databases: reindexing takes time and locks; for a big production database, reindex the largest indexes concurrently first, then refresh.
• The warning can also appear for template1; fix it the same way so future created databases dont inherit the stale version. Never run it against template0.
• If the mismatch returns after a later OS upgrade, the libraries changed again; repeat the sequence.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstz1rj1WHx7Xmw0AYzmzEvQ