Dev & EngARTICLE

PostgreSQL in Docker: the five container defaults that bring down production

PostgreSQL in Docker: the five container defaults that bring down production
Image: Roberto Diniz
Article · Dev & Eng
Reading
7 min
Tables
1
Code
3
In short

A guide by engineer Aleksei Aleinikov shows why poorly mounted volumes, 64 MB of shared memory, and a 10-second shutdown timeout account for most incidents with containerized PostgreSQL, not Docker itself.

Docker isn't the villain, the default is

Running PostgreSQL inside a container takes seconds to spin up in development. In production, according to engineer Aleksei Aleinikov, the same compose.yaml copied from a tutorial can take just a few minutes to lose data. His argument, published on his own blog and syndicated by Planet PostgreSQL, is straightforward: most incidents don't originate from Docker, they originate from the collision between Docker's default values and the way PostgreSQL manages memory, checkpoints, and files on disk.

Four defaults collide head-on with the Postgres engine: the 64 MB of /dev/shm, the 10-second timeout before SIGKILL, the :latest tag without a pinned version, and the volume path that changed in PostgreSQL 18. None of these values were designed for a relational database with checkpoints, dynamic shared memory, and one operating-system process per connection. Anyone who spins up Postgres in a container without reviewing these points is inheriting configuration meant for stateless services.

The structural change in PostgreSQL 18+

Starting with PostgreSQL 18, the official Docker Hub image started mounting PGDATA in versioned subdirectories inside /var/lib/postgresql (for example /var/lib/postgresql/18/docker), instead of the fixed path /var/lib/postgresql/data used through version 17. The VOLUME target in the official Dockerfile also changed, from /var/lib/postgresql/data to /var/lib/postgresql. It's a structural breaking change, not a cosmetic one.

The reason, according to the official image documentation cited by Aleinikov, is to align the container layout with Debian's pg_ctlcluster convention, so that future runs of pg_upgrade --link can promote major versions within the same parent directory. Anyone who points a postgres:18 container directly at a PG 17 volume won't see an automatic upgrade: they'll see the database simply fail to initialize.

The safe migration path, as described in the source, keeps the old volume untouched as an instant rollback until confirming the new container came up correctly:

bash
docker exec -t pg-prod pg_dumpall -U app_admin > pg17_full.sql
docker compose down
# update the compose.yaml to postgres:18.1-bookworm with the volume at /var/lib/postgresql
docker compose up -d postgres
docker exec -i pg-prod psql -U app_admin -d postgres < pg17_full.sql

Skipping pg_dumpall or reusing the old volume by pointing it directly at the new image is the most common error in the troubleshooting list in the source: the container doesn't initialize, because it doesn't find the layout it expects.

The five points that turn into outages

Aleinikov catalogs five silent traps, none of them exotic. They repeat because they're born from omission: nobody set an explicit value, and Docker's default stayed in place.

The 64 MB /dev/shm

Docker limits /dev/shm to 64 MB by default. PostgreSQL uses POSIX shared memory for dynamic shared memory (DSM) segments, parallel query workers, and parallel hash joins. Under real load, the query fails like this:

ERROR: could not resize shared memory segment "/PostgreSQL.12345" to 67108864 bytes: No space left on device

The fix is to declare shm_size: 1gb (or 2 to 4 GB on larger nodes) directly in the Compose service. It's one line, but without it every parallel query becomes a gamble.

The 10-second SIGKILL

When running docker compose down or docker stop, Docker sends SIGTERM and waits only 10 seconds before firing SIGKILL. If Postgres has gigabytes of dirty buffers to write out in a checkpoint, 10 seconds isn't enough: the process gets killed mid-flush, forcing a slow WAL recovery on the next startup. The fix is stop_grace_period: 60s (or 120s on write-heavy instances), enough time for SIGTERM to complete the checkpoint.

The healthcheck that lies

A healthcheck written just as pg_isready, without flags, tests the default user (root) and the default database, and can return success during an initdb restart, before your database and your user actually exist. It's a classic false positive: the orchestrator thinks Postgres is ready, and it isn't. The correct version specifies the target user and database:

yaml
healthcheck:
  test: ["CMD-SHELL", "pg_isready -U $${POSTGRES_USER} -d $${POSTGRES_DB}"]
  interval: 10s
  timeout: 5s
  retries: 5
  start_period: 20s

Connection exhaustion without a pooler

Unlike thread-based engines, PostgreSQL creates a dedicated operating-system process for each client connection, 5 to 10 MB of RAM each, plus the work_mem of each query. A burst of 300 serverless functions or worker containers opening connections at the same time is enough to trigger the Linux OOM killer inside a memory-limited container.

Named volume, bind mount, and permissions

On a production Linux host, a named Docker volume on local NVMe, or a bind mount to a dedicated directory owned by 999:999 (the UID/GID of the Debian image), work fine. Sharing via SMB or CIFS without strict locking guarantees is out of the question for PGDATA. On macOS and Windows, a direct host bind mount suffers I/O overhead and corrupts permissions; the recommendation is to always use a named volume inside the Linux VM that Docker Desktop already uses.

Why PgBouncer comes before any fine-tuning

PostgreSQL's process-per-connection model isn't an implementation detail: it's the reason connection pooling is architecture, not optimization. Putting PgBouncer in transaction pooling mode in front of the database, keeping Postgres's max_connections at something reasonable (100 to 150) while PgBouncer absorbs 500 or more client connections, solves the problem at the right layer.

It's worth noting a detail that changes the math for ORM users: since PgBouncer 1.21, protocol-level prepared statements work natively in transaction mode via MAX_PREPARED_STATEMENTS. This removes from Prisma, SQLAlchemy, and pgx the need for workarounds they used to maintain just to coexist with aggressive pooling.

The trade-off that remains: transaction-mode pooling breaks some session guarantees, such as variables set with SET and persistent LISTEN/NOTIFY across queries on the same logical connection. For most web applications this is irrelevant; for those who depend on protocol-level session state, the solution is to isolate those specific connections outside the pool, not to abandon PgBouncer.

Sizing memory by host size

The source's reference compose.yaml, designed for an 8 GB host, sets shared_buffers=2GB, effective_cache_size=6GB, work_mem=16MB, and max_connections=120, deliberately leaving between half and three-quarters of RAM outside shared_buffers: PostgreSQL relies heavily on the Linux page cache, and giving memory back to the OS to cache files is part of the design, not waste.

Host RAMshm_sizeshared_bufferseffective_cache_sizework_memmax_connections
2 GB512mb512MB1536MB4MB60
4 GB1gb1GB3GB8MB80
8 GB1gb2GB6GB16MB120
16 GB2gb4GB12GB32MB150
32 GB4gb8GB24GB64MB200

Backup isn't tar on a live volume

Even on a host with RAID or ZFS, a RAID snapshot isn't disaster recovery. Copying PGDATA's raw files with tar or rsync while PostgreSQL is running captures torn pages and produces a backup that won't restore.

The source proposes two patterns, and the choice depends on database size and required RPO:

  • Compressed logical dump, for databases up to 30-50 GB: scheduled pg_dump -Fc, with exit status verification, and sending the encrypted file to S3-compatible storage (Cloudflare R2, for example) via restic or rclone.
  • Continuous WAL with PITR, for larger databases or low RPO: pgBackRest or WAL-G running on a customized image or sidecar that shares the volume, allowing restoration to any exact second before a bad migration.

Neither one is worth anything without a restore test. The source's recommendation is monthly: spin up a disposable container, restore the latest dump, and check row counts before trusting the backup again. A backup that was never restored is just a hope with a fancy name.

When Docker isn't the right answer

Nothing Aleinikov describes is a reason to avoid running Postgres in a container, but it's worth marking where the math doesn't add up. Very large databases, with sustained I/O above what local NVMe in a container delivers, or workloads that depend on filesystem tuning beyond what Docker exposes, tend to benefit more from a dedicated host or a managed service than from a hand-tuned container.

The rule of thumb is simple: a container with the five points fixed works well for most teams that already use Docker for the rest of the stack and want Postgres in the same deploy flow. Anyone choosing a stack from scratch, with a database in the terabyte range or a very tight SLA, should put RDS, Cloud SQL, or a dedicated physical host on the same decision spreadsheet before signing off on the compose.yaml.

Translated from the Brazilian Portuguese original · Read the original