Skip to content

Docker image for version 15.3 throws collation warnings #1099

Closed

Description

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

Activity

  1. ImreSamu commented on Jun 15, 2023

    @ImreSamu
    Contributor

    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

  2. NReilingh commented on Jun 15, 2023

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

  3. yosifkit commented on Jun 15, 2023

    @yosifkit
    Member

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

  4. johannespostler commented on Jun 16, 2023

    @johannespostler
    Author

    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

  5. ardentperf commented on Dec 27, 2024

    @ardentperf
    Contributor

    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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions