How to bulk update collation in PostgreSQL

Often, when updating PostgreSQL, the collation gets updated.

This means you will get a collaction mismatch error.

You can bulk update all the DB by connection through psql with a superuser and issue the following:

SELECT
  'ALTER DATABASE '
  || quote_ident(datname)
  || ' REFRESH COLLATION VERSION'
FROM pg_database
WHERE datallowconn
ORDER BY datname;
\gexec