Database Backup Strategies: Full, Incremental, PITR and the 3-2-1 Rule

Data without a backup is data that is about to disappear. Disk failures, ransomware and human error all strike sooner or later. This article covers the components of a production-grade backup strategy and how to implement it with both PostgreSQL and MySQL.

The 3-2-1 Rule

• 3 copies (1 primary + 2 backups)
• 2 different media (disk + cloud)
• 1 offsite (physically separated)

Backup Types

• Full backup: a complete copy of the database. Weekly is a good cadence
• Incremental: changes since the last full/incremental. Daily
• Differential: changes since the last full (grows over time)
• WAL / binlog: a per-transaction log — required for PITR

PostgreSQL Backups

Logical Backup (pg_dump)

# Single DB
pg_dump -h localhost -U postgres -Fc -f /backup/mydb-$(date +%F).dump mydb

# -Fc = custom format (compressed, flexible pg_restore)
# -Fd = directory format (for parallel -j)
# -Fp = plain SQL (bad for big DBs)

# Parallel (for large DBs)
pg_dump -h localhost -U postgres -Fd -j 4 -f /backup/mydb-dir mydb

# Entire cluster
pg_dumpall -h localhost -U postgres -f /backup/cluster-$(date +%F).sql

# Restore
dropdb mydb && createdb mydb
pg_restore -d mydb /backup/mydb-2026-04-17.dump

Physical Backup (pg_basebackup) + WAL -> PITR

# Enable archive mode (postgresql.conf)
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'
archive_timeout = 300

# Take the base backup
pg_basebackup -h localhost -U replicator -D /backup/base -Ft -z -P

# PITR: roll back one hour
# 1) stop the service
systemctl stop postgresql
# 2) wipe the data directory, extract the base backup
tar xzf /backup/base/base.tar.gz -C /var/lib/postgresql/data
# 3) create recovery.conf
cat > /var/lib/postgresql/data/recovery.signal
cat > /var/lib/postgresql/data/postgresql.auto.conf <<EOF
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2026-04-17 13:45:00'
EOF
# 4) start
systemctl start postgresql

MySQL Backups

# mysqldump — small to medium DBs
mysqldump -u root -p --single-transaction --triggers --routines --events \
    --all-databases | gzip > /backup/all-$(date +%F).sql.gz

# xtrabackup — physical hot backup for large DBs
xtrabackup --backup --target-dir=/backup/base --user=root --password=x
xtrabackup --prepare --target-dir=/backup/base

# Binlog must be enabled for PITR
# my.cnf
server-id = 1
log_bin = /var/lib/mysql/binlog
binlog_format = ROW

# Point-in-time restore:
mysqlbinlog --start-datetime='2026-04-17 12:00:00' \
            --stop-datetime='2026-04-17 13:45:00' \
            /var/lib/mysql/binlog.000042 | mysql -u root -p

Automated Backup Script

#!/bin/bash
# /opt/scripts/pg-backup.sh
set -euo pipefail

DB="mydb"
BACKUP_DIR="/backup/postgres"
RETAIN_DAYS=14
S3_BUCKET="s3://my-backups/postgres"
TS=$(date +%F-%H%M)
FILE="${BACKUP_DIR}/${DB}-${TS}.dump"

mkdir -p "$BACKUP_DIR"

# 1) Dump
pg_dump -Fc -f "$FILE" "$DB"

# 2) Checksum
sha256sum "$FILE" > "${FILE}.sha256"

# 3) Upload to S3 (offsite)
aws s3 cp "$FILE" "$S3_BUCKET/"
aws s3 cp "${FILE}.sha256" "$S3_BUCKET/"

# 4) Remove old local files
find "$BACKUP_DIR" -name "${DB}-*.dump" -mtime +$RETAIN_DAYS -delete

# 5) Webhook/Slack notification
curl -sS -X POST https://hooks.slack.com/... \
    -d "{\"text\":\"Backup done: $FILE ($(du -h $FILE | cut -f1))\"}"

# Cron
0 2 * * * /opt/scripts/pg-backup.sh >> /var/log/pg-backup.log 2>&1

Restore Testing

An untested backup is not a backup. At least once a month, restore to a different server and verify the data works. Discovering a corrupt dump mid-disaster can end a company.

Encryption and Retention

• Encrypt backup files with AES-256 (gpg --symmetric --cipher-algo AES256)
• Enable SSE-KMS on the S3 bucket
• Retention policy: 14 daily, 12 weekly, 12 monthly
• GDPR and similar laws require specific retention periods when backups hold personal data

Monitoring

# Nagios/Grafana metric for last-backup age
LAST=$(ls -t /backup/postgres/*.dump | head -1 | xargs stat -c %Y)
AGE=$(( ($(date +%s) - $LAST) / 3600 ))
echo "pg_backup_age_hours $AGE"
# Alert if it exceeds 25 hours

Conclusion

A proper backup strategy starts with three questions: How much data loss can I tolerate? (RPO), How quickly must I recover? (RTO) and Are the backups stored safely?. If you cannot answer those crisply, you still do not have a strategy.