Application-consistent backups — quiesce and snapshot

An application-consistent backup is one the application will open without complaint and whose contents represent a state the application actually reached. There are exactly three ways to obtain one, and they differ in downtime, in restore speed, and in what they promise.

Everything else — tar of a live directory, rsync, a docker commit, an “online copy” — is in the smeared category from the previous lesson and gives you no guarantee at all.

Method 1: stop and copy

The one people skip because it sounds primitive. It is the only method with no subtleties, and for a stack with a maintenance window it is the right answer.

STACK=/srv/app
VOLUME=app_pgdata
STAMP=$(date +%F)

# Stop the writer, and everything that writes to the writer
docker compose -f "$STACK/compose.yaml" stop web db

# Confirm it is genuinely stopped, not merely asked to stop
docker compose -f "$STACK/compose.yaml" ps --format '{{.Service}} {{.State}}'

docker run --rm -v "$VOLUME":/source:ro -v /backup:/backup alpine:3.20 tar czf "/backup/pgdata-$STAMP.tar.gz" -C /source .

docker compose -f "$STACK/compose.yaml" start db web

Two details make this work rather than merely look like it works.

Stop the application before the database. docker compose stop web db with depends_on declared stops in reverse dependency order, so the application closes its connections first and the database sees a clean disconnect. Stopping the database out from under a live application leaves it doing crash recovery on the next start, which is a self-inflicted version of the problem you are trying to avoid.

Verify the stop. docker compose stop sends SIGTERM and waits — by default ten seconds — then SIGKILL. A database killed at ten seconds has not finished its shutdown checkpoint. Use --timeout 60 and check the state, rather than assuming the command’s exit code means the process shut down cleanly.

Method 2: logical dumps

The database produces the archive, so it is consistent by construction. Slower to restore than a physical copy, but portable across versions and architectures, and selectively restorable.

STAMP=$(date +%F)
DEST=/backup

# PostgreSQL - custom format, compressed, restorable table by table
docker exec app-db pg_dump -U app --format=custom --file=/tmp/app.dump app
docker cp app-db:/tmp/app.dump "$DEST/app-$STAMP.dump"
docker exec app-db rm -f /tmp/app.dump

# PostgreSQL - roles and tablespaces, which pg_dump does NOT include
docker exec app-db pg_dumpall -U postgres --globals-only > "$DEST/globals-$STAMP.sql"

# MySQL / MariaDB - --single-transaction is what makes it consistent
docker exec app-mysql mysqldump --single-transaction --routines --triggers --events --user=app --password="$MYSQL_PASSWORD" app > "$DEST/app-$STAMP.sql"

# MongoDB
docker exec app-mongo mongodump --db app --archive=/tmp/app.archive --gzip
docker cp app-mongo:/tmp/app.archive "$DEST/app-$STAMP.archive"

The flags are the lesson here, not the commands.

pg_dump is consistent without any flag. It opens a repeatable-read transaction and dumps from that snapshot, so the output represents the database at the instant the dump started. Concurrent writes continue and are simply not in the dump. This is genuinely safe.

pg_dump does not dump roles, tablespaces or other cluster-level objects. It dumps one database. A restore from pg_dump alone into a fresh cluster produces a database whose grants reference roles that do not exist. That is why pg_dumpall --globals-only is a second line and not a footnote — it is the most common reason a Postgres restore drill stalls.

mysqldump --single-transaction is required, not recommended. Without it, mysqldump reads tables one after another with no shared snapshot, and the result is smeared exactly like a tar. It works for transactional engines (InnoDB); a MyISAM table in the same database is inconsistent regardless.

Verifying a dump can actually be restored

DUMP=/backup/app-2026-08-12.dump

# pg_restore --list parses the archive header and TOC. It fails on a
# truncated or corrupt custom-format dump.
docker run --rm -v /backup:/backup:ro postgres:16 pg_restore --list "/backup/$(basename "$DUMP")" | head -20

# Count the objects it believes it can restore
docker run --rm -v /backup:/backup:ro postgres:16 pg_restore --list "/backup/$(basename "$DUMP")" | grep -c '^[0-9]'

pg_restore --list is a real check with a real failure. It is not a restore — it does not prove the row contents are correct — but it does prove the archive is structurally complete and tells you how many objects are in it. A dump that suddenly lists 3 objects instead of 240 is a signal available tonight rather than in six months.

Method 3: bracketed filesystem snapshots

For a large database where a logical dump takes hours and a restore takes longer, the physical copy is the answer: snapshot the filesystem atomically, and tell the database that you did.

#!/usr/bin/env bash
set -euo pipefail

VG=vg0
LV=docker
MNT=/var/lib/docker
SNAP=docker-backup
SNAPSIZE=20G

# The thaw must happen even if the script dies. This trap is the whole
# difference between a one-second pause and a hung host.
cleanup() {
fsfreeze -u "$MNT" 2>/dev/null || true
umount /mnt/snap 2>/dev/null || true
lvremove -f "/dev/$VG/$SNAP" 2>/dev/null || true
}
trap cleanup EXIT

# 1. Ask the database to flush so recovery on restore is short
docker exec app-db psql -U postgres -c 'CHECKPOINT;'

# 2. Freeze, snapshot, thaw. Keep this window as short as possible.
fsfreeze -f "$MNT"
lvcreate --size "$SNAPSIZE" --snapshot --name "$SNAP" "/dev/$VG/$LV"
fsfreeze -u "$MNT"

# 3. Archive the snapshot at leisure - the origin is live again
mkdir -p /mnt/snap
mount -o ro "/dev/$VG/$SNAP" /mnt/snap
tar czf "/backup/docker-$(date +%F).tar.gz" -C /mnt/snap volumes

# 4. cleanup() unmounts and removes the snapshot

Postgres: the current low-level API

If you snapshot a Postgres data directory without telling Postgres, you get a crash-consistent copy, and the engine will perform crash recovery on restore. That usually works. Telling Postgres converts “usually works” into “is supported”, by writing a backup_label that points recovery at the right starting WAL location.

# Begin. The session must stay open for the whole backup.
docker exec app-db psql -U postgres -c "SELECT pg_backup_start('nightly-snapshot', fast => true);"

# ... take the atomic filesystem snapshot here ...

# End. Returns the LSN, the backup_label contents, and the tablespace map.
docker exec app-db psql -U postgres -c "SELECT * FROM pg_backup_stop(wait_for_archive => true);"

Three things about this that people get wrong:

The function names changed. pg_start_backup() and pg_stop_backup() were removed in PostgreSQL 15 and replaced by pg_backup_start() and pg_backup_stop(). A script or a document using the old names does not work on any currently supported version — it fails with function pg_start_backup does not exist, which at least is loud.

pg_backup_stop returns data you must write down. It returns one row with three values. The second must be written to a file called backup_label in the root of the backup, and the third to tablespace_map if it is non-empty, byte for byte. A snapshot taken between the two calls but without those files is not a valid base backup, and Postgres will tell you so on restore — after you have lost the WAL you needed.

The two calls must be in the same session. docker exec ... psql -c opens a connection, runs, and exits, which ends the backup. Running the pair as two separate docker exec calls, as written above for readability, does not work as a real procedure: use a single psql session (a heredoc, or psql -f) holding the connection across the snapshot, or — far better — use pg_basebackup, which does the whole dance correctly on your behalf.

STAMP=$(date +%F)

docker exec app-db pg_basebackup -U replicator --pgdata=/tmp/basebackup --format=tar --gzip --wal-method=stream --checkpoint=fast --progress

docker cp app-db:/tmp/basebackup "/backup/basebackup-$STAMP"
docker exec app-db rm -rf /tmp/basebackup

--wal-method=stream opens a second connection that streams the WAL generated during the backup into the output, so the result is self-contained and restorable without a separate archive. This is the flag that makes the artefact useful on its own, and omitting it is the usual reason a pg_basebackup restore asks for WAL segments nobody kept.

The quiesce mechanisms that do not quiesce

Two pieces of advice circulate widely and are wrong. Both appear in scripts that have never been restore-tested, which is how they survive.

pg_advisory_lock() does not stop writes. An advisory lock is purely cooperative: it blocks only other sessions that explicitly try to take the same advisory lock. Ordinary INSERT and UPDATE statements from the application know nothing about it and proceed at full speed. A backup bracketed by pg_advisory_lock(1) and pg_advisory_unlock(1) is exactly as smeared as one with no bracket at all, and it looks more careful.

FLUSH TABLES alone does not hold a MySQL database still. It closes and reopens tables and flushes buffers, at that instant, and then normal operation resumes immediately. The mechanism that actually holds writes is FLUSH TABLES WITH READ LOCK, and it only holds for as long as the session that issued it stays connected — so a docker exec ... mysql -e 'FLUSH TABLES WITH READ LOCK' takes the lock and releases it on exit, microseconds later. Same shape of error as the Postgres one above.

Knowledge check