Repair PostgreSQL databases after the glibc collation change #55

Open
opened 2026-11-10 19:47:05 +00:00 by martin · 0 comments
Owner

Summary

PostgreSQL on nidaros reports a collation version mismatch on most databases: they were created with glibc collation 2.40 while the OS now provides 2.42. Text indexes built under the old collation can produce incorrect ordering and range results, so the databases need reindexing and a collation version refresh.

Context

Found while triaging kitchenowl-backend journal noise:

WARNING:  database "kitchenowl" has a collation version mismatch
DETAIL:  The database was created using collation version 2.40, but the operating system provides version 2.42.

Databases still at 2.40: postgres, forgejo, keycloak, atuin, linkwarden, headscale, grafana, kitchenowl, woodpecker, nextcloud, hass, freshrss. template1 and vikunja already report 2.42. Largest is woodpecker at 559 MB; kitchenowl is 12 MB.

Scope

  • Reindex each affected database, then refresh its stored collation version.
  • Verify that no collation mismatch warning remains on new connections.
for db in forgejo keycloak atuin linkwarden headscale grafana kitchenowl woodpecker nextcloud hass freshrss postgres; do
  sudo -u postgres reindexdb --verbose "$db"
  sudo -u postgres psql -d template1 -c "ALTER DATABASE \"$db\" REFRESH COLLATION VERSION;"
done

Acceptance criteria

  • REINDEX DATABASE completed for every affected database.
  • ALTER DATABASE "<db>" REFRESH COLLATION VERSION run for every affected database.
  • select datname, datcollversion from pg_database shows 2.42 for all non-template databases.
  • No collation mismatch warnings in the PostgreSQL log after reconnecting.
## Summary PostgreSQL on nidaros reports a collation version mismatch on most databases: they were created with glibc collation 2.40 while the OS now provides 2.42. Text indexes built under the old collation can produce incorrect ordering and range results, so the databases need reindexing and a collation version refresh. ## Context Found while triaging kitchenowl-backend journal noise: ```text WARNING: database "kitchenowl" has a collation version mismatch DETAIL: The database was created using collation version 2.40, but the operating system provides version 2.42. ``` Databases still at 2.40: postgres, forgejo, keycloak, atuin, linkwarden, headscale, grafana, kitchenowl, woodpecker, nextcloud, hass, freshrss. `template1` and `vikunja` already report 2.42. Largest is woodpecker at 559 MB; kitchenowl is 12 MB. ## Scope - Reindex each affected database, then refresh its stored collation version. - Verify that no collation mismatch warning remains on new connections. ```sh for db in forgejo keycloak atuin linkwarden headscale grafana kitchenowl woodpecker nextcloud hass freshrss postgres; do sudo -u postgres reindexdb --verbose "$db" sudo -u postgres psql -d template1 -c "ALTER DATABASE \"$db\" REFRESH COLLATION VERSION;" done ``` ## Acceptance criteria - [ ] `REINDEX DATABASE` completed for every affected database. - [ ] `ALTER DATABASE "<db>" REFRESH COLLATION VERSION` run for every affected database. - [ ] `select datname, datcollversion from pg_database` shows 2.42 for all non-template databases. - [ ] No collation mismatch warnings in the PostgreSQL log after reconnecting.
Sign in to join this conversation.
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
Personal/nixos-configuration#55
No description provided.