Backup & Recovery — Production Strategy

The 3-2-1 Rule

Every production backup strategy must satisfy:

• 3 copies of data (1 primary + 2 backups)
• 2 different storage media or locations (e.g., local disk + cloud)
• 1 offsite copy (different geographic region from primary)

Recommended schedule: Full weekly + Incremental daily with 30-day retention.

Database Backups

PostgreSQL

# Plain SQL dump (human-readable, compatible with any Postgres version)
pg_dump -U postgres -d mydb > mydb_$(date +%Y%m%d_%H%M%S).sql

# Custom format (-Fc) — compressed, supports parallel restore, preferred for large DBs
pg_dump -U postgres -Fc -d mydb -f mydb_$(date +%Y%m%d_%H%M%S).dump

# Directory format (-Fd) — parallel dump, one file per table
pg_dump -U postgres -Fd -j 4 -d mydb -f mydb_$(date +%Y%m%d_%H%M%S).dir/

# Dump globals (roles, tablespaces) — run separately
pg_dumpall -U postgres --globals-only > globals_$(date +%Y%m%d).sql

# Dump all databases
pg_dumpall -U postgres > all_databases_$(date +%Y%m%d).sql

PostgreSQL restore:

# From plain SQL
psql -U postgres -d mydb < mydb_20241201_030000.sql

# From custom format (parallel restore with -j)
pg_restore -U postgres -d mydb -j 4 --clean --if-exists mydb_20241201_030000.dump

# Create DB first if it doesn't exist
createdb -U postgres mydb
pg_restore -U postgres -d mydb mydb_20241201_030000.dump

# Restore globals first
psql -U postgres < globals_20241201.sql

MongoDB

# Full dump (BSON format)
mongodump --uri="mongodb://user:pass@localhost:27017/mydb" \
  --out=/var/backups/mongo/$(date +%Y%m%d_%H%M%S)/

# Compressed archive
mongodump --uri="mongodb://user:pass@localhost:27017/mydb" \
  --archive=/var/backups/mongo/mydb_$(date +%Y%m%d).gz \
  --gzip

# Restore
mongorestore --uri="mongodb://user:pass@localhost:27017/mydb" \
  --drop \
  --archive=/var/backups/mongo/mydb_20241201.gz \
  --gzip

MySQL / MariaDB

# Single database
mysqldump -u root -p mydb \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  > mydb_$(date +%Y%m%d_%H%M%S).sql

# All databases
mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  > all_databases_$(date +%Y%m%d).sql

# Restore
mysql -u root -p mydb < mydb_20241201_030000.sql

--single-transaction acquires a consistent snapshot for InnoDB without locking tables.

File Backups with rsync

# Basic archive sync (preserves permissions, timestamps, symlinks)
rsync -av /var/www/html/ /mnt/backup/html/

# With deletion (mirror: remove files deleted from source)
rsync -av --delete /var/www/html/ /mnt/backup/html/

# Exclude paths
rsync -av --delete \
  --exclude='*.log' \
  --exclude='node_modules/' \
  --exclude='.cache/' \
  /var/www/ /mnt/backup/www/

# Incremental backup using --link-dest (hardlinks unchanged files — space efficient)
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
LATEST=/mnt/backup/latest
rsync -av --delete \
  --link-dest="$LATEST" \
  /var/www/html/ \
  /mnt/backup/snapshots/$BACKUP_DATE/
# Update the "latest" symlink
ln -sfn /mnt/backup/snapshots/$BACKUP_DATE /mnt/backup/latest

# Remote sync over SSH (bandwidth-limited to 10 MB/s)
rsync -av --delete \
  --bwlimit=10000 \
  --partial \
  -e "ssh -i /root/.ssh/backup_key -p 22" \
  /var/www/html/ \
  backup-user@backup-server.example.com:/backups/html/

rclone for Cloud Storage

Installation and Configuration

curl https://rclone.org/install.sh | sudo bash

# Interactive config
rclone config
# Creates ~/.config/rclone/rclone.conf

~/.config/rclone/rclone.conf:

[b2]
type = b2
account = your-b2-account-id
key = your-b2-application-key

[s3]
type = s3
provider = AWS
access_key_id = AKIAIOSFODNN7EXAMPLE
secret_access_key = wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
region = us-east-1

[sftp-backup]
type = sftp
host = backup-server.example.com
user = backup
key_file = /root/.ssh/backup_key
port = 22

rclone Commands

# Copy (source → dest, skip existing identical files)
rclone copy /var/backups/ b2:my-bucket/server-backups/ --progress

# Sync (make destination identical to source — DELETES files not in source)
rclone sync /var/backups/ b2:my-bucket/server-backups/ \
  --progress \
  --bwlimit 50M

# Copy with bandwidth limit and filtering
rclone copy /var/backups/ s3:my-bucket/backups/ \
  --bwlimit 20M \
  --include "*.gpg" \
  --progress

# List remote contents
rclone ls b2:my-bucket/server-backups/

# Check (verify checksums between source and destination)
rclone check /var/backups/ b2:my-bucket/server-backups/

Encryption with GPG

Never upload unencrypted backups containing sensitive data to cloud storage.

# Symmetric encryption (single passphrase) — simpler, good for personal use
gpg --symmetric \
    --cipher-algo AES256 \
    --compress-algo none \
    mydb_backup.dump
# Produces: mydb_backup.dump.gpg

# Asymmetric encryption (public key) — better for automation (no passphrase at encrypt time)
# Generate key pair once: gpg --full-gen-key
gpg --encrypt \
    --recipient backup-key@example.com \
    mydb_backup.dump

# Decrypt symmetric
gpg --decrypt mydb_backup.dump.gpg > mydb_backup.dump

# Decrypt asymmetric (requires private key)
gpg --decrypt mydb_backup.dump.gpg > mydb_backup.dump

# Pipe backup directly through encryption (no plaintext file on disk)
pg_dump -U postgres -Fc mydb | \
  gpg --symmetric --cipher-algo AES256 --compress-algo none \
  > /var/backups/mydb_$(date +%Y%m%d).dump.gpg

Complete Production Backup Script

/opt/scripts/backup-postgres.sh:

#!/bin/bash
# Production PostgreSQL backup: dump → encrypt → upload to S3 → cleanup → ping
set -euo pipefail

# ── Configuration ────────────────────────────────────────────────────────────
DB_NAME="mydb"
DB_USER="postgres"
BACKUP_DIR="/var/backups/postgres"
RCLONE_DEST="s3:my-backup-bucket/postgres"
GPG_PASSPHRASE_FILE="/etc/backup/gpg-passphrase"   # chmod 600, owned by backup user
HEALTHCHECK_URL="https://hc-ping.com/YOUR-CHECK-UUID"
RETAIN_LOCAL_DAYS=7
RETAIN_REMOTE_DAYS=30
LOG_FILE="/var/log/backup-postgres.log"

# ── Helpers ───────────────────────────────────────────────────────────────────
log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG_FILE"; }

cleanup() {
  local exit_code=$?
  if [[ $exit_code -ne 0 ]]; then
    log "BACKUP FAILED (exit $exit_code) — sending failure ping"
    curl -fsS --retry 3 "${HEALTHCHECK_URL}/fail" > /dev/null || true
  fi
}
trap cleanup EXIT

# ── Ping start ────────────────────────────────────────────────────────────────
curl -fsS --retry 3 "${HEALTHCHECK_URL}/start" > /dev/null || true

# ── Backup ───────────────────────────────────────────────────────────────────
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
FILENAME="${DB_NAME}_${TIMESTAMP}.dump.gpg"
BACKUP_PATH="${BACKUP_DIR}/${FILENAME}"

mkdir -p "$BACKUP_DIR"
log "Starting backup of database: $DB_NAME"

pg_dump -U "$DB_USER" -Fc "$DB_NAME" | \
  gpg --batch --yes \
      --passphrase-file "$GPG_PASSPHRASE_FILE" \
      --symmetric \
      --cipher-algo AES256 \
      --compress-algo none \
  > "$BACKUP_PATH"

BACKUP_SIZE=$(du -sh "$BACKUP_PATH" | cut -f1)
log "Backup complete: $FILENAME ($BACKUP_SIZE)"

# ── Upload to S3 ─────────────────────────────────────────────────────────────
log "Uploading to $RCLONE_DEST"
rclone copy "$BACKUP_PATH" "$RCLONE_DEST/" \
  --progress \
  --log-file="$LOG_FILE" \
  --log-level INFO

log "Upload complete"

# ── Local retention cleanup ───────────────────────────────────────────────────
log "Removing local backups older than ${RETAIN_LOCAL_DAYS} days"
find "$BACKUP_DIR" -name "*.dump.gpg" -mtime +"$RETAIN_LOCAL_DAYS" -delete

# ── Remote retention cleanup ──────────────────────────────────────────────────
log "Removing remote backups older than ${RETAIN_REMOTE_DAYS} days"
rclone delete "$RCLONE_DEST" \
  --min-age "${RETAIN_REMOTE_DAYS}d" \
  --include "*.dump.gpg"

# ── Success ping ──────────────────────────────────────────────────────────────
curl -fsS --retry 3 "$HEALTHCHECK_URL" > /dev/null || true
log "Backup job finished successfully"

Crontab entry:

0 2 * * * backup-user /opt/scripts/backup-postgres.sh >> /var/log/backup-cron.log 2>&1

Retention and Rotation Schedule

Shell snippet for rotation logic:

# Keep only last N files matching pattern (sorted by modification time)
keep_last_n() {
  local dir="$1" pattern="$2" keep="$3"
  ls -t "$dir"/$pattern 2>/dev/null | tail -n +$((keep + 1)) | xargs -r rm --
}
keep_last_n /var/backups/postgres "*.dump.gpg" 7

AWS IAM Policy for Backup User

Minimal S3 permissions — no ListAllMyBuckets, no delete on critical prefixes:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:PutObject", "s3:GetObject", "s3:DeleteObject"],
      "Resource": "arn:aws:s3:::my-backup-bucket/postgres/*"
    },
    {
      "Effect": "Allow",
      "Action": ["s3:ListBucket"],
      "Resource": "arn:aws:s3:::my-backup-bucket",
      "Condition": {
        "StringLike": {"s3:prefix": ["postgres/*"]}
      }
    }
  ]
}

Disaster Recovery Concepts

RTO (Recovery Time Objective): Maximum acceptable downtime after a disaster. Drives decisions about hot/warm/cold standby and restore automation.

RPO (Recovery Point Objective): Maximum acceptable data loss measured in time. Drives backup frequency.

Restore Runbook Template

# DISASTER RECOVERY RUNBOOK — [Service Name]
# Last tested: [DATE]  Tested by: [NAME]

## Step 1: Provision New Server
- [ ] Provision server (size: ____, region: ____)
- [ ] Apply Terraform/Ansible baseline configuration
- [ ] Verify SSH access and firewall rules

## Step 2: Install Dependencies
- [ ] sudo apt install postgresql-16 (or appropriate version)
- [ ] Install application dependencies from runbook in DEPLOY.md

## Step 3: Download Latest Backup
  rclone copy s3:my-backup-bucket/postgres/ /var/restore/ --include "*.dump.gpg"
  # Identify most recent file
  ls -lt /var/restore/ | head -5

## Step 4: Decrypt
  gpg --batch --passphrase-file /etc/backup/gpg-passphrase \
      --decrypt /var/restore/mydb_YYYYMMDD_HHMMSS.dump.gpg \
      > /var/restore/mydb.dump

## Step 5: Restore Database
  createdb -U postgres mydb
  pg_restore -U postgres -d mydb -j 4 --clean --if-exists /var/restore/mydb.dump

## Step 6: Verify Data
  psql -U postgres -d mydb -c "SELECT COUNT(*) FROM users;"
  psql -U postgres -d mydb -c "SELECT MAX(created_at) FROM orders;"

## Step 7: Start Application
  systemctl start myapp
  curl -f http://localhost:3000/health

## Step 8: Update DNS / Load Balancer
  - [ ] Point DNS A record to new server IP
  - [ ] Update load balancer target group
  - [ ] Verify external access

## Step 9: Post-Recovery
  - [ ] Set up monitoring on new server
  - [ ] Schedule first backup of new server
  - [ ] Write incident report
  - [ ] Update runbook if steps were incorrect

Anti-Patterns

Troubleshooting