Docker image for version 15.3 throws collation warnings

[johannespostler]

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:

https://tracker.debian.org/pkg/glibc

(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!

[ImreSamu]

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

• https://hub.docker.com/_/postgres

[NReilingh]

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.

[yosifkit]

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).

[johannespostler]

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

[ardentperf]

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