Read more

Postgres in Alpine docker container: sorting order might differ

Avatar
Daniel Stra├čner
September 06, 2023Software engineer at makandra GmbH

In CI test runs I noticed that string sorting order changed after switching from a debian-based PostgreSQL docker image to one that is based on Alpine Linux.

Illustration web development

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

  • We build reliable cloud solutions with Infrastructure as code
  • We are experts in security, Linux and databases
  • We support your dev team to perform
Read more Show archive.org snapshot

Debian image sorting: bar Bar foo Foo
Alpine image sorting: Bar Foo bar foo

Explanation

Alpine Linux is a very slim linux distribution that results in small docker image sizes (roughly 100MB instead of 150MB), so it's a popular choice. However, it does not have all comman locales installed and does not use all locales that a user installs by default.
Postgres orders string columns based on the collation of the databse (LC_ALL or LC_COLLATE) Show archive.org snapshot . On dev machines this usually is en_US.UTF-8 locale. Alpine seems to use locale C which has a different sorting.

Solution

There is a github issue Show archive.org snapshot that suggests that starting with postgres:15-alpine one can set the locale-provider icu which can also handle UTF-8 locales.

I have not tested this approch. So either try this or switch back to a debian-based image (e.g. postgres:15) when you run into this problem.

Posted by Daniel Stra├čner to makandra dev (2023-09-06 10:49)