# Postgres Collation Version Mismatch in Docker: Is Your Data Safe?

> On default en_US.utf8, bookworm to trixie warned and nothing moved in my tests. On ICU or from Alpine, rows went missing. Check with pg_amcheck, then REINDEX.

Published: 2026-11-07. Author: Milan Jovanović.

Canonical: https://milanjovanovic.tech/blog/postgres-collation-version-mismatch-docker

It depends on your locale and provider: on the default `en_US.utf8`, moving from `17-bookworm` to `17-trixie` prints the warning, and nothing moved in my tests.
The same tag change on ICU, or a move from Alpine, returned wrong rows and let duplicates through a unique index.
Check yours with `pg_amcheck`, then `REINDEX`, then refresh.

I ran this on November 7, 2026, with PostgreSQL 17.11 images on Docker Desktop 29.6.1 under WSL2, not on a real VPS.
The test table was `customers`, 1,000,000 rows with a unique index on `email` and text indexes on `name` and `(city, name)`.
I didn't test partition bounds, CHECK constraints, generated columns, or materialized views, and of the glibc locales that changed, only `lv_LV` ran against real indexes.

## Is Your Data at Risk?

An index stores rows in the order the collation gave when they were inserted.
If the new library compares some strings differently, a lookup walks the tree the new way and misses rows that are there.
The unique check walks the same tree, so a duplicate can get in.

This is what happened on each change, on the same volume:

| Change | Warning | amcheck | Result |
| --- | --- | --- | --- |
| 17-bookworm to 17-trixie, default en_US.utf8 | 2.36 to 2.41 | Passed | No order change found, duplicates rejected |
| Same tags, ICU, straight and curly apostrophes in one index | 153.120 to 153.128 | Failed | Apostrophe rows missing from lookups, duplicates inserted |
| Same tags, ICU, one apostrophe shape or none in a table | 153.120 to 153.128 | Passed | No missed lookups |
| Same tags, lv_LV added as the image README shows | 2.36 to 2.41 | Failed | Email lookups missed 649 of 2,000, and 649 of 2,000 duplicates inserted |
| 17-alpine to 17-trixie | None | Failed | Email lookups missed 1,096 of 2,000, and 1,100 of 2,000 duplicates inserted |
| Builtin provider or C locale, bookworm to trixie | None | Passed | No recorded version moves with the OS |

I tested duplicates by inserting existing emails a second time, and those counts depend on my seed data, so they aren't rates.

On `en_US.utf8` I looked hard for a change and found none.
The `en_US` collation section has the same hash on both images, and `sort` inside each image agreed on 376,400 lines of names, emails, and real text, as did the server's `ORDER BY`.

The test can see a change, because `lv_LV` moved 3,291 of those lines and `th_TH` moved 107,800.
`LC_COLLATE` also differs for `ber_DZ`, `cv_RU`, and `kab_DZ`.

The `en_US` result covers 2.36 to 2.41, and bullseye's 2.31 gave the same answer.
I found no change, but I can't rule one out for your strings, and the [**amcheck docs**](https://www.postgresql.org/docs/18/amcheck.html) say it can show corruption exists but can't show there is none.

Postgres treats the C library's version as a proxy for the collation version, and the [**Postgres wiki**](https://wiki.postgresql.org/wiki/Locale_data_changes) still advises reindexing after a glibc upgrade.
The wiki names glibc 2.28 as the major break, which I couldn't reach on current tags.

ICU moved with the OS, from ICU 72 to ICU 76, and it changed how the two apostrophes sort:

```text
O Aaron | O-Aaron | O'Aaron | O'Zed | O’Aaron | O’Zed | OAaron
O Aaron | O-Aaron | O'Aaron | O’Aaron | O'Zed | O’Zed | OAaron
```

A table with only 1% straight and 0.1% curly apostrophes still failed amcheck, and retrying its apostrophe rows inserted 295 duplicates.
Its ordinary rows inserted none.
I only tried apostrophes on real indexes, and 46,302 of 551,368 test strings moved in a wider comparison, so a pass on my tables doesn't clear other characters.

Alpine is the worst case, because it broke the most and nothing warned me.
It records no collation version, and its `en_US.utf8` looks like byte order in my output, so this index range found nothing:

```text
index  name >= 'a' AND name < 'b'|0
table  name >= 'a' AND name < 'b'|134328
```

## What Does the Warning Mean?

Postgres records the library's collation version when it creates a database, then compares it with the running library on every connection:

```text
WARNING:  database "shop" has a collation version mismatch
DETAIL:  The database was created using collation version 2.36, but the operating system provides version 2.41.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE shop REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
```

You usually see it with no Postgres upgrade at all.
A maintainer attributes it to the images moving to Debian trixie in [**this issue**](https://github.com/docker-library/postgres/issues/1356), because tags without a Debian name follow the newest release.

Compare recorded and actual versions for every database:

```bash
docker compose exec -T postgres psql -U postgres -d postgres -Atc "SELECT datname, datlocprovider, datcollate, datcollversion, pg_database_collation_actual_version(oid) FROM pg_database ORDER BY 1;"
```

```text
postgres|c|en_US.utf8|2.36|2.41
shop|c|en_US.utf8|2.36|2.41
template0|c|en_US.utf8||2.41
template1|c|en_US.utf8|2.36|2.41
```

The second column is the provider: `c` is the C library, `i` is ICU, `b` is builtin.
An ICU database still shows `en_US.utf8` in the third column, so go by the second.
Apart from `template0`, an empty recorded version next to a real actual version is what my Alpine volume looked like.

The mismatch costs you something even when the sort didn't change.
`createdb` fails with `template database "template1" has a collation version mismatch`, and `CREATE DATABASE appdb TEMPLATE template0;` is the workaround.
Every connection logs the warning too, so a `pg_isready` healthcheck at 1 second added 30 warnings in 30 idle seconds, at three log lines each.

## How Do You Check Your Own Indexes?

Run this before you change anything, once per database:

```bash
docker compose exec -T postgres pg_amcheck -U postgres -d shop --install-missing --heapallindexed
```

It keeps going past a failing index, lists each one, and exits `2` if anything failed.
`--install-missing` creates the `amcheck` extension, so it's a small schema change.
Leave `--parent-check` off on a live server, because per the docs it takes a `ShareLock` that blocks writes.

A damaged index reports `item order invariant violated`.
A clean run means amcheck found nothing, and the rebuild below is still the way to clear the warning.

## How Do You Fix It Safely?

Plain `REINDEX` blocks the table while its indexes rebuild.
On 4,000,000 rows with a 500 ms `lock_timeout`, most reads and writes timed out during the rebuild, and only ones landing as it ended succeeded.
On a server taking traffic, use `REINDEX DATABASE CONCURRENTLY shop;` in place of the plain form below, because with the concurrent form on that table all 6 reads and all 6 writes succeeded in each of 2 runs.

On a 1 GB database on local disk, plain `REINDEX DATABASE` took 5.5 to 5.7 seconds and `CONCURRENTLY` took 6.2 to 8.4, three runs each.

Run `REINDEX` first, then refresh, on each database that warns:

```bash
docker compose exec -T postgres psql -U postgres -d shop -q -c "REINDEX DATABASE shop;"
docker compose exec -T postgres psql -U postgres -d shop -q -c "ALTER DATABASE shop REFRESH COLLATION VERSION;"
docker compose exec -T postgres psql -U postgres -d postgres -q -c "REINDEX DATABASE postgres;"
docker compose exec -T postgres psql -U postgres -d postgres -q -c "ALTER DATABASE postgres REFRESH COLLATION VERSION;"
docker compose exec -T postgres psql -U postgres -d template1 -q -c "REINDEX DATABASE template1;"
docker compose exec -T postgres psql -U postgres -d template1 -q -c "ALTER DATABASE template1 REFRESH COLLATION VERSION;"
```

The order matters because the refresh only rewrites the recorded version, and the [**docs**](https://www.postgresql.org/docs/18/sql-altercollation.html) say it doesn't check whether anything was rebuilt.
On the ICU volume, refreshing first removed the only warning, while amcheck kept failing with the same errors and lookups kept missing the same rows:

```text
NOTICE:  changing version from 153.120 to 153.128
ERROR:  item order invariant violated for index "customers_email_key"
 sampled | name_lookup_missed | email_lookup_missed 
---------+--------------------+---------------------
    2000 |                447 |                 433
```

`template0` needs no refresh, and trying gives `ERROR:  invalid collation version change`.
A volume that came from Alpine gives the same error, and `REINDEX` alone fixed it.

A column with its own ICU collation in an otherwise libc database has its own warning, and the database refresh doesn't touch it.
This query from the docs lists what depends on a changed collation, and the fix is `REINDEX` on those indexes, then `ALTER COLLATION ... REFRESH VERSION`:

```sql
SELECT pg_describe_object(refclassid, refobjid, refobjsubid), pg_describe_object(classid, objid, objsubid) FROM pg_depend d JOIN pg_collation c ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid WHERE c.collversion <> pg_collation_actual_version(c.oid) ORDER BY 1, 2;
```

## What If REINDEX Fails With a Duplicate Key?

Once duplicates are in, the unique index can't rebuild:

```text
ERROR:  could not create unique index "customers_email_key"
DETAIL:  Key (email)=(cēsis.979504@example.com) is duplicated.
```

`REINDEX` stops there and the bad indexes stay in place, all three still failing amcheck.
With `CONCURRENTLY`, the failure also leaves an invalid `customers_email_key_ccnew` behind, so drop it before you retry:

```sql
DROP INDEX CONCURRENTLY IF EXISTS customers_email_key_ccnew;
```

Find the duplicates with index scans off, because the damaged index can't be trusted:

```sql
SET enable_indexscan = off; SET enable_bitmapscan = off; SET enable_indexonlyscan = off;
SELECT email, count(*), min(id) AS keep_id, max(id) AS extra_id FROM customers GROUP BY email HAVING count(*) > 1 ORDER BY 1 LIMIT 3;
```

```text
          email           | count | keep_id | extra_id 
--------------------------+-------+---------+----------
 abols.107297@example.com |     2 |  107297 |  1001709
 abols.135241@example.com |     2 |  135241 |  1000468
 abols.149017@example.com |     2 |  149017 |  1000774
```

My lab kept the oldest row because its rows were generated, so treat this as a shape and not a command to paste.
Decide which row wins, take a dump first, and check any foreign keys pointing at the rows you remove:

```sql
SET enable_indexscan = off; SET enable_bitmapscan = off; SET enable_indexonlyscan = off;
DELETE FROM customers c USING (
  SELECT email, min(id) AS keep_id FROM customers GROUP BY email HAVING count(*) > 1
) d
WHERE c.email = d.email AND c.id <> d.keep_id;
```

After that delete, `REINDEX` exited `0`, lookups missed `0` of 2,000, and a new duplicate was rejected again.
Then refresh.

## Does a Major Upgrade Clear the Mismatch?

Dump and restore does, and `pg_upgrade` doesn't.
`pg_upgrade` carries the old recorded version into the new cluster, so `17-bookworm` through the trixie-based upgrade image still warned on 18, with `shop|2.36|2.41`.
A dump restored into a fresh 18 volume recorded `2.41`, with no warning.

That's one more reason [**dump and restore**](https://milanjovanovic.tech/blog/upgrade-postgres-docker-compose) is the route to start with, as long as you carry your locale arguments to the new service.

## How Do You Prevent It?

On ICU with both apostrophes, putting the old tag back isn't an undo once the new image has taken writes.
Going back after reads alone passed amcheck.
After inserts on trixie, `17-bookworm` printed no warning, amcheck failed, and lookups missed rows.

`REINDEX` on bookworm cleared amcheck, and default `en_US.utf8` passed each time.
So run `pg_amcheck` after a tag change in either direction, and pin the tag you fixed on.

Name the OS in the tag, so the base image changes only when you decide:

```yaml
    image: postgres:17-trixie
```

This is what the tags pointed at when I read them on November 7, 2026:

| Tag | OS | glibc | ICU | PostgreSQL |
| --- | --- | --- | --- | --- |
| 16, 16-trixie | Debian 13 | 2.41 | 76 | 16.15 |
| 17, 17-trixie, 17.11, 17.11-trixie | Debian 13 | 2.41 | 76 | 17.11 |
| 18, 18-trixie, 18.6, latest | Debian 13 | 2.41 | 76 | 18.6 |
| 16-bookworm, 17-bookworm, 18-bookworm | Debian 12 | 2.36 | 72 | 16.15, 17.11, 18.6 |
| 16-bullseye, 17-bullseye | Debian 11 | 2.31 | 67 | 16.9, 17.5, behind current minors |
| 17-alpine, 18-alpine | Alpine 3.24 | musl 1.2.6 | 78 | 17.11, 18.6 |

`postgres:17.11` has the same image ID as `17-trixie` today, but it pins Postgres and not the OS.
The maintainer expects plain tags to move again at Debian Forky.
A pin has a shelf life too, as the bullseye tags stuck at 17.5 and 16.9 suggest, so plan the OS move and run the checks above when you make it.

For a new database, the builtin provider records nothing that moves with the OS.
Set it on the first start of an empty volume:

```yaml
    environment:
      POSTGRES_INITDB_ARGS: "--locale-provider=builtin --builtin-locale=C.UTF-8 --locale=C.UTF-8"
```

That gave `shop|b|C.UTF-8|1|1` on both images, with no warning.
It needs Postgres 17 or newer, and the [**docs**](https://www.postgresql.org/docs/18/collation.html) promise stability only within a major version.
The trade is a sort that puts `Z` before `a`, which matters for any user-facing `ORDER BY`, and I didn't test moving an existing database to it.

```text
db_builtin sort|-y A B Z _x a b z é
db_libc sort|a A b B é _x -y z Z
```

A column collation inside a builtin database still depends on glibc, and it warned after the tag change.

## Summary

On default `en_US.utf8` from bookworm to trixie I found no change, while ICU with mixed apostrophes, an added `lv_LV` locale, and a move from Alpine returned wrong rows and accepted duplicates.

Run `pg_amcheck` on each database, then `REINDEX`, `CONCURRENTLY` on a live server, and only then refresh the version, because a refresh on its own hides damage.

Pin the OS in your tag.
These results come from containers under WSL2 on my data, so test the change on a copy of your own volume first.

My [**self-hosting course**](https://milanjovanovic.tech/pragmatic-self-hosting) builds a monthly maintenance routine that covers backups and updates.

Thanks for reading.

And stay awesome!

---

## Frequently asked questions

### Is my data at risk after a Postgres collation version mismatch warning?

It depends on the locale and provider. On default en_US.utf8, moving from bookworm to trixie changed nothing I could find. On ICU with mixed apostrophes, on an added lv_LV locale, and on a move from Alpine, lookups missed rows and unique indexes let duplicates in. Run pg_amcheck to check your own database.

### Does ALTER DATABASE REFRESH COLLATION VERSION fix the problem?

No. It only updates the recorded version and removes the warning. Where the order really moved, index checks failed with the same errors afterwards and lookups still missed rows. Run REINDEX first, then refresh.

### Why did the warning appear after a Docker image update?

Plain tags such as postgres:17 moved to Debian 13 trixie, which ships a newer glibc and ICU. The same data directory then reports a different collation version from the one recorded when the database was created, with no Postgres version change involved.

### Does no warning mean my indexes are fine?

No. A volume moved from postgres:17-alpine to postgres:17-trixie printed no warning at all, because Alpine records no collation version. It was the worst case I tested, with lookups missing rows and more than half of the duplicate inserts accepted.

### Does using ICU avoid the collation version mismatch?

No. ICU warned and broke on the same tag change, because ICU moved with the operating system. The builtin provider and the C locale record nothing that moves with the OS, though builtin needs Postgres 17 or newer and sorts Z before a.

### How do I stop this happening again?

Name the OS in the tag, such as postgres:17-trixie, so the base image changes only when you decide. A tag like postgres:17.11 pins Postgres and not the OS. For a new database, the builtin provider removes the dependency on the OS.