Timevolt

Posted on Aug 3

Back to the Future: Mastering Database Backups and Disaster Recovery

#devops #docker #kubernetes #cicd

The Quest Begins (The "Why")

Picture this: it’s 2 a.m., the pager is screaming, and the production database has just decided to take an unscheduled nap. I was staring at a blank terminal, heart pounding, wondering if I’d ever see those precious user records again. That night I learned the hard way that “we’ll just copy the files” isn’t a strategy—it’s a wish. The dragon I needed to slay wasn’t a bug in the code; it was the terrifying possibility of losing data forever. If you’ve ever felt that gut‑wrenching panic when a backup fails, you know exactly what I’m talking about.

So I embarked on a quest: find a backup and disaster‑recovery plan that actually works, is repeatable, and lets me sleep through the night. Spoiler: the treasure wasn’t a magical artifact—it was a set of practices, scripts, and a mindset that turns chaos into confidence.

The Revelation (The Insight)

The big “aha!” moment came when I stopped thinking of backups as a once‑a‑day cron job and started seeing them as a continuous safety net. The insight? Combine logical backups (point‑in‑time dumps) with physical snapshots and a solid retention policy, then test restores religiously.

Why does this combo work?

Logical dumps (pg_dump, mysqldump) give you portable, human‑readable files you can load onto any identical version.
Physical snapshots (filesystem LVM, ZFS, or cloud provider snapshots) capture the exact byte‑for‑byte state of the data files, making recovery blisteringly fast.
Keeping both gives you flexibility: need to roll back a single table? Pull a logical dump. Need the whole cluster back in under five minutes? Spin up a snapshot.

And the secret sauce? Automated verification. Every backup triggers a restore to a temporary instance and runs a quick sanity check (row count, checksum). If the check fails, the alarm goes off before you even notice the problem.

Wielding the Power (Code & Examples)

Let’s look at a concrete example for a PostgreSQL cluster running on a Linux host. I’ll show the struggle (a naive cron job) and then the victory (the battle‑tested script).

The Struggle – A Naïve Cron Job

# /etc/cron.daily/db_backup (the old way)
#!/bin/bash
DUMP_FILE="/var/backups/db_$(date +%F).sql"
pg_dump -U postgres -Fc mydb > "$DUMP_FILE"
gzip "$DUMP_FILE"

Traps:

No retention policy → disk fills up.
No verification → you might be backing up a corrupt database.
No snapshot → restoring a large DB takes ages.

I ran this for months, only to discover that the dump file was empty because the pg_dump user lacked permission. The alarm? Silence. The disaster? Real.

The Victory – A Robust Backup Script

#!/usr/bin/env bash
set -euo pipefail

# ----- CONFIG -----
DB_NAME="mydb"
DB_USER="postgres"
BACKUP_DIR="/var/backups/postgres"
SNAPSHOT_DIR="/var/snapshots/postgres"
RETENTION_DAYS=30
# ------------------

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
LOGICAL_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump"
SNAPSHOT_NAME="${SNAPSHOT_DIR}/${DB_NAME}_${TIMESTAMP}"

# 1️⃣ Take a logical dump (custom format for pg_restore)
echo "[$(date)] Starting logical dump..."
pg_dump -U "$DB_USER" -Fc "$DB_NAME" -f "$LOGICAL_FILE"
gzip "$LOGICAL_FILE"
echo "[$(date)] Logical dump completed: ${LOGICAL_FILE}.gz"

# 2️⃣ Create a physical snapshot using LVM (adjust for your storage)
echo "[$(date)] Creating LVM snapshot..."
lvcreate -L 10G -s -n "${DB_NAME}_snap_${TIMESTAMP}" /dev/vg0/${DB_NAME}
# Mount snapshot, rsync data, then remove snapshot (simplified)
SNAP_LV="/dev/vg0/${DB_NAME}_snap_${TIMESTAMP}"
mkdir -p "$SNAPSHOT_NAME"
mount "$SNAP_LV" "$SNAPSHOT_NAME"
rsync -a --delete "$SNAPSHOT_NAME/" "${SNAPSHOT_NAME}_copy/"
umount "$SNAP_LV"
lvremove -f "$SNAP_LV"
echo "[$(date)] Snapshot stored at ${SNAPSHOT_NAME}_copy"

# 3️⃣ Retention cleanup
echo "[$(date)] Pruning old backups (>${RETENTION_DAYS} days)..."
find "$BACKUP_DIR" -type f -name "*.dump.gz" -mtime +$RETENTION_DAYS -delete
find "$SNAPSHOT_DIR" -type d -mtime +$RETENTION_DAYS -exec rm -rf {} +

# 4️⃣ Verification – restore to a temp DB and run a quick check
TEMP_DB="${DB_NAME}_verify_$(date +%s)"
echo "[$(date)] Verifying backup by restoring to $TEMP_DB..."
createdb -U "$DB_USER" "$TEMP_DB"
gunzip -c "${LOGICAL_FILE}.gz" | pg_restore -U "$DB_USER" -d "$TEMP_DB"
# Simple sanity check: count rows in a known table
ROW_COUNT=$(psql -U "$DB_USER" -d "$TEMP_DB" -t -c "SELECT COUNT(*) FROM users;" | xargs)
if [[ "$ROW_COUNT" -eq 0 ]]; then
  echo "[$(date)] ❌ Verification failed: row count is zero!"
  exit 1
else
  echo "[$(date)] ✅ Verification passed: $ROW_COUNT rows in users table."
fi

# Clean up temp DB
dropdb -U "$DB_USER" "$TEMP_DB"
echo "[$(date)] Backup cycle complete."

What changed?

Retention automatically prunes old files, preventing disk‑fill surprises.
Dual backup: logical dump for flexibility, LVM snapshot for speed.
Verification catches permission issues, corruption, or empty dumps before you need them.
Logging with timestamps lets you trace any hiccup in /var/log/backup.log (just redirect the script’s output).

Run this via a systemd timer or cron every hour (or whatever your RPO demands). Adjust the LVM part to ZFS snapshots, AWS EBS snapshots, or GCP persistent disk snapshots— the logic stays the same.

Common Mistakes to Avoid (The Traps)

Skipping verification – Assuming a file exists means it’s good. Always test a restore.
Using only one backup type – Relying solely on logical dumps makes large‑scale restores painfully slow; relying solely on snapshots makes it hard to migrate to a new version or replicate elsewhere.
Hard‑coding credentials – Use .pgpass files, IAM roles, or secret managers; never drop passwords in plain scripts.

Why This New Power Matters

Now, when the pager screams at 2 a.m., I glance at the monitoring dashboard, see the latest backup succeeded, and know I can spin up a fresh replica from a snapshot in minutes. If a developer accidentally drops a table, I pull the corresponding logical dump, restore just that table, and the service is back before the coffee finishes brewing.

The peace of mind isn’t just about avoiding data loss—it’s about velocity. Teams can experiment, migrate, and upgrade without the fear of irreversible mistakes. And the best part? The scripts are version‑controlled, reviewed, and improve over time, just like any other piece of code.

Your Turn – The Next Quest

Ready to level up your backup game? Grab the script above, adapt it to your stack (MySQL, MongoDB, cloud‑native services), and run a fire drill: simulate a failure, restore from both backup types, and time how long it takes.

Challenge: After your first successful test, tweet or write a short note about what you learned—and tag a friend who still swears by “just copying the files.” Let’s spread the confidence, one backup at a time.

Happy safeguarding, and may your databases always be ready to roll back to the future! 🚀

DEV Community