Learn Borg Backup - Consistent Database Backup

#Introduction

In episode 9 we automated backups with borgmatic. Now the trickiest challenge: databases. Copying raw database data files is like photographing a book while it is being read — half of the text has moved, and the consistency is broken. A database backup must be made through a mechanism that guarantees consistency: a dump or a snapshot that accounts for the WAL. This episode teaches the correct pattern for PostgreSQL, MySQL/MariaDB, and file-based services.

#Why Database Backups Are Different

#The Problem of Files Being Written

Databases write pages of data to disk continuously. If a backup copies database files while a transaction is in progress, the result can be a mix of mutually inconsistent page versions — a restore yields a corrupt database.

#The Role of WAL and Dumps

• WAL (Write-Ahead Log): the PostgreSQL database records every change to the WAL before writing the actual data. A filesystem snapshot + WAL can give point-in-time recovery — consistent, but complex.
• Logical dump (pg_dump/mysqldump): reads the database through the database API with a read transaction — producing a text/SQL file that is consistent at one point in time. This is the simplest and most common way.

The practical recommendation for most workloads: dump first, then back up the dump file. Borg does not need to know it is a database — it simply stores the consistent dump file.

#The Hook Pattern: Dump Before, Clean Up After

#with borgmatic

Step 1 — make sure the dump directory is in source_directories:

source_directories:
  - /home
  - /etc
  - /var/backups/db

Step 2 — define the hooks:

hooks:
  before_backup:
    - pg_dump -U backup_user appdb | gzip > /var/backups/db/appdb.sql.gz
    - mysqldump --all-databases --single-transaction | gzip > /var/backups/db/mysql.sql.gz
  after_backup:
    - rm -f /var/backups/db/appdb.sql.gz /var/backups/db/mysql.sql.gz
  on_error:
    - echo "Database backup FAILED" >&2

The flow: the dump is created → borgmatic backs up the /var/backups/db directory → the after_backup hook cleans up the dumps so they do not pile up.

#Notes per Database Engine

• PostgreSQL: pg_dump -U backup_user appdb — consistent per-database. For the whole cluster, pg_dumpall. Make sure the backup_user has read rights (SELECT).
• MySQL/MariaDB: mysqldump --all-databases --single-transaction — --single-transaction gives a consistent snapshot for InnoDB without locking all tables.
• Commands from borgmatic: make sure pg_dump/mysqldump are on the PATH of the user running borgmatic (root is usually fine).

#with plain borg (without borgmatic)

Without borgmatic, the same pattern is achieved with a shell script + cron:

#!/usr/bin/env bash
set -euo pipefail
 
pg_dump -U backup_user appdb | gzip > /var/backups/db/appdb.sql.gz
mysqldump --all-databases --single-transaction | gzip > /var/backups/db/mysql.sql.gz
 
export BORG_REPO=/backup/borg
export BORG_PASSCOMMAND='cat /root/.borg-passphrase'
borg create --stats /backup/borg::"{hostname}-{now}" /home /etc /var/backups/db
borg prune --keep-daily 7 --keep-weekly 4 --keep-monthly 6
borg compact
 
rm -f /var/backups/db/appdb.sql.gz /var/backups/db/mysql.sql.gz

#Native Database Support in borgmatic

Borgmatic also has built-in database support — it manages the dump and restore itself without manual hooks:

postgresql_databases:
  - name: appdb
    hostname: localhost
    username: backup_user
 
mysql_databases:
  - name: all

With this configuration, borgmatic creates the database dump to a temporary location, backs it up, then cleans it up — plus the borgmatic restore --database capability for targeted restores. To begin with, the manual hook pattern is more explicit; the native support is more concise and documented.

#The Other Case: File-Based Services

For services whose data is files (Nextcloud, GitLab, PHP applications) there are different nuances:

• Make sure transactions stop: for Nextcloud, turn on maintenance mode or use occ maintenance:mode --on during the backup.
• Use filesystem snapshots (LVM/ZFS) where possible: a snapshot gives a consistent point in time, then back up that snapshot.
• Data files vs DB metadata: many applications separate user files and database. Back both up in close windows, or use built-in features (e.g. GitLab's packaged backup) that keep the two in sync.

#Common Pitfalls

• Backing up raw database files: almost certainly corrupt. Always dump or snapshot.
• Dumps in a directory that is not backed up: the dump is made but not included in source_directories — pointless.
• Dumps never cleaned up: dumps accumulate and duplicate the same data every day — remove them with after_backup.
• Ignoring users and privileges: run dumps with a dedicated user, not the DB root.
• Forgetting the database restore test: restore a dump into an empty instance every month to confirm the dump is still valid (and its format is still supported by the DB version in use).

#Closing

• Databases must be backed up via dump/snapshot, not raw file copies.
• The hook pattern: before_backup creates the dump, after_backup cleans up.
• pg_dump for PostgreSQL; mysqldump --single-transaction for MySQL/MariaDB.
• borgmatic has native postgresql_databases and mysql_databases support.
• File-based services need maintenance mode or a snapshot to stay consistent.
• Always test dump restores regularly.

In episode 11 we take the backup off the server: Borg on a Remote Server (SSH) — a repository on another host via borg serve, SSH key setup with a restricted command, and security practices such as a dedicated passwordless user and Borg's built-in seccomp.