Repair PostgreSQL databases after the glibc collation change #55
Labels
No labels
automerge
bug
dependencies
duplicate
help wanted
invalid
lang
rust
migrations
opencode
question
security
type
documentation
type
enhancement
type
maintenance
type
refactor
vulnerability
wontfix
No milestone
No project
No assignees
1 participant
Notifications
Due date
No due date set.
Dependencies
No dependencies set
Reference
Personal/nixos-configuration#55
Loading…
Reference in a new issue
No description provided.
Delete branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
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:
Databases still at 2.40: postgres, forgejo, keycloak, atuin, linkwarden, headscale, grafana, kitchenowl, woodpecker, nextcloud, hass, freshrss.
template1andvikunjaalready report 2.42. Largest is woodpecker at 559 MB; kitchenowl is 12 MB.Scope
Acceptance criteria
REINDEX DATABASEcompleted for every affected database.ALTER DATABASE "<db>" REFRESH COLLATION VERSIONrun for every affected database.select datname, datcollversion from pg_databaseshows 2.42 for all non-template databases.