Recipe: PostgreSQL, MySQL and MongoDB

Data volumes, secret files, init scripts, health checks, backup and upgrades.

A Compose stack comes up, docker compose ps says the database is healthy, and the application's first login fails with password authentication failed for user "shop_app". The database logs from the first ten seconds hold the reason: an init script could not read its secret file and exited, the restart policy started the container again, and the second start skipped initialisation because the volume already held a database. The container never finished its one-time setup, and the health check could not know. This lesson builds the stateful-service recipe on PostgreSQL 18, reproduces that failure on the way, and then shows what changes for MySQL 8.4 and MongoDB.

Everything runs on the main lab VM, secopslog-docker, as ubuntu in ~/lab/r-postgres. The lesson files download has compose.yaml, initdb/01-app-role.sh, and the mysql/ and mongo/ directories used further down. Compose basics and depends_on conditions are in "Multi-container apps with Compose" (Docker for beginners), the wider rules for secrets in "Runtime secrets, done right" (Advanced container security).

The Postgres recipe

name: lab-shop
 
services:
  db:
    image: postgres:18-alpine
    environment:
      POSTGRES_DB: shop
      POSTGRES_PASSWORD_FILE: /run/secrets/pg_superuser_password
    secrets:
      - pg_superuser_password
      - app_db_password
    volumes:
      - pgdata:/var/lib/postgresql
      - ./initdb:/docker-entrypoint-initdb.d:ro
    healthcheck:
      # TCP on 127.0.0.1, not the socket: the temporary init server listens on the socket only
      test: ["CMD", "pg_isready", "-h", "127.0.0.1", "-U", "postgres", "-d", "shop"]
      interval: 10s
      timeout: 3s
      retries: 5
      start_period: 30s
      start_interval: 1s
    shm_size: 128mb
    # time for a clean shutdown (checkpoint) before Docker sends SIGKILL; the default is 10s
    stop_grace_period: 1m
    restart: unless-stopped
 
  psql:
    # a client in its own container, the way the application connects: over the network, with a password
    image: postgres:18-alpine
    profiles: [tools]
    secrets: [app_db_password]
    entrypoint: ["sh", "-c", "PGPASSWORD=\"$$(cat /run/secrets/app_db_password)\" exec psql -h db -U shop_app -d shop \"$$@\"", "psql"]
    depends_on:
      db:
        condition: service_healthy
 
volumes:
  pgdata:
 
secrets:
  pg_superuser_password:
    file: ./secrets/pg_superuser_password.txt
  app_db_password:
    file: ./secrets/app_db_password.txt

The volume is mounted at /var/lib/postgresql, not at /var/lib/postgresql/data as in most older examples: from version 18 the official image keeps its data in a version-named subdirectory, /var/lib/postgresql/18/docker (the image sets PGDATA to it), so one mount holds the current cluster and can later hold the next major version beside it. The upgrade section below shows what happens with the old path.

No password appears in the file. POSTGRES_PASSWORD_FILE names a path, and the image reads the superuser password from that file on first start. Compose's secrets: puts each listed file under /run/secrets/<name> in the container. Without Swarm, Compose does this with a read-only bind mount of the host file, so the file keeps its host owner and mode inside the container; the long-syntax uid, gid and mode fields are Swarm features and changed nothing in a test on Compose 5.6.0. That detail decides the next section.

The health check runs pg_isready against 127.0.0.1, over TCP. On first start the image runs a temporary server that listens only on the Unix socket while it creates the database and runs the init scripts, so a socket check could report ready during setup and release dependent services too early; the TCP check passes only once the real server listens on the network. shm_size raises /dev/shm from Docker's 64 MiB default, which parallel queries can exhaust. stop_grace_period: 1m gives the server time for its shutdown checkpoint: after Docker's default of ten seconds it sends SIGKILL, and a busy database killed that way runs crash recovery on the next start. The MySQL and MongoDB files below set the same. There is no ports: entry: other services on the project network reach the server as db:5432, and nothing on the host or beyond needs to. The server does not run as root either. The entrypoint starts as root to prepare directories and read the superuser secret, then re-executes itself as postgres (UID 70 in the Alpine image).

The psql service sits in the tools profile, so docker compose up ignores it. It is a client in its own container that connects to db over the network with the application role's password, the same way an application does, which makes it the honest test of credentials. The init script creates that role:

#!/bin/sh
# Runs once, as the postgres user, when the container starts with an empty data directory.
set -eu
secret=/run/secrets/app_db_password
[ -r "$secret" ] || { echo "01-app-role.sh: cannot read $secret" >&2; exit 1; }
APP_PW=$(cat "$secret") psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<'SQL'
\getenv app_pw APP_PW
CREATE ROLE shop_app LOGIN PASSWORD :'app_pw';
CREATE SCHEMA shop AUTHORIZATION shop_app;
ALTER ROLE shop_app SET search_path = shop;
SQL

Files in /docker-entrypoint-initdb.d run in name order, once, on a first start with an empty data directory. This one gives the application a role that owns one schema and nothing else, so the superuser password stays with administrators. The password reaches psql through an environment variable that only that psql process has, and \getenv copies it into a psql variable, so it never appears on a command line or in the script. The explicit [ -r ... ] test turns an unreadable secret into a clear error instead of a role created with an empty password.

Secrets: readable by the user that reads them

Create the two secrets the careful-looking way, mode 0600 in a 0700 directory, and start the stack:

That is the incident from the opening. The container is healthy and has restarted once. The superuser secret worked, because the entrypoint reads it while still root. The init script runs as UID 70, which cannot read a file owned by UID 1000 with mode 0600, so it stopped with its error and the container exited. restart: unless-stopped started it again, the entrypoint found a data directory with PG_VERSION in it, logged Skipping initialization, and started the server. The shop_app role and its schema do not exist, and nothing will ever create them on this volume.

Two rules come out of it. A file secret must be readable by the user that reads it, and in official database images that is often not root; the usual fix is mode 0644 for the files, with the 0700 directory keeping other host users out. And a failed first initialisation leaves a populated volume behind, so throw the volume away before trying again:

Swarm secrets work differently (they live in the cluster store and accept uid, gid and mode); "Stacks, secrets and configs" covers them.

What the secret file hides, and where a password is checked

docker inspect shows only the path, and the server's own environment (PID 1, read as postgres because only the process owner may read it) holds no password variable. Anyone who can read the secret file or docker exec into the container still has the password, so this protects against leaks through inspect, crash dumps and debugging output, not against someone with access to the Docker daemon. Images differ here; MySQL below keeps the value in its environment.

Before testing the application role, look at where Postgres asks for passwords at all:

The trust lines come from initdb and cover the socket and 127.0.0.1 inside the container. The last line, added by the image, demands a SCRAM password from every other address. So docker compose exec db psql -U postgres never asks for a password, and a password test run inside the database container proves nothing. The psql tool service connects from another container:

The first lines of each run are Compose starting the one-off container after db reports healthy. The role logs in over the network, lands in its own schema through the search_path the init script set, and gets permission denied to create database for anything outside its job.

A populated volume ignores the init settings

POSTGRES_PASSWORD_FILE, POSTGRES_DB and the init scripts are first-start settings. Once the volume holds a database, the image skips all of them. Rotate the application secret and recreate the container to see it:

The recreated container read nothing new, so the new file content and the role's stored password disagree. Rotation is a statement against the running database: ALTER ROLE over the trusted local socket, with the new value read from the secret file inside the container. The same applies to the superuser password and to anything an init script once did: on a populated volume, change it with SQL, and coordinate the application restart with it (or give the application a second role during the switch).

Backup, and a restore you have actually run

"Volumes, bind mounts and tmpfs in practice" copied a stopped Postgres volume with tar. Postgres's own tools need no downtime: pg_dump reads a consistent snapshot of one database while the server keeps serving. The custom format (-Fc) is compressed and lets pg_restore pick objects:

Dump sizes and object IDs vary. The listing shows the schema, the table and its data, all owned by shop_app. Roles are cluster-wide and not part of a database dump; pg_dumpall --roles-only saves them, password hashes included, so treat that file like a secret. The dump file holds every row of the database. Its mode is -rw-r--r--, and only the 0700 backup/ directory keeps other local users away from it, so move it to restricted, encrypted storage. A backup counts once a restore has worked, so restore into a throwaway server:

--create recreates the shop database and --no-owner assigns everything to the restoring user, which is right for a verification copy that has no shop_app role. Both rows came back. Put this check in the backup job itself.

Major upgrades

A major version changes the on-disk format, and the image never converts it. Changing postgres:17 to postgres:18 on an existing volume does not upgrade anything. With the pre-18 mount path the 18 image refuses to start at all, even on an empty volume:

There are two supported ways across a major version. Dump and restore: pg_dumpall from the old server, a new server on a new volume, psql to load it. It is simple, needs disk for both copies and takes downtime proportional to the data size. Or pg_upgrade, which converts the data directory in place (--link uses hard links and needs old and new directories on one filesystem, which the 18 layout under one mount makes possible) and needs the old and new binaries in one container; it is faster for large databases and harder to undo. A dump and restore looks like this:

# 1. stop every service that writes to the database, then dump the old (17) cluster
docker compose exec -T db pg_dumpall -U postgres > backup/all-17.sql
docker compose exec -T db psql -U postgres -d shop -tAc "SELECT count(*) FROM shop.orders"
docker compose down    # keeps the old volume for rollback
# 2. compose.yaml: image postgres:18-alpine, a NEW volume mounted at /var/lib/postgresql,
#    and no POSTGRES_DB and no initdb mount, so the new cluster starts empty
docker compose up -d --wait db
docker compose exec -T db psql -U postgres -d postgres < backup/all-17.sql 2> backup/restore-errors.log
grep ERROR backup/restore-errors.log    # expect only: role "postgres" already exists
docker compose exec -T db psql -U postgres -d shop -tAc "SELECT count(*) FROM shop.orders"

Stop the writers first, or rows written after the dump are lost. The new server starts without POSTGRES_DB and without the init scripts, because the dump recreates the shop database, the shop_app role and the schema itself; with them, those objects would already exist and the load would print a stream of "already exists" errors in which a real failure is easy to miss. pg_dumpall output cannot run with ON_ERROR_STOP, since its CREATE ROLE postgres always fails on a cluster that already has that role, so the errors go to a file and the only one expected is that line. The row counts before and after are the check that the data arrived. Restore POSTGRES_DB and the init mount in the Compose file afterwards if new environments are built from it. Keep the old volume until the new server has passed the same restore check. Clean up the Postgres part:

MySQL and MongoDB: what changes

The pattern carries over: a named volume, _FILE secrets, first-start-only initialisation, a health check that cannot pass during setup, the engine's own dump tool, and no published port. The details differ in ways that bite, and each row below was checked in the lab that follows:

The MySQL files are in ~/lab/r-postgres/mysql, and the commands in this part run from that directory:

name: lab-shop-mysql
 
services:
  db:
    image: mysql:8.4
    environment:
      MYSQL_ROOT_PASSWORD_FILE: /run/secrets/mysql_root_password
      MYSQL_ROOT_HOST: localhost
      MYSQL_DATABASE: shop
      MYSQL_USER: shop_app
      MYSQL_PASSWORD_FILE: /run/secrets/app_db_password
    secrets:
      - mysql_root_password
      - app_db_password
    volumes:
      - mysqldata:/var/lib/mysql
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "--silent"]
      interval: 10s
      timeout: 3s
      retries: 5
      start_period: 60s
      start_interval: 2s
    stop_grace_period: 1m
    restart: unless-stopped
 
volumes:
  mysqldata:
 
secrets:
  mysql_root_password:
    file: ./secrets/mysql_root_password.txt
  app_db_password:
    file: ./secrets/app_db_password.txt

The 0600 secrets worked, because the MySQL entrypoint reads them as root. MYSQL_ROOT_HOST: localhost matters: without it the image creates root@'%', a superuser any container on the network can try passwords against. With it, root exists only for a shell inside the container. The health check output looks alarming and is fine: mysqladmin ping exits 0 whenever the server answers, even when it refuses the login, so it proves the server is up and nothing about credentials. --silent, as in the Compose file, hides the message. During first initialisation MySQL's temporary server does not listen on TCP, so the -h 127.0.0.1 check cannot pass early.

The environment check is the caveat from the table. The MySQL entrypoint exports the values it read from the files, and they stay in mysqld's environment, readable by the mysql user and by root in the container through /proc/1/environ. The step prints only the names. _FILE still keeps the passwords out of docker inspect and the Compose file; it does not keep them out of the process. "Runtime secrets, done right" (Advanced container security) discusses what that means for your threat model.

The image gives MYSQL_USER ALL PRIVILEGES on its database, which includes DROP and ALTER. For an application account, revoke that in an init script and grant what the code uses, typically SELECT, INSERT, UPDATE, DELETE, adding privileges such as EXECUTE one at a time when the application proves it needs them. Rotation behaves exactly as in Postgres: the new secret is ignored on a populated volume until ALTER USER sets it. The printf ... | mysql form keeps the new password off the mysql command line. mysqldump --single-transaction takes a consistent dump of InnoDB tables without locking them; the lab loaded it into a second database and read the rows back. Two more 8.4 facts: accounts use caching_sha2_password, and the old mysql_native_password plugin is disabled by default (MySQL 9 removes it), so very old client libraries fail to log in. And moving from 8.0 to 8.4 upgrades the data directory on first start with no way back, so take a dump with the old version first.

The MongoDB files are in ~/lab/r-postgres/mongo, and the commands in this part run from there. The lab uses mongo:8.2, and the reason is worth knowing:

The 8.0 image (8.0.32) refuses to start on Linux 6.19 and newer, and the lab VM runs kernel 7.0. In the same test the 9.0 image (9.0.2) refused too, and 8.2 (8.2.12) started. On hosts with older kernels 8.0 runs normally. Check MongoDB's release notes and support dates before choosing a line for production, and keep the kernel in mind when you upgrade hosts under an existing Mongo.

name: lab-shop-mongo
 
services:
  db:
    image: mongo:8.2
    environment:
      MONGO_INITDB_ROOT_USERNAME: root
      MONGO_INITDB_ROOT_PASSWORD_FILE: /run/secrets/mongo_root_password
    secrets:
      - mongo_root_password
      - app_db_password
    volumes:
      - mongodata:/data/db
      - ./initdb:/docker-entrypoint-initdb.d:ro
    mem_limit: 1g
    healthcheck:
      # $$HOSTNAME resolves to the container's network address: the temporary init server listens
      # on 127.0.0.1 only, so this check cannot pass before initialisation has finished
      test: ["CMD-SHELL", "mongosh --quiet --norc --host \"$$HOSTNAME\" --eval 'db.adminCommand(\"ping\").ok'"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 60s
      start_interval: 2s
    stop_grace_period: 1m
    restart: unless-stopped
 
volumes:
  mongodata:
 
secrets:
  mongo_root_password:
    file: ./secrets/mongo_root_password.txt
  app_db_password:
    file: ./secrets/app_db_password.txt

// Runs once, in mongosh, when the container starts with an empty /data/db.
const pw = require('fs').readFileSync('/run/secrets/app_db_password', 'utf8').trim();
db.getSiblingDB('shop').createUser({
  user: 'shop_app',
  pwd: pw,
  roles: [{ role: 'readWrite', db: 'shop' }],
});

The health check deserves a look. The Mongo entrypoint's temporary init server listens on 127.0.0.1, and an early version of this file checked localhost: Compose reported the container healthy during setup, and the next command failed with ECONNREFUSED as the temporary server shut down. Connecting to $HOSTNAME, the container's network address, cannot succeed until the real server listens on all interfaces. ping needs no login, so the check proves the server answers, not that credentials work. Create the secrets with mode 0600 again:

Mongo reads the _FILE secrets after dropping to the mongodb user (UID 999), so the 0600 file stops it before anything is written, and the container restarts in a loop. Fix the mode and start clean:

A Mongo user belongs to the database it was created in, here shop, so --authenticationDatabase shop is required and the same password against admin fails. The cache step answers a common sizing question: with mem_limit: 1g, WiredTiger configured a 256 MiB cache, its minimum, because it sizes the cache from the memory limit it sees rather than the host's RAM (the formula is half of the memory minus 1 GiB, at least 256 MiB). Set --wiredTigerCacheSizeGB explicitly when you give Mongo more memory and want a particular split. These commands pass passwords with -p, which puts them in the argument list of a process inside the container for its lifetime; that is acceptable for an administrator's one-off shell, while applications should read the secret file.

mongodump --archive streams a consistent-enough dump of one database to stdout; for a replica set add --oplog to capture writes made during the dump. --nsFrom/--nsTo restored it under a new name for the check.

The last Mongo behaviour concerns an instance that ran without authentication and gets root credentials later. Start one without credentials and write a document, stopping it cleanly so the write is on disk:

With MONGO_INITDB_ROOT_USERNAME and the password set, the entrypoint adds --auth on every start, whatever the volume holds. It creates the root user only on an empty volume. The result is a locked database, not an open one: unauthenticated requests fail, and the root login fails because that user does not exist. The way in is the localhost exception, which lets a client on the same host create the first user while none exist; it closes as soon as one does. Run it from inside the container, reading the password from the secret file. Clean up:

Try this

Run docker compose exec -T db pg_dumpall -U postgres > backup/all-17.sql on a scratch host or disposable cluster and read the output against what this lesson described. Then change one input so it fails, and re-run: the error you get is the one you will meet in production.

Takeaway

If you keep one thing from recipe: postgresql, mysql and mongodb, keep “MySQL and MongoDB: what changes”. Decide now which check you will run when this shows up on a live system, and write it somewhere your team will find it.

Related