[Repost] Do not run PostgreSQL with Docker in production environment

🛠 Tools and Programming
,
1

This article was transcoded by 简悦 SimpRead, original source vonng.com

A large number of users using the official Docker Postgres image have encountered issues during recent minor version upgrades. Lao Feng once again reminds everyone to be cautious about running production databases inside containers.

Back in 2019, Lao Feng mentioned in “Is It a Good Idea to Put Databases in Docker?” — do not run PostgreSQL databases in containers in production environments because you are highly likely to encounter a bunch of troubles and problems that never exist on physical or virtual machines. Just recently, users using the “official” Postgres Docker image hit a snag during upgrades. Yesterday, PostgreSQL community veteran Gwen Shapira posted on X to complain about this.

:warning: Important reminder: Do not use the official Docker Postgres image in production. If you must use it, be sure to explicitly specify the Debian base image version. Minor PostgreSQL version upgrades (e.g., 17.6 → 17.7) are generally safe, simple, and recommended, and theoretically should never break anything.

However, if you used the Docker official image and performed a minor version upgrade recently (since August), you might have seen a warning like this: “The collation version used when the database was created is 2.36, but the current operating system provides version 2.41. Please rebuild all objects using the default collation and execute ALTER DATABASE \"mydb\" REFRESH COLLATION VERSION, or rebuild PostgreSQL with the correct version of the library.”

Why does this happen? The reason is actually simple and ridiculous:

  1. The Docker official PG image supports only two Debian versions.
  2. When Debian releases a new version, if you do not explicitly specify the Debian version tag, it will automatically become the new default base image.
  3. The new Debian version uses a new version of glibc.
  4. After the glibc update, the locale (collation) files have changed.

So your current state becomes:

  • The running PostgreSQL links against one set of locale files.
  • But the data and indexes in the database were generated based on another older set of locale files.

PostgreSQL is well aware that this mixture causes:

  • Incorrect query results
  • Wrong sorting
  • More seriously, it can even trigger data corruption

Therefore, it demands you to:

  • Rebuild all affected objects
  • Then execute ALTER DATABASE ... REFRESH COLLATION VERSION

This set of operations usually only needs to be done for major version upgrades, and no one would expect that a minor version upgrade would require rebuilding the entire database. The result is: the Docker official image forcefully throws this onto users’ faces: minor version upgrades may also trigger glibc/locale changes. Beware! Official images do not mean “responsible production behavior”.

Imagine you are using the “official” Postgres image provided by Docker, and then the latest minor PostgreSQL version is released this week — so you prepare to upgrade a minor version. Aren’t minor PG upgrades supposed to be very safe and simple? Just pull the latest image again (I guess a good portion of people do this), and some users who care a bit more might pull the image like (17.6 → 17.7) specifying the version. If so, you are doomed!

Unless your image tag strictly includes the Debian version number, such as 17.6-bookworm, then the recent minor version update actually implicitly involves a major Linux OS upgrade. You think you’re upgrading from 17.6 to 17.7, but actually, the underlying OS is upgraded from Debian 12 to 13! This unplanned in-place upgrade will cause your database indexes to become invalid! (Or even worse!)

What exactly happened#

The Docker official PostgreSQL images are mainly based on Debian system images (Alpine versions are also provided but are mostly based on Debian). Maintainers note that these images support only two Debian releases at the same time — when a new Debian stable release comes out, the base image is upgraded to the new version and support for the oldest version is dropped.

Recently, Debian 13 “trixie” was just released, so the Docker official postgres image was updated, with the underlying Debian image upgraded from 12 “bookworm” to 13 “trixie”. This caused a jump in the version of the underlying C library (glibc) — the glibc version in Debian 13 upgraded from 2.36 (in Debian 12) to 2.41, and the collation rules changed between these two glibc versions, which is problematic.

Because the core of database indexes — collation — is defined by collation rules, which are not static. Whenever the collation rules change, a database cluster using the old collation rules needs to be rebuilt — at least the indexes need to be rebuilt, otherwise data corruption may occur. Serious production databases cannot do without indexes, so until you rebuild all indexes — “in-place indexes are invalid”, and database performance crashes. In the worst case, this may affect database constraints, data consistency, partition table behavior, and so forth.

The scope of this mistake is huge. On DockerHub, the postgres image is one of the most downloaded images — over one billion downloads, about 17 million pulls in the past week. Many users just blindly do docker pull postgres. Even if specifying a PG version like 17.6, if the Debian version is not specified explicitly, failures still occur.

Emergency Measures#

For friends using the so-called Docker official “postgres” container in production, Lao Feng’s advice is to switch your container version to an image that locks both PG and Debian versions (e.g., 17.6-bookworm) as soon as possible, at least before the next minor upgrade or re-pull. When upgrading, be sure to use version tags like 17.7-bookworm.

Also, don’t expect to in-place upgrade directly from 17.7-bookworm to 17.7-trixie. Any changes involving glibc (Linux distro major version) require a standard SOP for logical migration — either online migration via logical replication blue-green deployment, or pg_dump logical export. Unless you’re an experienced PG expert who explicitly specifies and selects PG built-in locale provider with C/C-UTF8 during cluster initialization.

Of course, from a long-term perspective, migrating to database deployment on physical or virtual machines is more reliable. Lao Feng has discussed this in “Should Databases Be Put into K8S?” and “Is It a Good Idea to Put Databases in Docker?” — the more complex the architecture juggling, the more painful the failure!

If you must use containers, Lao Feng also suggests finding a better third-party Docker Postgres image, which is much better than the “official” primitive image.

Why Are Collations Important#

So why does this problem occur? Lao Feng has elaborated on this in “Local Collations in PG”. The simple conclusion is that you should always use C.UTF-8 as the global collation, and starting from PostgreSQL 17, the PG built-in locale provider should be forcibly used instead of the OS glibc collation rules. When you really need specific locale rules (like Chinese pinyin sorting), you can explicitly declare it in DDL/SQL — no impact on usage — use ICU collations, not OS ones!

The reason is that (at least before PG 17) PostgreSQL strongly depends on the OS localization library to perform string comparison and sorting, which is a core function provided by glibc, and the collation rules in glibc change! The glibc version updates with every major Linux distro version upgrade. This means in production environments, you generally cannot directly copy PG physical files from system A to system B and run them — unless you use the built-in collation rules after PG17, which is not the default.

When running initdb, use the options --locale-provider=builtin and --builtin-local=C.UTF-8.

At PGConf.Dev 2024, Jeremy Schneider’s keynote “Collations from A-Z” deeply explained this issue. The PostgreSQL development team recognized this as a problem and introduced a new feature in PG 17: built-in collations — no longer using the OS glibc collation rules, but only supports C and C.UTF-8 collations. If you want to know more about this topic, Lao Feng highly recommends reading this material or watching the PGConf.Dev 2024 recording.

23 Common Misconceptions about Collations, All Wrong#

  1. Sorting characters is a simple task.
  2. Sorting rules used by humans and computers are immutable.
  3. Changes in sorting rules are rare.
  4. Changes in sorting rules are always intentional.
  5. Sorting rules only break indexes.
  6. Broken things can be rebuilt.
  7. My database doesn’t use characters from strange languages, so collation doesn’t matter.
  8. My database understands all characters put into it.
  9. PG’s “wrong collation version” warning will always be seen by someone.
  10. PG can always know the C standard library version of the host system.
  11. You can separately extract and build the collation part of old glibc code and install it in the new system to fix issues.
  12. ICU can solve all collation problems!
  13. ICU does not have major collation changes like glibc 2.28 fiasco.
  14. Assume Devrim and Christoph are happy to build old ICU versions for you.
  15. glibc minor/patch versions do not modify collations.
  16. If library version number does not change, collation does not change.
  17. PG has not yet provided a built-in Collation Provider to solve all data corruption crises above.
  18. PG’s C and C.UTF-8 collations are the same.
  19. C.UTF-8 collation is immutable.
  20. Collation Provider only solves collation issues.
  21. C.UTF-8’s CTYPE is immutable.
  22. Users want DB-level language-specific sorting.
  23. PG is unlikely to have a built-in collation to solve these issues.

Fortunately, PostgreSQL introduced built-in collations in version 17 to resolve these problems. Lao Feng’s PG distribution Pigsty also officially applied this feature in v3.4.0.

— All clusters above PG 17 use the built-in locale-provider uniformly, fixed to the C.UTF-8 collation. For versions below 17, the OS’s C.UTF-8 collation is used; if the OS does not support C.UTF-8 (which does happen), fallback to the C collation.

The benefit is that by using this built-in collation, no matter how much the OS messes around, PostgreSQL’s sorting is unaffected. Even if you upgrade the underlying OS, you don’t have to rebuild indexes or worry about data corruption.

Official ≠ “Reliable”#

However, obviously, what is common sense and best practice for PostgreSQL experts is not so widespread. At least on the Docker “official postgres image,” these known “best practices” are lacking. As Gwen said: having the word “official” does not mean “responsible production behavior.”

The postgres image on DockerHub is widely used (said to be the most downloaded image), but PostgreSQL experts find its quality worrying. The “official” means Docker’s “official”, not the PostgreSQL community’s. So it’s filled with many anti-patterns and very uncomfortable to use.

Basically, this so-called official image is an extremely simple wrapper: use apt to install from the PGDG APT repository, then run a primitive init script. This image is basically sufficient for POCs, development, testing, and learning, but it is worlds away from production readiness.

Production Databases Are Not Suitable for Containers#

If you use the Docker Postgres container, even if you didn’t hit problems in this minor upgrade, you are very likely to encounter others. For example, the default 64 MB Shmem shared memory segment; direct writes on Overlay FS; extensions disappearing on standby nodes; running two PG instances on one volume causing corrupted data; bizarre standby setup processes, and so forth.

Lao Feng has discussed many such container-specific problems that simply don’t exist on physical or virtual machines in “Is It a Good Idea to Put Databases in Docker?”. Clearly, the community will keep seeing new surprises (or scares), and running databases in containers has yet to reach the equilibrium state of running natively on bare Linux for the long term.

There are tons of engineering details like locale configurations that cannot be solved by just pulling a so-called “official image”. Lao Feng’s Pigsty project itself contains nearly 100,000 lines of pure code to solve PostgreSQL issues — this clearly cannot be covered by an “official image” with a few hundred lines of Shell/Dockerfile script.

In fact, some third-party PostgreSQL-over-Kubernetes vendors provide PG containers far better than this “official version”. But honestly, they still face container intrinsic limitations — a bunch of K8S/Docker gurus optimizing for a long time still cannot beat PG running bare-metal Linux directly. For database pros, it indeed feels like scratching an itch while wearing boots.

Docker is very convenient; Lao Feng uses it for stateless services, batch compiling tasks, cheap VM testing, or simple database feature testing sometimes. But when it comes to production environments, Lao Feng firmly says “no” to running databases inside containers (—— the only possible exception might be Redis).

How Should PostgreSQL Be Installed?#

So if not containers, how should PostgreSQL be installed and deployed?

PostgreSQL is a special software closely tied to the OS. The best state is to run directly on bare Linux without a wrapper — simple, direct, stable, reliable, without extra performance overhead or management burden.Many people think this is a very complicated matter, as if you have to tinker with some YUM/APT repositories, the official images are too slow and require VPNs; domestic mirror sites have all stopped updating — 《Trust Issues in the Software Supply Chain Seen from PG “Supply Cut”》— and then after installation, they are at a loss about how to configure, tune, and optimize. In fact, all of this is already old news. Lao Feng’s open-source PG distribution Pigsty is designed specifically to run enterprise-level PostgreSQL services directly on Linux.

Currently, I provide native PostgreSQL kernels (PG versions 13-18, six major versions) on 14 mainstream Linux distributions, including Debian 12/13, Ubuntu 22/24, EL 8/9/10, ARM / x86 architectures, 8 different PG kernel flavors, nearly a hundred ecosystem tools and 430 ecosystem extensions. It is made into a one-click deployment, installation, and startup solution, featuring built-in monitoring, high availability, and PITR for production use. Also provided is a Chinese mirror of the official PGDG repository, which is possibly the only PG mirror site in China that has not stopped updating alongside PGDG — 《PG Extension Cloud: Unlock PG Full Set Without VPN and for Free》

To be honest, this is really hard work. There are tens of thousands of RPM/DEB packages built. Various test combinations and upstream changes all need to be taken care of. Lao Feng also considered — why not make a Docker image, which is lazy and convenient, let users run it on whatever operating system they like. But as a large-scale production solution also intended for myself, I decided to do the “right and hard” thing — providing the ability to run the entire PostgreSQL ecosystem directly on 14 mainstream Linux distributions.

After all, the “official images” are also installed from repositories via APT…

And the best part is, Pigsty’s extension and mirror repositories are independent. If you don’t like using a big and comprehensive distribution, you can also freely use our APT/YUM repositories directly to install the native PGDG kernel along with all these tools and extensions above.

Of course, the advertisement ends here. In this article, Lao Feng talked about why you should not run PostgreSQL in containers in production environments. In the next article, Lao Feng will provide a detailed introduction to PostgreSQL installation practice — If not using containers, how should I install PG!

Reference Reading#

Is putting a database into Kubernetes a good idea?

Is putting a database into Docker a good idea?

“Kubernetes Founder Speaks Out! K8s Is Backfiring!”

“The Curse of Docker: Once Thought to Be the Ultimate Solution, But Finally ‘Truly Evil’?”

“What We Can Learn from Didi’s Failure”

“PostgreSQL@K8s Performance Optimization Notes”

“Running Database on Kubernetes”