Automated backups (pgBackRest/Barman/Xtrabackup/cron)

Symptom

Goal: Automate database backups; build a reliable recovery chain with scheduled full+incremental+WAL/binlog archiving, retention, verification and an offsite copy.

1. Choose the right backup tool

Choose a tool for your engine: pgBackRest or Barman for PostgreSQL (physical backup + WAL archive + PITR); Percona XtraBackup (online physical backup) for MySQL/MariaDB, or mysqldump/mydumper (logical) for small datasets. Physical tools give fast, consistent backups + point-in-time recovery on large databases; logical dumps are portable but slow on large data.

PostgreSQL

# pgBackRest or Barman

MySQL

# physical: xtrabackup
# logical: mysqldump / mydumper

2. Build the full + incremental + continuous archive chain

Set up point-in-time recovery with weekly/daily full backups, incremental/differential backups in between, and continuous WAL (PostgreSQL) or binlog (MySQL) archiving. pgBackRest manages full/diff/incremental backup types and the WAL archive; in PostgreSQL you point archive_command at pgBackRest. Incremental backups + archive let you return to any point in time.

PostgreSQL

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=appdb archive-push %p'

# full backup
pgbackrest --stanza=appdb --type=full backup
# incremental
pgbackrest --stanza=appdb --type=incr backup

MySQL

# full
xtrabackup --backup --target-dir=/backup/full
# incremental
xtrabackup --backup --target-dir=/backup/incr1 \
  --incremental-basedir=/backup/full

3. Schedule backups with cron/timer

Run backup commands regularly with cron or a systemd timer: for example a full backup Sunday night and incrementals on other nights. Schedule outside business hours and make sure full and incr jobs do not overlap. Write output logs somewhere and raise an alert on failure.

PostgreSQL

# crontab
0 1 * * 0 pgbackrest --stanza=appdb --type=full backup
0 1 * * 1-6 pgbackrest --stanza=appdb --type=incr backup

4. Define a retention policy

Do not accumulate backups forever; set how many full backups and how many days of archives to keep, and let the tool expire old ones automatically. In pgBackRest this is managed with repo1-retention-full and the archive retention settings; in Barman with retention_policy. Retention must match your recovery objectives (how many days back you need to go).

PostgreSQL

# pgbackrest.conf
[global]
repo1-retention-full=4
repo1-retention-archive=8

MySQL

# purge old backups with cron (example)
find /backup -type d -mtime +14 -exec rm -rf {} +

5. Copy backups offsite / to S3

Backups must not stay on the same machine/region as the database; copy them to another location or object storage (S3, GCS). pgBackRest can write directly to S3-compatible storage; otherwise sync to an offsite target with rsync/rclone. In a disaster (loss of local disk/region) only the offsite copy will save you.

PostgreSQL

# pgbackrest.conf S3 repository
[global]
repo1-type=s3
repo1-s3-bucket=company-backup
repo1-s3-region=eu-central-1
repo1-s3-endpoint=s3.eu-central-1.amazonaws.com

6. Verify the backup

A backup existing does not mean it can be restored; verify integrity regularly. In pgBackRest the 'verify' command and archive consistency checks, and in XtraBackup a successful prepare (--prepare) step, show the backup is sound. Run verification automatically after every backup and raise an alarm on failure.

PostgreSQL

pgbackrest --stanza=appdb verify

MySQL

# make the backup consistent (verification)
xtrabackup --prepare --target-dir=/backup/full

7. Run regular restore drills

The most critical step: periodically ACTUALLY restore the backup to a separate server and verify that the database opens and the data is consistent. This drill lets you measure your recovery time (RTO) and catch gaps in the procedure outside of an incident. An untested backup is not a backup.

PostgreSQL

# restore + PITR on a separate server
pgbackrest --stanza=appdb \
  --type=time --target="2026-07-01 03:00:00" restore

MySQL

# copy the prepared backup back to the data directory
xtrabackup --copy-back --target-dir=/backup/full