1

I recently updated my PostgreSQL database to a newer version, and since then, I've been encountering the following error:

2023-07-03 11:32:19.519 UTC [291] WARNING: database "myDB" has a collation version mismatch
2023-07-03 11:32:19.519 UTC [291] DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.36.
2023-07-03 11:32:19.519 UTC [291] HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE "myDB" REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

The server instance runs in docker, this error started spamming my logs after i updgraded to latest image (15.3-1.pgdg120+1).

What should be the correct way of upgrading the server to avoid this error?

I have many databases on the instance, all throwing the same error, can i fix them all in bulk?

I would greatly appreciate any guidance or insights on how to resolve this collation version mismatch error. Thank you in advance for your help!

1

1 Answer 1

1

From official docs:

all objects depending on the collation should be rebuilt, for example, using REINDEX. When that is done, the collation version can be refreshed using the command ALTER COLLATION ... REFRESH VERSION

source: https://www.postgresql.org/docs/current/sql-altercollation.html

2
  • It would be much more helpful if you could please clarify what exactly "all objects depending on the collation" could be? The documentation article talks about REINDEX, but only as an example. Is it sufficient to run a REINDEX? What other objects, besides indices, could be depending on a collation?
    – intelfx
    Dec 14, 2023 at 14:15
  • @intelfx it is a literal citation from the docs. If you want to know more about their words, ask e.g. the authors, I am not that competent. Dec 15, 2023 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.