Keeping collation versions stable in Postgres docker images

Question

I'm using the official PostgreSQL docker images, so my Dockerfile starts with

FROM postgres:17

I noticed that I was getting collation mismatch warnings (I'm using en_US.utf8):

DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.41.

I am assuming that the reason is that the collation is not kept stable in these docker images by design. I will pin a specific minor version of Postgres here anyway I think to get more control over when exactly minor version upgrades happen, but I still would prefer to upgrade to newer minor Postgres versions as they come out.

When do the collations actually change here? I did figure out only after writing this, that there was actually a major Debian upgrade from Bookworn to Trixie in that image recently. I misunderstood the policy here for the default image and assumed those major upgrades would be in images with different tags.

Do the collations only change on major OS upgrades, or more frequent than that? Potentially on every minor Postgres version change, or even in between when I build the image again and the OS had minor updates in the meantime?

I understand there are ICU collations I could use, but those also seem to change frequently.

My main question is, can I keep the collation version stable in a Docker image like this, and does it even make sense to do this or not?

Or should I focus on reindexing and fixing the collation mismatch every time it happens?

If it is possible, how would I keep the collation versions stable here, at least within a specific Postgres minor version, ideally while upgrading to the latest minor version.

• Biller Builder: The collation comes from underlying OS. And since the image for postgres:17 pulls from debian without a version specifier (although I have no idea if the trixie-slim is specific enough for debian images), it depends entirely on debian to decide if collation updates are minor or major. In general it's a good practice to qualify images at least to minor versions, so you wouldn't have to redownload images on each patch version for minors outside of the minor range. Though it requires deps maintenance routine in frequent enough intervals.

Answer 1

If you are using C library collations and a Dovker image based on the Debian distribution, you will get breaking changes (a collation version mismatch) only if there is a change in the Debian version used.

If you want to avoid these changes (and the necessary index rebuild) altogether, you have two choices:

• use the binary collation C, either from the C library or from the new builtin collation provider

• create your own Docker images with a fixed version of the ICU library and use only ICU collations