Collation warning

[bartoszgralek]

Hi!

I've just recently upgraded from 10 to 15, and mostly everything worked fine. I had to add also host all all all md5 to my pg_hba.config and then the container started. Now I am trying to get rid of this warning message that appears every time that something is being retrieved from database:

WARNING:  database "my_database" has no actual collation version, but a version was recorded

I've tried the things that people on the internet suggested like:

1. REINDEX SYSTEM my_database
2. REINDEX DATABASE my_database
3. ALTER DATABASE my_database REFRESH COLLATION VERSION

but none of them seems to work. The guy here seems to have the same problem

Send help

[ChrFr]

Are you using an alpine postgres image? The docker-postgres-upgrade image is based on a debian image.

The collation error occurs if the image used to migrate the database and the one running it afterwards use different C-libraries (according to this article)

I had the same problem and switched from the alpine image to a debian one (the postgres images without the "alpine" suffix) and got a different warning:

WARNING:  database "my_database" has a collation version mismatch
DETAIL:  The database was created using collation version 2.36, but the operating system provides version 2.31.
HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE my_database REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

But this can be fixed with the steps you already mentioned:

1. REINDEX SYSTEM my_database
2. REINDEX DATABASE my_database
3. ALTER DATABASE my_database REFRESH COLLATION VERSION

[mbirth]

Are you using an alpine postgres image? The docker-postgres-upgrade image is based on a debian image.

Ran into the same problem and yes, I'm using postgres:16-alpine as this image is 40-50 MB smaller than the Debian equivalents.

@tianon: Any chance to get an Alpine Linux variant of this?

[tianon]

#108 (comment)

[mbirth]

Okay, maybe it's just something with the utf8 collations. In my originating pg13 database (running happily in the 13-alpine container), I can see that the en_US.utf8 collation is of version 2.31 (glibc) whereas all other languages are version 153.14 (musl?). I remember upgrading (using this very Docker image) from (IIRC) 9 to 13 before. And it appears that the en_US.utf8 collation was still fine after that. However, now, after trying to update from 13 to 16, that very collation is missing afterwards which then produces the error messages.