Application-consistent vs crash-consistent backups

A snapshot is consistent only if the application is paused during the snapshot. Otherwise the snapshot is crash- consistent: it represents the filesystem at a point in time, but the application may have been mid-write.

Snapshot types

• Application-consistent: the application is paused or flushed before the snapshot. All in-flight writes are committed. On restore, the application starts cleanly.
• Crash-consistent: the snapshot is taken without coordination. The application may be mid-write. On restore, the application recovers as if from a crash (uses its WAL or recovery log).
• Fuzzy: a snapshot that captures neither - some application state is corrupted. Never use for production.

Application-consistent: the session rule

Database quiesce is session-scoped. Both the PostgreSQL backup API and the MySQL global read lock end the moment the client that issued them disconnects.

This is why the pattern below - which you will find in a lot of old blog posts - does not work:

# BROKEN. Do not use. Each invocation is its own session.
psql -c "SELECT pg_start_backup('label');"
# ... snapshot ...
psql -c "SELECT pg_stop_backup();"

mysql -e "FLUSH TABLES WITH READ LOCK;"
# ... snapshot ...
mysql -e "UNLOCK TABLES;"

Each psql -c and mysql -e connects, runs one statement, and disconnects. The backup is aborted and the lock is dropped microseconds later, long before the snapshot command runs. The snapshot is taken against a fully live datadir, and the closing command fails or is a no-op on a fresh session. The result is presented as application-consistent. It is not.

PostgreSQL

Prefer the tool that does the bracketing and the WAL handling for you:

pg_basebackup -D /backup/base -Ft -z -Xs -P -c fast

If you must bracket a filesystem, LVM, or ZFS snapshot, run the whole sequence inside one session:

psql <<'SQL'
SELECT pg_backup_start('snap', fast => true);
\! lvcreate -L 20G -s -n pgdata-snap /dev/vg0/pgdata
SELECT * FROM pg_backup_stop();
SQL

pg_backup_stop() returns the label file and the end LSN. Write that label file into the snapshot as backup_label, or the restore will not know where recovery must start.

MySQL and MariaDB

For InnoDB, take a transactionally consistent dump. No global lock is needed:

mysqldump --single-transaction --quick --routines --events \
    --source-data=2 --all-databases > /backup/all-$(date +%F).sql

--source-data=2 records the binlog coordinates as a comment, which is what you need to rebuild a replica from the dump. On MySQL before 8.0.26 and on MariaDB the option is spelled --master-data=2.

For a physical hot backup, use Percona XtraBackup:

xtrabackup --backup --target-dir=/backup/xtra-$(date +%F)
xtrabackup --prepare --target-dir=/backup/xtra-$(date +%F)

If you genuinely must hold FLUSH TABLES WITH READ LOCK around a snapshot, one live session has to hold it from start to finish:

mysql <<'SQL'
FLUSH TABLES WITH READ LOCK;
SYSTEM lvcreate -L 20G -s -n mysql-snap /dev/vg0/mysql;
UNLOCK TABLES;
SQL

Crash-consistent with LVM

LVM snapshots are atomic at the filesystem level but application-unaware:

# Create snapshot
lvcreate -L 1G -s -n mydata-snap /dev/vg0/mydata

# Mount snapshot (read-only)
mkdir /mnt/snap
# XFS refuses to mount a filesystem whose UUID is already mounted, and a
# snapshot carries the origin's UUID - so on XFS this needs `nouuid`.
# ext4 has no such restriction.
mount -o ro,nouuid /dev/vg0/mydata-snap /mnt/snap   # XFS
# mount -o ro       /dev/vg0/mydata-snap /mnt/snap   # ext4

# Backup
rsync -a /mnt/snap/ /backup/

# Remove snapshot
umount /mnt/snap
lvremove /dev/vg0/mydata-snap

The snapshot is crash-consistent: the filesystem is consistent, but the application may have been mid-write. On restore, the database recovers as if from a crash.

For PostgreSQL, MySQL, and most modern databases, crash- consistent recovery works. For some applications (e.g. without WAL), it does not.

Application-aware snapshots

Some applications have snapshot hooks:

• PostgreSQL: pg_basebackup does a consistent backup with WAL, in one session, without you bracketing anything.
• MySQL: xtrabackup (Percona XtraBackup) does physical hot backups; mysqldump --single-transaction does logical ones.
• Filesystems: ZFS and btrfs have snapshot-aware semantics.

For a critical application, use the application backup tool rather than a generic snapshot.

When to use each

Knowledge check