Docker Compose Postgres Backup: pg_dump, Cron and Restore Tests

What a Postgres backup in Docker Compose actually is

A Postgres backup in a Compose deployment is the same as a backup anywhere else. The container only changes where the data directory lives (usually a named volume) and how you reach the server (docker compose exec or a network connection). There are two families of backup, and they recover different things.

Logical backups (pg_dump, pg_dumpall) export SQL or an archive of schema and rows. pg_dump reads one database from a single transaction snapshot, so the output is consistent even while writes continue. It does not include roles or tablespaces. pg_dumpall exports those cluster-wide objects as well. Restores replay inserts and rebuild every index, which is why they are slower than physical restores. In exchange, they work across major versions and let you restore a single table.

Physical backups copy the cluster's data files. You can make them with pg_basebackup against a running server, or by copying files while the server is stopped. If you also archive WAL continuously, you can replay up to a chosen moment. Restoring a physical backup requires the same major version.

Running tar or rsync against a live named volume does not produce a valid backup. The server keeps writing pages and WAL while the copy runs, so you capture files from different moments that disagree with each other. The copy can start up cleanly and still contain corrupted data.

A backup only counts once you have restored it and queried the result. Until then you have a file and no evidence that it works. For the details of each method, read the Backup and Restore chapter of the PostgreSQL documentation.

Who should use logical dumps, and when to avoid them

Logical dumps fit best when one Compose host runs one Postgres container and the database is small to medium. "Medium" here means a dump and a full restore both finish well inside the downtime you can tolerate. Backend and platform teams in this position get a lot from pg_dump. The output is portable across Postgres major versions and CPU architectures, and you can restore a single table from it. It also doesn't depend on how the container stores its volume. Your restore target can be a laptop, a CI runner or a fresh host.

When dumps alone are not enough

You cannot lose recent writes. A dump captures the database at the moment its snapshot was taken. If you dump nightly and the volume fails at 17:00, every write since the previous night is lost. If that is unacceptable, you need continuous WAL archiving and point-in-time recovery. A dump cannot replay changes made after it ran.

Restore time exceeds your recovery target. A logical restore reloads every row, then rebuilds every index and revalidates every constraint. This often takes much longer than the dump itself. Time a real restore. If it runs past your recovery time objective, a physical backup is the better fit.

The primary is busy and the dump runs long. pg_dump reads from a single transaction snapshot so the output is consistent. While that snapshot is open, vacuum cannot remove row versions that are newer than the snapshot. On a write-heavy database this causes table and index bloat, and the dump adds sustained read I/O on top of production traffic. You can schedule dumps in quiet periods or take them from a replica.

How the backup and restore-test pipeline fits together

The pipeline has five parts. Each one runs outside the database container on purpose.

Trigger: host cron

A cron entry on the Docker host runs a backup script. The scheduler lives on the host because the database container is replaceable. Recreating it with docker compose up after an image bump should not wipe the schedule, and a cron daemon inside the Postgres container is an extra process that the official image does not run or supervise.

Dump: docker compose exec -T

The script runs pg_dump and pg_dumpall --globals-only inside the db service through docker compose exec -T. Using the service's own client binaries keeps the client version matched to the server. The -T flag disables pseudo-TTY allocation. Without it, cron has no terminal to attach, and a TTY can mangle binary custom-format output. The globals dump matters because pg_dump does not include roles or tablespaces, and a restore whose owners and grants reference missing roles fails partway through.

Storage: stream to the host, then copy off-host

Output streams over stdout into files on the host. Nothing is staged in the container's writable layer or in the data volume. The script then copies the files to storage on another machine. A dump that sits on the same disk as the volume it protects is lost in the same failure.

Verification: a separate restore test

A second job, scheduled independently, verifies the backups. It starts a throwaway Compose project under a different project name with an empty volume, restores the globals and then the latest dump, runs sanity checks, and tears the project down along with its volume. Keeping this job separate means a broken restore cannot touch production. It also means a failed restore test shows up as its own alert and is not hidden inside a backup job that reported success.

CopyDOCKER HOST (production compose project)
+------------+     +---------------+  exec -T  +---------------------------+
| host cron  |---->| backup script |---------->| db service                |
+------------+     +---------------+           |  pg_dump -Fc              |
                     |         ^               |  pg_dumpall --globals-only|
                     |         |    stdout     |  (client = server version)|
                     |         +---------------|  volume: prod data        |
                     v                         +---------------------------+
              +-------------------------+
              | dump files on host disk |
              | (nothing staged in the  |
              |  container or volume)   |
              +-------------------------+
                     |  copy off-host
                     v
+--------------------------------------------+
| OFF-HOST STORAGE (another machine)         |
|  globals.sql + latest custom-format dump   |
+--------------------------------------------+
                     |  fetch latest
                     v
+------------+     +--------------------------------------------------+
| cron (own  |---->| RESTORE-TEST JOB (throwaway project, other -p)   |
| schedule)  |     |  1. up with a new, empty volume                  |
+------------+     |  2. restore globals, then latest dump            |
                   |  3. sanity checks -> separate alert on failure   |
                   |  4. down -v: remove project and its volume       |
                   +--------------------------------------------------+
                    isolated: cannot touch prod project or volume

Host cron runs a script that dumps the db service with docker compose exec -T and streams the output to the host, then off-host; a separately scheduled job restores the latest dump into a throwaway Compose project and tears it down.

Implementing the backup job with pg_dump and host cron

Run the dump inside the database container with docker compose exec -T db pg_dump -Fc. The -T flag disables TTY allocation, so binary output is not corrupted when it is piped to the host. Running inside the container also keeps client and server versions matched. pg_dump can read older servers but refuses newer ones, so a host-installed client drifts out of date the first time someone bumps the image. Pin the major version in Compose (postgres:18, not latest) so an upgrade is a deliberate change and not a side effect of a pull.

Start the script with set -euo pipefail. Without pipefail, a failed pg_dump piped into a file still reports success. Set umask 077 before writing anything, because dumps contain every row and password hash. Stream the output to name.dump.tmp and mv it to a timestamped name such as app-20261020T0300Z.dump only after the command exits zero. A truncated file then never looks like a valid backup.

Save pg_dumpall --globals-only output alongside each dump. Roles and tablespaces live outside any single database, and a restore without them fails on ownership. Prune old files with find -mtime after a successful run, never before.

Keep credentials out of the script. Use an env file with mode 600, or use Compose secrets with the image's POSTGRES_PASSWORD_FILE.

Schedule the script in the host crontab, for example 15 3 * * *, with output redirected to a log. Log the exit status explicitly. As a last step, copy the files off the host with rsync or an object-storage upload. A backup that lives on the same disk as the database disappears with it.

Copy#!/usr/bin/env bash
# /opt/pgbackup/backup.sh  (compose.yaml pins the server: image: postgres:18)
# crontab -e:  15 3 * * * /opt/pgbackup/backup.sh >> /var/log/pgbackup.log 2>&1
set -euo pipefail
umask 077                      # dumps and globals readable by the owner only

# Log a timestamp and the exit status on every run, success or failure
trap 'rc=$?; rm -f "$DIR"/*.tmp; echo "$(date -u +%FT%TZ) backup exit=$rc"' EXIT

# Mode 600 env file, not the script, holds connection settings:
# POSTGRES_USER=app  POSTGRES_DB=app  BACKUP_REMOTE=backup@backup-host:/srv/pg/app/
set -a; source /opt/pgbackup/backup.env; set +a

cd /opt/app                    # directory containing compose.yaml
DIR=/var/backups/postgres
STAMP=$(date -u +%Y%m%dT%H%MZ)
mkdir -p "$DIR"

# Client tools run inside the db container, so they always match the server.
# -T disables the TTY so the binary custom-format output is not mangled.
docker compose exec -T db pg_dump -U "$POSTGRES_USER" -Fc "$POSTGRES_DB" \
  > "$DIR/app-$STAMP.dump.tmp"
mv "$DIR/app-$STAMP.dump.tmp" "$DIR/app-$STAMP.dump"

# Roles and tablespaces live outside the database; keep them next to the dump
docker compose exec -T db pg_dumpall -U "$POSTGRES_USER" --globals-only \
  > "$DIR/globals-$STAMP.sql.tmp"
mv "$DIR/globals-$STAMP.sql.tmp" "$DIR/globals-$STAMP.sql"

# Ship off the host; no --delete, so remote copies outlive local pruning
rsync -a "$DIR/" "$BACKUP_REMOTE"

# Prune only after the dump and the copy have both succeeded
find "$DIR" -maxdepth 1 \( -name 'app-*.dump' -o -name 'globals-*.sql' \) \
  -mtime +14 -delete

A host-cron backup script that dumps through docker compose exec -T, writes to temp files and renames them only on success, stores globals next to each dump, ships the files off the host, prunes old copies afterwards, and logs the exit status.

Automating the restore test

A dump that has never been restored is an assumption, not a backup. The restore test turns it into evidence, and it should run on the same schedule as the backup job.

Start a throwaway Postgres service under its own project name (docker compose -p restore-test) or behind a dedicated profile. A separate project name gives the test its own containers, network and named volume, so it cannot attach to production data. Use the same major version image as production, and always start from an empty volume.

Restore in two steps. First load the globals file with psql -v ON_ERROR_STOP=1 so roles exist before any object references them. Then run pg_restore against the custom-format dump with --exit-on-error and -j set to the number of available cores. Parallel jobs only work with custom or directory format read from a file, which is one reason to choose that format. Capture both exit codes, and fail the job if either is non-zero.

A clean exit only proves that the file parses and the schema applies. It does not prove the data is what you expected. An empty table restores without error. So run sanity queries next: row counts on a handful of key tables, compared against a threshold or yesterday's numbers, plus the latest version recorded in your migration tool's tracking table. Then tear everything down with docker compose -p restore-test down -v.

Run the test on a separate host or during quiet hours. A parallel restore saturates CPU and disk I/O, and that load would compete with production. Record the wall-clock time of each run. That figure is your measured recovery time, and it is more reliable than any estimate.

Copy#!/usr/bin/env bash
# Nightly restore test. Assumes restore-test.yml defines service "db" using the
# same major Postgres image as production, a named volume for PGDATA, and
# ./backups mounted read-only at /backups.
set -euo pipefail

DUMP=/backups/app_latest.dump        # custom-format (pg_dump -Fc), path inside container
GLOBALS=./backups/globals_latest.sql # pg_dumpall --globals-only, path on host
MIN_ORDERS=100000                    # threshold for the sanity check

# Separate project name: own containers, network and volume, never production's.
dc() { docker compose -p restore-test -f restore-test.yml "$@"; }
trap 'dc down -v' EXIT               # always tear down, including the volume

start=$(date +%s)
dc up -d db
until dc exec -T db pg_isready -U postgres >/dev/null 2>&1; do sleep 1; done

# 1) Roles first. Skip the superuser, which already exists in a fresh cluster.
grep -v '^CREATE ROLE postgres;' "$GLOBALS" \
  | dc exec -T db psql -U postgres -v ON_ERROR_STOP=1 -q

# 2) Data. -j needs a file on disk, not stdin. set -e fails the job on non-zero exit.
dc exec -T db createdb -U postgres -O app_owner app
dc exec -T db pg_restore -U postgres -d app --exit-on-error -j "$(nproc)" "$DUMP"

# A clean exit only proves the file parses; check the data is actually there.
q() { dc exec -T db psql -U postgres -d app -Atc "$1"; }
orders=$(q 'SELECT count(*) FROM orders')
[ "$orders" -ge "$MIN_ORDERS" ] || { echo "orders too low: $orders" >&2; exit 1; }
version=$(q 'SELECT max(version) FROM schema_migrations')

# Wall-clock time from empty volume to verified data = measured recovery time.
echo "restore OK: orders=$orders migration=$version elapsed=$(( $(date +%s) - start ))s"

A restore-test script that starts a throwaway Postgres under its own Compose project, loads globals and then a parallel pg_restore that fails fast on errors, checks row counts and the latest migration version, reports elapsed recovery time, and removes all containers and volumes on exit.

Alternatives: volume snapshots, stop-and-copy, and WAL archiving

Logical dumps are not the only option. The other methods copy the cluster's data files instead of its SQL contents. That changes the trade-offs in three ways.

Stop-and-copy is consistent because no process is writing to the files. Snapshots are only safe if they capture every file in the cluster, including WAL, at a single instant. On start, PostgreSQL treats the snapshot as a crash and replays WAL to reach a consistent state.

All physical copies depend on the PostgreSQL major version and on the CPU architecture that created them. You cannot restore a version 16 data directory into a version 17 image, and moving the files between hosts with different architectures is also unsafe. A dump has neither restriction.

Choose by how much data you can afford to lose. If losing a day of writes is acceptable, nightly dumps are enough. If you can lose only minutes, you need WAL archiving with point-in-time recovery. Add regular restore tests that replay the archived WAL, so you know the archive is complete and usable.

Impact on cost and engineering time

Storage cost grows with retention depth, and depth is what protects you from slow-moving damage. If a bad migration or an application bug corrupts rows on Monday and nobody notices until Friday, seven days of nightly dumps may all contain the bad data. Tiered retention keeps older restore points without keeping every night's dump.

Illustrative estimate (assumed numbers, not a benchmark): assume a compressed dump of 2 GB, with 7 daily, 4 weekly and 6 monthly copies retained. Storage = dump size × (daily + weekly + monthly) = 2 GB × (7 + 4 + 6) = 34 GB. Keeping 30 daily copies instead would need 2 GB × 30 = 60 GB and still cover only one month.

Restore tests cost CPU and disk. Each test needs a scratch instance with enough space for the restored database and its indexes, and index rebuilds usually dominate the runtime. Running the test off-peak or on a separate host keeps it from competing with production. WAL archiving costs more to set up than dumps. You have to configure archiving, store base backups, monitor archive lag and manage WAL retention.

Cron fails silently. A changed password or a full disk can leave you with no file, or with a truncated one. Alert when the newest dump is missing, older than expected, or much smaller than the previous one.

An automated restore test saves engineering time during incidents. Without one, recovery is improvised under pressure. With one, you have a rehearsed procedure with a known duration, and you can tell stakeholders how long recovery will take.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to docker compose postgres backup, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.