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

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:

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 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 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:

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:

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:

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, because tags without a Debian name follow the newest release.

Compare recorded and actual versions for every database:

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;"

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:

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:

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 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:

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:

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:

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:

DROP INDEX CONCURRENTLY IF EXISTS customers_email_key_ccnew;

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

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;

          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:

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 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:

    image: postgres:17-trixie

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

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:

    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 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.

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 builds a monthly maintenance routine that covers backups and updates.

Thanks for reading.

And stay awesome!