Creating Consistent Database Snapshots

1. Why a filesystem snapshot alone does not guarantee consistency

A database snapshot that simply takes a filesystem image of the data directories while the database is actively processing writes risks being frozen mid-transaction. InnoDB and PostgreSQL do not write data to disk in a single atomic step, but across multiple files and buffers, redo logs, tablespaces, write-ahead logs. A database snapshot taken at exactly that moment can capture tables and their indexes in different states.

What makes this insidious is that such an inconsistent database snapshot does not show any problem at creation time, no error, no warning. Only during the actual restore, often weeks or months later in an emergency, does the database show integrity errors, missing indexes, or even silently wrong data. A consistent database snapshot must therefore technically guarantee that all involved files reflect exactly the same logical point in time, not merely that they happened to be copied within the same second.

2. Crash consistent versus application consistent

Database snapshots distinguish two levels of consistency. A crash consistent snapshot matches the state the database would find after a hard power outage: through redo logs and crash recovery mechanisms, InnoDB or PostgreSQL can automatically return to a consistent state from that point, provided the snapshot was truly created atomically, meaning all blocks were frozen at exactly the same instant. That is exactly what LVM and Btrfs snapshots deliver when used correctly.

An application consistent database snapshot goes one step further: the database is actively instructed, before the snapshot, to flush all buffered changes to disk and to temporarily stop accepting new writes. The result is a state that is immediately usable at restore time, without any crash recovery. For most production setups, combining a brief application consistent freeze with a subsequent crash consistent snapshot is the most practical path to a truly reliable database snapshot.

3. Combining FLUSH TABLES WITH READ LOCK with LVM snapshots

For MySQL and MariaDB, FLUSH TABLES WITH READ LOCK is the classic command for briefly forcing a consistent state for a database snapshot. It writes all buffered changes to disk and blocks new writes until the lock is released again. During that lock, which ideally lasts only a few seconds, an LVM snapshot of the underlying logical volume is created, permanently capturing the frozen state.

The decisive advantage of this combination: the actual mysqldump or copy operation no longer happens during the lock, but afterwards, on the already created LVM snapshot. The lock itself can therefore be released again after just a few seconds, while the actual, potentially lengthy backup runs from the frozen snapshot without further blocking production traffic. A database snapshot created this way is both consistent and associated with minimal downtime.

#!/usr/bin/env bash
# flush-lock-lvm-snapshot.sh — consistent database snapshot via short freeze + LVM
set -euo pipefail

readonly DB_USER="backup_user"
readonly VG_NAME="data-vg"
readonly LV_NAME="mysql-lv"
readonly SNAP_NAME="mysql-snap-$(date +%Y%m%d%H%M%S)"

# Step 1: freeze the database briefly — flush buffers, block new writes
mysql -u "$DB_USER" -e "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(2);" &
FLUSH_PID=$!
sleep 1

# Step 2: while frozen, create the LVM snapshot — this is the atomic moment
lvcreate --size 5G --snapshot --name "$SNAP_NAME" "/dev/${VG_NAME}/${LV_NAME}"

# Step 3: release the lock immediately — the snapshot already captured a
# consistent state, the database can resume writing right away
mysql -u "$DB_USER" -e "UNLOCK TABLES;"
wait "$FLUSH_PID" 2>/dev/null || true

echo "[OK] Consistent database snapshot created: $SNAP_NAME"

# Step 4: mount the snapshot read-only and run the actual backup from there
mkdir -p /mnt/db-snapshot
mount -o ro "/dev/${VG_NAME}/${SNAP_NAME}" /mnt/db-snapshot
tar -czf "/var/backup/db-snapshot-$(date +%Y%m%d).tar.gz" -C /mnt/db-snapshot .
umount /mnt/db-snapshot
lvremove -f "/dev/${VG_NAME}/${SNAP_NAME}"

4. Mariabackup for hot backups without a global lock

For large InnoDB heavy databases, a global FLUSH TABLES WITH READ LOCK, even for a few seconds, is sometimes not acceptable. Mariabackup, the fork of Percona XtraBackup for MariaDB, solves this problem by reading InnoDB tables directly from the data files without a lock, while capturing the redo logs in parallel. Only at the very end, for a very short period, does it briefly lock to also capture non-transactional tables like MyISAM consistently.

The result is a database snapshot created with practically no noticeable downtime, while consistency is guaranteed by replaying the captured redo logs during the prepare step. This two stage process, backup followed by prepare, is the reason a Mariabackup database snapshot is not restorable immediately after copying, but only after the explicit --prepare run, which applies the recorded transactions and produces a consistent final state.

#!/usr/bin/env bash
# mariabackup-hot.sh — consistent database snapshot without a global lock
set -euo pipefail

readonly BACKUP_DIR="/var/backup/mariabackup/$(date +%Y%m%d-%H%M%S)"
readonly PREPARE_DIR="${BACKUP_DIR}-prepared"

mkdir -p "$BACKUP_DIR"

# Phase 1: copy InnoDB data files without locking, tracking redo log changes
mariabackup --backup \
  --target-dir="$BACKUP_DIR" \
  --user=backup_user \
  --password="$(cat /etc/mysql/backup.pass)"

# Phase 2: apply the tracked redo log to reach a consistent, restorable state
cp -a "$BACKUP_DIR" "$PREPARE_DIR"
mariabackup --prepare --target-dir="$PREPARE_DIR"

echo "[OK] Consistent database snapshot prepared at $PREPARE_DIR"

# Restore: stop MariaDB, clear the data directory, copy back, fix ownership
# mariabackup --copy-back --target-dir="$PREPARE_DIR"
# chown -R mysql:mysql /var/lib/mysql

5. LVM and Btrfs snapshots for consistent filesystem states

LVM snapshots are based on copy-on-write: when the snapshot is created, no data block is copied, only a marker is set so that future writes to the original first have to save the old block content into the snapshot area. This technique makes creating a database snapshot nearly instant, regardless of volume size, precisely because nothing is actually copied, only a copy-on-write pointer is set.

Btrfs offers the same underlying idea natively at the filesystem level, without having to manage separate snapshot volumes. A btrfs subvolume snapshot is likewise copy-on-write and is even easier to automate than LVM, because no separate logical volume of fixed size has to be reserved in advance. For both technologies the same rule applies: the snapshot itself is only crash consistent if it is created during a FLUSH TABLES WITH READ LOCK or an equivalent database freeze, otherwise the underlying inconsistency from section one remains.

6. PostgreSQL: pg_basebackup and WAL archiving

PostgreSQL follows a different, in many cases more elegant approach to consistent database snapshots. pg_basebackup copies the data directories online, while the database keeps running normally, without a global lock. The changes that occur during the copy are captured through the write-ahead log (WAL) and shipped alongside the backup as part of the result. During a restore, PostgreSQL automatically replays this WAL segment and thereby reaches a consistent state, exactly at the point the backup finished.

For point-in-time recovery, meaning restoring to an exact moment between two base backups, continuous WAL archiving must also be enabled. That produces an uninterrupted stream of every transaction, allowing a database snapshot to be restored up to any second before a faulty DELETE statement, instead of only going back to the last nightly backup. This precision clearly distinguishes PostgreSQL backups from a plain daily database snapshot without WAL archiving.

#!/usr/bin/env bash
# pg-basebackup.sh — consistent PostgreSQL snapshot with WAL archiving
set -euo pipefail

readonly BACKUP_DIR="/var/backup/postgres/$(date +%Y%m%d-%H%M%S)"
readonly WAL_ARCHIVE="/var/backup/postgres-wal"

mkdir -p "$BACKUP_DIR" "$WAL_ARCHIVE"

# pg_basebackup runs online, no global lock, WAL changes are captured
pg_basebackup \
  --pgdata="$BACKUP_DIR" \
  --format=tar \
  --gzip \
  --wal-method=stream \
  --checkpoint=fast \
  --label="nightly-consistent-snapshot" \
  --host=localhost \
  --username=replicator

echo "[OK] Consistent PostgreSQL snapshot created at $BACKUP_DIR"

# postgresql.conf must enable continuous WAL archiving for point-in-time recovery:
# archive_mode = on
# archive_command = 'test ! -f /var/backup/postgres-wal/%f && cp %p /var/backup/postgres-wal/%f'

7. Snapshot automation with a locking strategy

A manually executed database snapshot is worthless in an emergency if it does not run regularly and reliably in an automated fashion. Automation has to guarantee two things: first, that the lock duration under FLUSH TABLES WITH READ LOCK genuinely stays short and is not indefinitely extended by a stuck LVM operation, which would block production. Second, that a failed snapshot attempt always releases the lock, even if a script step aborts in the middle.

A trap based cleanup that releases the lock no matter what, whether the script completes normally or aborts with an error, is mandatory for production database snapshot automation. Likewise, the snapshot size in the LVM approach should be generously sized, because a copy-on-write snapshot that runs out of reserved space during an ongoing backup is automatically discarded by LVM, invalidating the entire database snapshot.

#!/usr/bin/env bash
# automated-db-snapshot.sh — reliable locking with guaranteed cleanup
set -euo pipefail

readonly DB_USER="backup_user"
readonly VG_NAME="data-vg"
readonly LV_NAME="mysql-lv"
readonly SNAP_NAME="auto-snap-$(date +%Y%m%d%H%M%S)"
LOCK_ACQUIRED=0

cleanup() {
  if [[ "$LOCK_ACQUIRED" -eq 1 ]]; then
    mysql -u "$DB_USER" -e "UNLOCK TABLES;" 2>/dev/null || true
    echo "[INFO] Lock released during cleanup"
  fi
}
trap cleanup EXIT

mysql -u "$DB_USER" -e "FLUSH TABLES WITH READ LOCK;"
LOCK_ACQUIRED=1

# Generously sized snapshot: 20% of source volume, avoids running out of COW space
lvcreate --size 10G --snapshot --name "$SNAP_NAME" "/dev/${VG_NAME}/${LV_NAME}"

mysql -u "$DB_USER" -e "UNLOCK TABLES;"
LOCK_ACQUIRED=0

echo "[OK] Database snapshot $SNAP_NAME created with guaranteed lock cleanup"

8. Snapshot validation after the backup

A database snapshot that is considered consistent but was never checked is an unproven assumption. The most reliable validation is an actual test restore into an isolated MariaDB or PostgreSQL instance, followed by CHECK TABLE or pg_amcheck respectively, to uncover structural integrity errors in tables and indexes. A database snapshot that was only copied, but never actually restored, can contain errors that only surface during a real restore.

Beyond the structural check, a content level spot check is worthwhile: does the row count in critical tables roughly match the expected growth, are key foreign key relationships intact. This validation should run automatically after every database snapshot created and trigger a warning on any deviation, rather than being discovered manually weeks later during the actual emergency.

9. Snapshot methods compared

Choosing the right method for a consistent database snapshot depends heavily on database size, acceptable lock duration, and available infrastructure.

For most Magento and PHP applications running MariaDB, combining Mariabackup for the daily full backup with an occasional validated LVM snapshot for quick intermediate states is the most pragmatic path to a reliable database snapshot. PostgreSQL environments benefit the most from continuous WAL archiving, because it enables point-in-time recovery without any additional tools.

10. Summary

A consistent database snapshot does not result from simply copying files, but requires either a brief application consistent freeze via FLUSH TABLES WITH READ LOCK ahead of a crash consistent LVM or Btrfs snapshot, or a specialized tool such as Mariabackup, which achieves consistency through redo log replay without a global lock. PostgreSQL solves the same problem more elegantly with pg_basebackup and continuous WAL archiving, which additionally enables point-in-time recovery.

Regardless of the chosen method: a database snapshot that has never been validated through a real test restore is an unproven assumption. Only the combination of correct locking, automated execution with guaranteed cleanup, and regular validation turns a database snapshot into a reliable foundation for an actual emergency.

11. FAQ: Creating Consistent Database Snapshots