supabase database "postgres" has a collation version mismatch

TL;DR

The postgres maintenance database records a collation version older than the system's current library after a platform upgrade. Connect to it (or to another database), run REINDEX DATABASE postgres and ALTER DATABASE postgres REFRESH COLLATION VERSION, and the warning clears.

supabase database "postgres" has a collation version mismatch

Use this when

• Supabase logs warn about the postgres database collation version
• The warning appeared after scheduled platform maintenance
• template1 already got fixed but postgres still warns

Not for this skill when

• The warning names your application database (same commands, that database's name)
• Queries return wrong ordering (thats actual collation damage)
• Connections fail outright (thats auth or network)

Steps

1. Confirm the mismatch on the postgres database:

SELECT datname, datcollversion, pg_collation_actual_version(datcollate) AS actual
FROM pg_database WHERE datname = 'postgres';

Expected output: datcollversion and actual differ. The data is fine; only the recorded version is stale.

1. Reindex it. Connect to a different database first if your session is on postgres:

REINDEX DATABASE postgres;

Expected output: completes cleanly. The postgres database is small, so this is fast.

1. Refresh the recorded version:

ALTER DATABASE postgres REFRESH COLLATION VERSION;

Expected output: ALTER DATABASE. Re-run the step-1 query to confirm both versions now match.

1. On Supabase, run these from the SQL editor with an adequately privileged role:

SQL editor \u2192 new query \u2192 paste the REINDEX, run, then the ALTER, run

Expected output: both succeed. The anon key cant run these; use the dashboard SQL editor which runs with higher privileges.

Variant phrasings

collation warning after every Supabase maintenance window

Expected: the platform upgrades the OS under Postgres. Keep this two-command fix in your runbook.

warning persists after refresh

Something re-created or restored the database from an old backup carrying the stale version. Re-run the refresh after the restore.

Why it happens

Supabase runs maintenance that upgrades the underlying OS and its glibc. The postgres database, like every database, recorded its collation version at creation, so after the upgrade the recorded version lags reality and Postgres warns on connections. It is cosmetic in nearly all cases, but the warning is noisy and it is worth clearing so real warnings stand out.

Edge cases

• Dont confuse this with the template1 warning; fixing one doesnt fix the other.
• Point-in-time restores can reintroduce the stale version; add the refresh to post-restore checks.
• If Supabase support says they handle it platform-side, dont run the commands yourself; double maintenance can lock briefly.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_nl4c1eHV4M25jw1Nzkg-4w