backup-recovery

What this skill does

# 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)

| Backup Type | Description | Use Case |
|------------|-------------|----------|
| Full | Complete snapshot of all data | Weekly baseline, before major upgrades |
| Incremental | Changes since last backup (any type) | Daily; fast but requires chain for restore |
| Differential | Changes since last full backup | Balances restore speed vs. storage cost |

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

---

## Database Backups

### PostgreSQL

```bash
# 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:**
```bash
# 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

```bash
# 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

```bash
# 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

```bash
# 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/ \
  [email protected]:/backups/html/
```

---

## rclone for Cloud Storage

### Installation and Configuration

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

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

`~/.config/rclone/rclone.conf`:
```ini
[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

```bash
# 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.

```bash
# 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 [email protected] \
    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`:
```bash
#!/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"
fin