Learning Restic - Consistent Database & Application Backups

#Introduction

In episode 9 your backup script already runs automatically. But there is one class of data that makes naive backups dangerous: databases. Copying .frm, .ibd, or base/... files while the database is writing is a recipe for corruption — half-written files produce a restore that won't open.

This episode dissects why consistency matters, and how to combine the official database tools (pg_dump/mysqldump) with restic.

#Filesystem Snapshot ≠ Logical DB Backup

Why can't we just do restic backup /var/lib/postgresql? Because databases use a buffer pool and WAL: the data in files can be mid-write, and files copied without understanding WAL ordering are not guaranteed to be consistent. The restore may succeed, but the database refuses to start.

The principle: logical backup (SQL dump) for consistency, filesystem backup for speed — and the two cannot replace each other.

#The Role of WAL in Consistency

PostgreSQL writes changes to the WAL (Write-Ahead Log) before the data files. A consistent filesystem backup must include the data plus enough WAL, or use mechanisms like pg_basebackup with --wal-method=stream.

For MySQL/MariaDB, the equivalent is redo/undo logs. Because handling this is genuinely complex, the recommended practice for most teams is a logical dump — the easiest to verify and restore.

#Pre/Post Hooks with a Staging Directory

A clean pattern: pg_dump/mysqldump writes to a staging directory, then restic backs up that staging — not the database data files directly:

pg_dump -Fc -f /var/backup/staging/appdb.dump appdb
mysqldump --single-transaction --routines --triggers \
  -u backup -p"$MYSQL_PW" appdb > /var/backup/staging/appdb.sql

Then back up the staging:

restic backup /var/backup/staging --tag db-daily

Because the dump is a validated text/binary file, its restore is deterministic: pg_restore or mysql < file.sql will always succeed as long as the dump is intact.

#--single-transaction for MySQL

The --single-transaction flag makes the dump read a consistent InnoDB snapshot without locking tables — without it, the dump could mix data from different points in time.

#Backing Up PostgreSQL

For PostgreSQL, two approaches are worth using:

pg_dump -Fc -f /var/backup/staging/appdb.dump appdb
restic backup /var/backup/staging --tag db-daily

The -Fc (custom) format supports selective restore and compression — suited for large repositories. Restore:

restic dump latest /var/backup/staging/appdb.dump | pg_restore -d appdb

restic dump to stdout piped into pg_restore — no need to materialize the dump to disk. For large scale, consider pg_basebackup + WAL archiving; that is its own topic in another series.

#Backing Up MySQL/MariaDB

mysqldump --single-transaction --routines --triggers \
  -u backup -p"$MYSQL_PW" appdb > /var/backup/staging/appdb.sql
restic backup /var/backup/staging --tag db-daily

Restore:

restic dump latest /var/backup/staging/appdb.sql | mysql -u root appdb

#Backing Up Application Files

Besides the database, don't forget application files: code, uploads, config. Back them up together as a regular snapshot:

restic backup /var/www /etc/nginx --tag app-daily

Note the separation: --tag db-daily for dumps, --tag app-daily for files. Tags allow different retention (episode 8) — the database may need a longer history than build files that can be re-downloaded.

#Conclusion

• A database filesystem snapshot without understanding WAL risks a corrupt restore.
• Logical backup (pg_dump/mysqldump) = verifiable consistency.
• Staging dir pattern: dump → staging → restic backup of the staging — not the DB data files.
• --single-transaction makes MySQL dumps consistent without locking.
• Restore via pipe: restic dump ... | pg_restore/mysql.
• Separate DB vs application tags so retention can be managed independently.

In the next episode, episode 11, we test trust: check & integrity verification — restic check for repo consistency, --read-data to verify every blob, periodic scheduling, auditing with restic cat config, and a restore drill to another host.