Setting Up Incremental Database Backups
Full database dumps every night are expensive in disk space and time, especially when the database occupies tens of gigabytes. Incremental backups save only changes since the last backup.
Types of incremental backups
Differential — saves all changes since the last full backup. Restore: full + one differential.
Incremental — saves only changes since the last any backup. Restore: full + chain of incrementals.
WAL-based (PostgreSQL) — continuous transaction log archiving, foundation for PITR.
PostgreSQL: WAL archiving via pgBackRest
# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
log-level-console=info
[myapp]
pg1-path=/var/lib/postgresql/14/main
Initialization and first full backup:
pgbackrest --stanza=myapp stanza-create
pgbackrest --stanza=myapp --type=full backup
Subsequent incremental backups:
# Differential (once a week)
pgbackrest --stanza=myapp --type=diff backup
# Incremental (daily)
pgbackrest --stanza=myapp --type=incr backup
Cron schedule:
# Full backup once a week (Sunday 01:00)
0 1 * * 0 pgbackrest --stanza=myapp --type=full backup
# Differential (Mon-Sat 01:00)
0 1 * * 1-6 pgbackrest --stanza=myapp --type=diff backup
MySQL: Percona XtraBackup
The only tool supporting true incremental MySQL backups without locks:
# Full backup
xtrabackup --backup --target-dir=/var/backups/mysql/full/
# First incremental
xtrabackup --backup \
--target-dir=/var/backups/mysql/incr1/ \
--incremental-basedir=/var/backups/mysql/full/
# Second incremental (based on incr1)
xtrabackup --backup \
--target-dir=/var/backups/mysql/incr2/ \
--incremental-basedir=/var/backups/mysql/incr1/
Restore:
# Prepare full backup
xtrabackup --prepare --apply-log-only --target-dir=/var/backups/mysql/full/
# Apply incrementals
xtrabackup --prepare --apply-log-only \
--target-dir=/var/backups/mysql/full/ \
--incremental-dir=/var/backups/mysql/incr1/
xtrabackup --prepare \
--target-dir=/var/backups/mysql/full/ \
--incremental-dir=/var/backups/mysql/incr2/
Cloud storage and deduplication
Tools with built-in deduplication and compression significantly reduce storage volume:
- Restic — encrypted incremental backups to S3/GCS/B2/SSH
- Borg Backup — block-level deduplication
- Duplicati — with web interface
# Restic: initialize repository
restic -r s3:s3.amazonaws.com/my-bucket/db-backups init
# Backup directory with dumps
restic -r s3:s3.amazonaws.com/my-bucket/db-backups \
backup /var/backups/postgres/ \
--password-file /etc/restic-password
Monitoring and alerting
Metrics to monitor:
- Size of last backup (sharp decrease — problem signal)
- Backup execution time
- Successful healthcheck ping
# End of script — check and healthcheck
if pgbackrest --stanza=myapp check; then
curl -s "https://hc-ping.com/${HC_UUID}"
else
curl -s "https://hc-ping.com/${HC_UUID}/fail"
fi
Timeline
Setup of pgBackRest or XtraBackup with incremental strategy — 1–2 business days.