ベースイメージや OS を上げたあと、PostgreSQL に接続するたびに同じ 3 行が出ます。
WARNING: database "postgres" 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 postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.WARNING なので接続は成立し、psql の終了コードは 0 です。クエリも通ります。同じ検索で当たる表示ゆれは次のとおりです。
The database was created using collation version X, but the operating system provides version Y.（DETAIL 行。版番号は環境によって変わります）Rebuild all objects in this database that use the default collation（HINT 行の前半だけが手がかりになっている場合）
直し方：HINT が求める 2 つ
HINT は 2 つを求めています。「デフォルトの照合順序を使うオブジェクトを作り直す」ことと、「ALTER DATABASE ... REFRESH COLLATION VERSION を実行する」ことです。この順で打つと警告は消えます。
$ psql -U postgres -c 'REINDEX DATABASE postgres'
REINDEX
$ psql -U postgres -c 'ALTER DATABASE postgres REFRESH COLLATION VERSION'
NOTICE: changing version from 2.36 to 2.41
ALTER DATABASEREINDEX は本番では止まる操作です。 上流のドキュメントはこう書いています。
REINDEX locks out writes but not reads of the index’s parent table. It also takes an
ACCESS EXCLUSIVElock on the specific index being processed, which will block reads that attempt to use that index. In particular, the query planner tries to take anACCESS SHARElock on every index of the table, regardless of the query, and soREINDEXblocks virtually any queries except for some prepared queries whose plan has been cached and which don’t use this very index.
テーブルへの読み自体は止まりませんが、プランナが各インデックスに ACCESS SHARE を取ろうとするので、実際にはほぼすべてのクエリが待ちます。
HINT には第 2 の選択肢もあります——or build PostgreSQL with the right library version。元の glibc を持つイメージへ戻して動かせば、記録と実際が一致するので警告は出ません。移行を延期できる場合の逃げ道です。
この記事の残りは、その REINDEX が本当に要るのかを、ロックを取る前に測る話です。要るかどうかは、越えた glibc の版境界によって変わります。測りきれなかったときは REINDEX する——それが既定です。
なぜ PostgreSQL 15 以降で出るのか：記録された版と、OS が返す版
このエラーは PostgreSQL のメジャー移行では起きません。postgres:17-bookworm で initdb したデータディレクトリを postgres:17-trixie で起動すると出ます。
$ docker run --rm postgres:17-bookworm postgres --version
postgres (PostgreSQL) 17.10 (Debian 17.10-1.pgdg12+1)
$ docker run --rm postgres:17-trixie postgres --version
postgres (PostgreSQL) 17.10 (Debian 17.10-1.pgdg13+1)PostgreSQL は両方 17.10 です。動いているのは足元の glibc だけです。
$ ldd --version | head -1 # postgres:17-bookworm
ldd (Debian GLIBC 2.36-9+deb12u14) 2.36
$ ldd --version | head -1 # postgres:17-trixie
ldd (Debian GLIBC 2.41-12+deb13u3) 2.41initdb を実行した時点の照合順序の版が、データベースに文字列として記録されます。起動のたびに、PostgreSQL はその記録と、いまリンクしている C ライブラリが名乗る版を突き合わせます。
SELECT datcollate, datcollversion AS recorded,
pg_database_collation_actual_version(oid) AS actual
FROM pg_database WHERE datname = 'postgres'; datcollate | recorded | actual
------------+----------+--------
en_US.utf8 | 2.36 | 2.41比較されているのは、この 2 つの文字列です。 libc プロバイダの場合、actual はリンクしている glibc の版そのものです。ソースの get_collation_actual_version() は gnu_get_libc_version() の値を返し、コメントに Use the glibc version because we don't have anything better. と書いてあります。
上流もこれを近似だと明言しています。 ALTER COLLATION のドキュメントは、C ライブラリの版が照合順序の版の proxy として使われていること、ディストリビューションが古い glibc へ照合定義をバックポートしうることを注記しています。「版が違う＝並び順が変わった」ではないという指摘は上流でも既知で、BUG #18029 が同じ論点を報告しています。
ロケールが C / POSIX / C.UTF-8 のデータベースでは、そもそも版が記録されないので、この警告は出ません。
datname | datcollate | version_not_recorded | datcollversion
----------+------------+----------------------+----------------
d_c | C | t |
d_cutf8 | C.UTF-8 | t |
d_posix | C | t |
postgres | en_US.utf8 | f | 2.41PostgreSQL 14 以前は、同じことが起きても黙っている
データベース単位の照合順序バージョン記録（pg_database.datcollversion）は PostgreSQL 15 の新機能です。14 以前には列そのものがありません。
$ SELECT count(*) FROM information_schema.columns
WHERE table_name = 'pg_database' AND column_name = 'datcollversion';
0 -- postgres:14-bullseye（PostgreSQL 14.18）比較する記録が無いので、同じ glibc 更新をしても 1 行も出ません。 危険が無いのではなく、検知する仕組みが無いだけです。
実際に、並び順が変わる版境界を PostgreSQL 11 でまたぐと、こうなります。
$ psql -U postgres -c 'SELECT 1' # postgres:11-buster、インデックスは壊れている
?column?
----------
1
(1 row)このデータベースのインデックスは、後述のとおり amcheck が破損と判定する状態です。それでも接続時の出力は空です。警告が出ていないことは、インデックスが正しいことの証拠になりません。
測った 2 つの版境界：同じ警告で、実害が正反対になる
警告の文面は、どの版境界を越えたかを区別しません。実際に測ると、答えは正反対になります。
glibc 2.36 → 2.41：測った範囲では並び順が動いていない
ラテン文字・アクセント付き・ギリシャ・キリル・かな・漢字・ハングル・記号・数字・空白・ハイフンを混ぜた 1〜8 文字の文字列を 30,000 件、postgres:17-bookworm（glibc 2.36）で投入してインデックスを張り、postgres:17-trixie（glibc 2.41）で起動して、2 つの経路の並び順を突き合わせました。
-- インデックスに記録された順序（Index Only Scan）
SET enable_seqscan = off;
SET enable_sort = off;
SELECT md5(string_agg(s, chr(10))) FROM (SELECT s FROM w ORDER BY s) t;
-- いまの glibc で並べ直した順序（Seq Scan + Sort）
RESET enable_seqscan;
SET enable_indexscan = off;
SET enable_indexonlyscan = off;
SET enable_bitmapscan = off;
SET enable_sort = on;
SELECT md5(string_agg(s, chr(10))) FROM (SELECT s FROM w ORDER BY s) t;EXPLAIN (COSTS OFF) で、前者が Index Only Scan using w_s_idx on w、後者が Sort + Seq Scan on w になっていることを確かめてあります。結果は 4 つの状態すべてで同じ値でした。
| 状態 | インデックスの順序 | 現行 glibc での順序 |
|---|---|---|
| bookworm（2.36）で作った直後 | aa55ddd3… | aa55ddd3… |
| trixie（2.41）で起動・未修正 | aa55ddd3… | aa55ddd3… |
REFRESH COLLATION VERSION だけ実行 | aa55ddd3… | aa55ddd3… |
REINDEX も実行 | aa55ddd3… | aa55ddd3… |
単一の文字だけを並べても差は出ません。U+0020〜U+FFFF（サロゲートを除く）と U+10000〜U+1FFFF の 128,992 文字を 1 行 1 文字で並べ、同じ入力を両方のイメージで sort にかけると出力が一致します（どちらも f458afe5…）。
この 2 つは範囲が違います。 1 行 1 文字の比較は、複数文字にまたがる規則（contraction、空白や句読点の扱い、複数レベルの重み）を原理的に見ません。多文字の変化を捉えているのは 30,000 件のほうで、単独では全称の根拠になりません。言えるのは「測った集合では差が出なかった」までで、en_US.UTF-8 のすべての文字列について不変だと確かめたわけではありません。
glibc 2.24 → 2.28：57,715 行がずれ、インデックスが実際に壊れる
同じ 128,992 行の入力を postgres:9.6（glibc 2.24）と postgres:11-buster（glibc 2.28）のコンテナ内で sort にかけると、57,715 行が違う位置に並びます。ここで比較しているのはコンテナ内の LC_ALL=en_US.UTF-8 sort で、PostgreSQL は関与しません。
glibc 2.24 : a1, a 1, a-1 | coop, co op, co-op | deluca, de luca, de-luca
glibc 2.28 : a 1, a-1, a1 | co op, co-op, coop | de luca, de-luca, delucaハイフンや空白を含む文字列の位置が、まとめて反転しています。
この境界を PostgreSQL の中でまたぐと、インデックスが実際に壊れます。同じ PostgreSQL 11.13 で glibc だけを変え、postgres:11-stretch（glibc 2.24）で 30,000 件を投入してインデックスを張り、postgres:11-buster（glibc 2.28）で起動しました。
2.24 側で作った直後 インデックス=3962613d… 並べ直し=3962613d…（一致）
2.28 側で起動した後 インデックス=3962613d… 並べ直し=aa55ddd3…（食い違う）amcheck は、この状態を破損として検出します。
$ SELECT bt_index_check('w_s_idx'::regclass, true);
ERROR: item order invariant violated for index "w_s_idx"
DETAIL: Lower index tid=(3,3) (points to index tid=(4,34)) higher index tid=(3,4) (points to index tid=(5,143)) page lsn=0/19331E8.
$ SELECT bt_index_parent_check('w_s_idx'::regclass, true);
ERROR: down-link lower bound invariant violated for index "w_s_idx"
DETAIL: Parent block=3 child index tid=(2,34) parent page lsn=0/19331E8.順序の不変条件が破れているので、heapallindexed を付けなくても落ちます。
glibc 2.28 と 2.36 と 2.41 は、同じ 30,000 件で同じ順序になりました（いずれも aa55ddd3…）。上の 57,715 行の差は 2.24 → 2.28 の 1 段で起きています。glibc 2.28 は 2018 年のリリースで、ロケールデータが大規模に更新されました。Debian の PostgreSQL パッケージメンテナである Christoph Berg は、2019 年 3 月に debian-glibc へこう書いています。
For PostgreSQL, this means that the ordering of indexes on disk is becoming corrupt, and all “text” (varchar, char, …) indexes need to be rebuilt.
放置した場合について、同じ投稿はこう続けます（文中からの引用）。
if that is not done immediately, the tables might become corrupt because some tuples aren’t index-visible anymore due to the incorrect btree ordering
REINDEX が要るかを判定する
既定は REINDEX です。 下のチェックリストを全部通ったときだけ省略できます。1 つでも確認できない項目があれば REINDEX 側に倒してください——判定を誤ったときの損失が非対称だからです。省略して間違えると、REFRESH COLLATION VERSION が警告を消したあとは、食い違いを知らせる仕組みが残りません。
測る前に、いまの記録を控えておきます。
$ psql -U postgres -Atc "SELECT datname, datcollate, datcollversion FROM pg_database"REFRESH COLLATION VERSION を打つと datcollversion が上書きされ、どの版で作られたインデックスなのかが分からなくなります。判定をやり直せなくなるので、先に控えます。
1. ロケールが効いていることを確かめる
sort を使う判定には、黙って答えを変える経路があります。LC_ALL に指定したロケールがイメージ内で生成されていないと、GNU sort は診断を出さずに C ロケール（バイト順）で動きます。
$ docker run --rm postgres:17-trixie sh -c 'locale -a | grep -ic ja_JP'
0
$ printf 'a\nB\nb\nA\n' | LC_ALL=ja_JP.UTF-8 sort # 生成されていない
A B a b # C ロケールの結果。終了コードは 0、警告も出ない公式イメージが生成しているのは en_US.UTF-8 だけです。ja_JP.UTF-8 などのデータベースを持っている場合、指定したとおりに指定した結果として、両側とも C で並びます。 差が出るはずの環境でも diff が空になります。
必ず先にカナリアを通してください。大小の混在で判定します。
$ printf 'a\nB\nb\nA\n' | LC_ALL=<自分のロケール> sort | tr '\n' ' '
a A b B # ロケールが効いている
A B a b # 落ちている。この結果のまま先へ進まないロケールが無いイメージでは、localedef で生成してから測るか、この判定法をあきらめて REINDEX してください。
2. 実データで新旧を突き合わせる
$ psql -U postgres -Atc 'SELECT s FROM w' > /tmp/words.txt
$ docker run --rm -v /tmp/words.txt:/w.txt:ro <移行前のイメージ> \
sh -c 'LC_ALL=en_US.UTF-8 sort /w.txt' | md5sum
$ docker run --rm -v /tmp/words.txt:/w.txt:ro <移行後のイメージ> \
sh -c 'LC_ALL=en_US.UTF-8 sort /w.txt' | md5sumLC_ALL には datcollate に記録されているロケールを渡します。sort の結果を PostgreSQL の並び順の代わりに読めるのは、どちらも同じ C ライブラリの同じロケールデータで比較しているからです。
この手順には範囲の限界が 2 つあります。
- いまテーブルに入っている値しか見ません。 照合規則の差は、まだ入っていない文字種にだけ出ることがあります（2.28 の変化はハイフンと空白が代表で、氏名にハイフンが 1 つも無ければ差は出ません）。値域が閉じていない列は
REINDEX側に倒してください。 - 改行を含む text 値があると、行区切りの出力では値の境界が失われます。 そういう列がある場合、この比較は使えません。
3. amcheck でインデックスそのものを検査する
前節のとおり、amcheck は照合順序の変化による破損を実際に検出します。公式イメージには拡張が同梱されています。
ただし CREATE EXTENSION amcheck は読み取り専用の検査ではありません。 amcheck は trusted extension ではないので作成には通常 superuser 権限が要り、実行するとそのデータベースに拡張が恒久的に残ります（pg_dump にも含まれます）。導入済みかを先に確認してください。
SELECT extname FROM pg_extension WHERE extname = 'amcheck';SELECT c.relname,
bt_index_check(i.indexrelid, heapallindexed => true)
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relam = (SELECT oid FROM pg_am WHERE amname = 'btree')
AND c.relkind = 'i'
AND i.indisready
AND i.indisvalid
AND c.relpersistence <> 't';relkind と indisready / indisvalid の条件は公式の例に合わせたものです。無効なインデックス（CREATE INDEX CONCURRENTLY が失敗した残骸など）を対象にすると関数がエラーを返し、照合順序の破損と見分けが付かなくなります。
bt_index_check が取るのは AccessShareLock で、上流のドキュメントは「単純な SELECT 文が関係に対して取得するのと同じロックモード」と書いています。同じ拡張の bt_index_parent_check は ShareLock を取り、INSERT / UPDATE / DELETE を止めます。 また heapallindexed => true は公式に「数倍の時間がかかる」とされ、欠落の検出率は maintenance_work_mem に依存します。本番で全インデックスに流す前に、対象を照合順序に依存するユーザーテーブルへ絞ってください。
通ったことは安全証明ではありません。 上流のドキュメントは、検査が証明できるのは破損の存在であって不在ではないと明記しています。
なお、REINDEX DATABASE が作り直すのはユーザーテーブルのインデックスだけです。上流のドキュメントは Recreate all indexes within the current database, except system catalogs. Indexes on system catalogs are not processed. と書いており、システムカタログには REINDEX SYSTEM が要ります。上の検査クエリはカタログのインデックスも対象に含みますが、カタログの識別子は name 型で C 照合順序を使うので、glibc の更新では動きません——検査が通っても、それは en_US.UTF-8 のユーザーデータについて何も言っていません。
4. 範囲を自分の環境に合わせる
この記事が測ったのは en_US.UTF-8 だけです。 日本語環境で多い ja_JP.UTF-8 は測っていません。ロケールごとに照合テーブルが違うので、en_US.UTF-8 で 2.36 → 2.41 が動かなかったことは、他のロケールで動かない根拠になりません。 自分の datcollate そのもので測ってください。
REFRESH COLLATION VERSION だけを実行したときに起きること
$ psql -U postgres -c 'ALTER DATABASE postgres REFRESH COLLATION VERSION'
NOTICE: changing version from 2.36 to 2.41
ALTER DATABASE
$ psql -U postgres -c 'SELECT 1'
?column?
----------
1
(1 row)警告は止まります。このコマンドが書き換えるのは pg_database.datcollversion の文字列だけで、インデックスには触れません。並び順が動いていた場合、このコマンドは警告を消したうえで、誤った並びのインデックスをそのまま残します。 PostgreSQL はもう版を比較しないので、次の起動でも何も言いません。
Kali Linux のドキュメントが案内しているのは、postgres と template1 の両方に REFRESH COLLATION VERSION を打つ 1 行だけで、インデックスを作り直す手順はありません。前節の判定を通していないなら、この手順が保証するのは「警告が消えたこと」だけです。
切り分け
C/POSIX/C.UTF-8のデータベースで出た：これらのロケールでは版が記録されないので、この警告は出ません。datcollateを確認してください。builtin プロバイダのC.UTF-8（datlocproviderがb）は版が1で、libc の話とは別です。- PostgreSQL 14 以前を使っている：この警告は出ません。出ないことは安全を意味しないので、glibc の版境界をまたいだなら前節の判定を通してください。
- ログが警告で埋まる：
WARNINGは接続のたびに出ます。接続プールが張り直すたびに 1 組ずつ増えます。記録を合わせるか、元の glibc へ戻すかのどちらかで止まります。 - データベース単位ではなく、個々の照合順序について出ている：
CREATE COLLATIONで作った照合順序にも版が記録され、更新にはALTER COLLATION ... REFRESH VERSIONを使います。この記事で再現したのはデータベース単位の形だけで、そちらの形は測っていません。 REINDEX DATABASEがロック待ちで進まない：対象テーブルへの参照が残っています。PostgreSQL 12 以降にはREINDEX ... CONCURRENTLYがありますが、この記事では所要時間もロックの挙動も測っていません。- ICU を照合プロバイダに使っている（
datlocproviderがi）：版を記録して比較する仕組みは同じですが、版番号は ICU のものになり、動く条件も glibc とは別です。この記事の実測はすべて libc プロバイダです。 pg_upgradeが別のエラーで止まる：PostgreSQL のメジャー移行では、照合順序より前に止まる項目があります（old cluster does not use data checksums but the new one does の直し方）。
検証環境
postgres@sha256:9b18b783…（17-bookworm／glibc 2.36）でinitdb→postgres@sha256:7958605b…（17-trixie／glibc 2.41）で起動、ネットワーク遮断（イメージの pull のみ）。PostgreSQL は両方 17.10- 対照に使った版：
postgres:9.6（glibc 2.24）、postgres:11-stretchとpostgres:11-buster（どちらも PostgreSQL 11.13、glibc 2.24 と 2.28）、postgres:14-bullseye（PostgreSQL 14.18） - 再現：
psql -c "SELECT 1"がhas a collation version mismatchを出す（WARNINGなのでpsql自体は終了コード 0。ハーネスは警告の有無を判定して終了コードに変換している） - 修正：
REINDEX DATABASE postgresとALTER DATABASE postgres REFRESH COLLATION VERSIONを実行すると警告が消え、終了コード 0
再現から修正までは errfix の検証ハーネスが機械的に確認しています。次の観測も同じ手順で得たものです——PostgreSQL が両イメージで 17.10 であること、記録された版が 2.36・OS が返す版が 2.41 になること、30,000 件の多言語文字列でインデックスの順序と現行 glibc の順序が 4 つの状態すべてで一致すること、128,992 文字の並びが glibc 2.36 と 2.41 で完全に一致し 2.24 と 2.28 では 57,715 行ずれること、同じ PostgreSQL 11.13 で glibc 2.24 から 2.28 へ移すとインデックスの順序と並べ直しが食い違い bt_index_check と bt_index_parent_check がエラーを返すこと、その状態でも接続時に警告が出ないこと、PostgreSQL 14.18 に datcollversion 列が無いこと、C / POSIX / C.UTF-8 では版が記録されないこと、生成されていないロケールを渡した sort が終了コード 0 のまま C ロケールで並ぶこと、REFRESH COLLATION VERSION が NOTICE: changing version from 2.36 to 2.41 を出して警告を止めること。
REINDEX のロック、bt_index_check と bt_index_parent_check のロックの差、heapallindexed の所要時間と maintenance_work_mem 依存、REINDEX DATABASE がシステムカタログを処理しないこと、照合順序の版が C ライブラリの版の proxy であることは、PostgreSQL 17 の REINDEX / amcheck / ALTER COLLATION の各ドキュメントによります（実測ではなくドキュメントの記述）。glibc 2.28 の更新が PostgreSQL のインデックスに与えた影響についての引用は、Christoph Berg が 2019 年 3 月 25 日に debian-glibc へ投稿したものです。
実運用規模での REINDEX の所要時間は計測していません。 ここで測ったのは 30,000 行・1 索引だけで、停止時間の見積もりには使えません。en_US.UTF-8 以外のロケールも測っていません——ロケールごとに照合テーブルが異なるので、他のロケールで 2.36 → 2.41 が動かない根拠にはなりません。ICU プロバイダ、REINDEX ... CONCURRENTLY、2.24 → 2.28 以外の個々の版境界（2.31 → 2.36 など）、並び順以外への影響（LIKE の範囲最適化、パーティション制約、一意制約の判定）も測っていません。