## 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
```text
database "postgres" has a collation version mismatch
```

## Steps

### Step 1: Confirm which databases are affected
```sql
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
```bash
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
```sql
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
```sql
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/pst_z1rj1WHx7Xmw0AYzm_zEvQ