Fix Postgres Collation Version Mismatch

2026-01-12 (Last Modified: 2026-01-12)

I looked in the systemd journals on my Qotom fanless server. I saw something reporting a Postgres database problem.

I have Gitea running on my Qotom so that I have a centralized git server for my repositories. I want to duplicate my official github repositories for the occasion of Microsoft enshittifying github.com.

I configured my instance of gitea to use Postgres as the backing store.

I got Postgres journals with journalctl -u postgresql --since -30m

Jan 12 21:58:15 modest postgres[13060]: 2026-01-12 21:58:15.056 MST [13060] DETAIL:  The database was created using collation version 2.41, but the operating system provides version 2.42.
Jan 12 21:58:15 modest postgres[13060]: 2026-01-12 21:58:15.056 MST [13060] HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE template1 REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
Jan 12 21:58:29 modest postgres[13063]: 2026-01-12 21:58:29.948 MST [13063] WARNING:  database "postgres" has a collation version mismatch

I had to futz around for a few minutes to figure this out. gitea (the Linux process) uses tables in a Postgres database named gitea. That particular database didn’t need any fixing, but I tried anyway. No effect, but at least I didn’t roach it, either.

Ultimately, this worked:

1005 % psql -U postgres                                                  # /home/bediger
WARNING:  database "postgres" has a collation version mismatch
DETAIL:  The database was created using collation version 2.41, but the operating system provides version 2.42.
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.
psql (18.1)
Type "help" for help.

postgres=# ALTER DATABASE postgres REFRESH COLLATION VERSION;
NOTICE:  changing version from 2.41 to 2.42
ALTER DATABASE
postgres=# \q

Notice that logging in to database postgres gave the same warnings as appeared in the systemd journals.

A quick check of the systemd journals showed that I’d uncovered a new problem.

Jan 12 22:23:15 modest postgres[13300]: 2026-01-12 22:23:15.381 MST [13300] WARNING:  database "template1" has a collation version mismatch 
Jan 12 22:23:15 modest postgres[13300]: 2026-01-12 22:23:15.381 MST [13300] DETAIL:  The database was created using collation version 2.41, but the operating system provides version 2.42.
Jan 12 22:23:15 modest postgres[13300]: 2026-01-12 22:23:15.381 MST [13300] HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE template1 REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

I altered another database:

postgres=# ALTER DATABASE template1 REFRESH COLLATION VERSION;
NOTICE:  changing version from 2.41 to 2.42
ALTER DATABASE

The warnings about collation sequence had been appearing every 60 seconds, at 15 seconds past the minute. Those warnings (or even another problem) did not occur after the second ALTER DATABASE command.