You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Docker image for version 15.3 throws collation warnings #1099
Since today we see the following warnings in our application logs:
[WARN ] 2023-06-15 10:58:36 [ main][o.h.e.j.spi.SqlExceptionHelper]: SQL Warning Code: 0, SQLState: 01000 [WARN ] 2023-06-15 10:58:36 [ main][o.h.e.j.spi.SqlExceptionHelper]: database "txture" has a collation version mismatch
Upon inspection, we realised that while pulling the newest version of postgres:15 that apparently the collation version has changed since our last pull:
root@2f2cfdc05db1:/# psql -U txture WARNING: database "txture" has a collation version mismatch DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.36. HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE txture REFRESH COLLATION VERSION, or build PostgreSQL with the right library version. psql (15.3 (Debian 15.3-1.pgdg120+1))
When starting our application along with the docker image for version 15.2, no such warning is displayed and the collation table shows version 2.31.
I assume that this is connected to the release of Debian Bookworm a couple days ago, which brings glibc 2.36 as the new glibc version:
(also note version 2.31 in old-stable).
At the moment, we are operating a lot of databases created with Postgres 15.x (where x < 3) and we'd like to avoid refreshing the collation for now.
While this is just a warning, it is thrown frequently. I'm not entirely sure what the procedure is here, but I wanted to point out the issue as I expect we are not the only ones affected.
Appreciate any feedback!
Reacted by Nick Reilingh, David Plankensteiner, Samuel Bartík, Dhanush R, ajtraxler, Dobromir Stefanov and SMARTRUS.ORG
At the moment, we are operating a lot of databases created with Postgres 15.x
(where x < 3) and we'd like to avoid refreshing the collation for now.
As far as I understand, now both Debian versions ( bullseye, bookworm ) are currently supported and updated;
if you encounter similar problems, it's advisable to lock the docker image version at
15.3-bullseye, 15-bullseye
and not using the default 15
You can check the "Supported tags and respective Dockerfile links" here
Having the same problem, just want to validate my current understanding of this and maybe give a framing on the issue that most people might not expect:
One might assume that using postgres:15 as their tag would accomplish soft-pinning to avoid breaking changes. However, what occurred in the past few days was that 15 moved from 15-bullseye to 15-bookworm. This creates a breaking change because postgres does not provide its own collations -- it uses collations from the base OS, which were upgraded with Debian's change from bullseye to bookworm.
The takeaway is that if you actually want soft-pinning without breaking changes, you need to pin not only the postgres major version, but also the operating system, i.e. postgres:15-bookworm.
The takeaway is that if you actually want soft-pinning without breaking changes, you need to pin not only the postgres major version, but also the operating system, i.e. postgres:15-bookworm.
That is correct. The Debian release tags to help users choose their level of stability vs updates. We are currently committed to keeping the two latest stable versions of Debian simultaneously (just like the Alpine variants).
We've pinned our version to 15-bullseye now. I wasn't aware of the base image being subject to change within a major version. In addition to the above warning, we also saw the following on fresh installs:
popen failure: Cannot allocate memory initdb: error: program "postgres" is needed by initdb but was not found in the same directory as "/usr/lib/postgresql/15/bin/initdb"
I believe this is related to the seccomp issue mentioned in #1100
Noticed this issue was linked from an issue on the postgis repo
Users can't safely switch between any debian major release images (bullseye to bookworm, or any newer ones) without logical pg_dump-and-load or logical replication. Switching the container image from bullseye to bookworm and keeping the same storage (whether via PVCs or StatefulSets) can corrupt your database.
The root cause is the stability issues with linguistic collation (especially in libc) and a number of database corruptions have happened in recent years related to this. Here's a talk from the postgres development conference earlier this year on the topic https://www.youtube.com/watch?v=KTA6oau7tl8
Note that it's not only libc, same problem applies to ICU as well, since the default version is always updated in Debian major releases
It may be wise to keep the bullseye images around for a slightly extended amount of time
Activity
ImreSamu commentedon Jun 15, 2023
As far as I understand, now both Debian versions ( bullseye, bookworm ) are currently supported and updated;
if you encounter similar problems, it's advisable to lock the docker image version at
15.3-bullseye,15-bullseyeand not using the default
15You can check the "Supported tags and respective Dockerfile links" here
NReilingh commentedon Jun 15, 2023
Having the same problem, just want to validate my current understanding of this and maybe give a framing on the issue that most people might not expect:
One might assume that using
postgres:15as their tag would accomplish soft-pinning to avoid breaking changes. However, what occurred in the past few days was that15moved from15-bullseyeto15-bookworm. This creates a breaking change becausepostgresdoes not provide its own collations -- it uses collations from the base OS, which were upgraded with Debian's change frombullseyetobookworm.The takeaway is that if you actually want soft-pinning without breaking changes, you need to pin not only the postgres major version, but also the operating system, i.e.
postgres:15-bookworm.yosifkit commentedon Jun 15, 2023
That is correct. The Debian release tags to help users choose their level of stability vs updates. We are currently committed to keeping the two latest stable versions of Debian simultaneously (just like the Alpine variants).
johannespostler commentedon Jun 16, 2023
We've pinned our version to
15-bullseyenow. I wasn't aware of the base image being subject to change within a major version. In addition to the above warning, we also saw the following on fresh installs:popen failure: Cannot allocate memory initdb: error: program "postgres" is needed by initdb but was not found in the same directory as "/usr/lib/postgresql/15/bin/initdb"I believe this is related to the
seccompissue mentioned in #1100ardentperf commentedon Dec 27, 2024
Noticed this issue was linked from an issue on the postgis repo
Users can't safely switch between any debian major release images (bullseye to bookworm, or any newer ones) without logical pg_dump-and-load or logical replication. Switching the container image from bullseye to bookworm and keeping the same storage (whether via PVCs or StatefulSets) can corrupt your database.
The root cause is the stability issues with linguistic collation (especially in libc) and a number of database corruptions have happened in recent years related to this. Here's a talk from the postgres development conference earlier this year on the topic https://www.youtube.com/watch?v=KTA6oau7tl8
Note that it's not only libc, same problem applies to ICU as well, since the default version is always updated in Debian major releases
It may be wise to keep the bullseye images around for a slightly extended amount of time