DEV Community

Timevolt
Timevolt

Posted on

The Return of the Jedi: Leveling Up Your Database Backup & Disaster Recovery Game

#devops #docker #kubernetes #cicd

The Quest Begins (The "Why")

Honestly, I used to think backups were that boring checkbox you tick at the end of a sprint. “We’ll just run pg_dump every night and call it a day.” I’d set up a cron job, forget about it, and move on to the next shiny feature. Then came the day our staging database decided to take an unscheduled vacation—right in the middle of a demo for a big client. The screen froze, the logs screamed “connection refused”, and I felt like I’d just walked into a trap room with no exit.

After a frantic hour of trying to piece together remnants from scattered logs, I realized we had no recent, usable backup. The data loss wasn’t catastrophic, but the embarrassment was. That moment was my dragon: the fear of waking up to a silent database and having no way to bring it back to life. I swore I’d never let that happen again.

The Revelation (The Insight)

The real treasure wasn’t just “take a backup”. It was understanding that a backup strategy is a living contract between you and your data. Three pillars turned the quest from a chore into a superpower:

  1. Automation that’s visible – a schedule you can monitor, not a hidden cron that silently fails.
  2. Verification – you must test the restore, otherwise you’re just hoarding digital dust.
  3. Geographic separation – keeping copies off‑site (or at least off‑the‑same‑hardware) protects you from fire, flood, or a rogue DROP TABLE.

When I stopped seeing backups as a “set‑and‑forget” task and started treating them like a safety net you routinely inspect, everything changed. The peace of mind was instant, and the confidence to push risky migrations skyrocketed.

Wielding the Power (Code & Examples)

The Struggle – Manual, Unverified Dump 

# old-school manual dump, run whenever you remember
pg_dump -U myuser -h prod-db.mycompany.com mydb > /var/backups/mydb_$(date +%F).sql
Enter fullscreen mode Exit fullscreen mode

Problems:

  • No alert if pg_dump fails (disk full, auth issue).
  • File sits on the same server; if the server dies, the backup dies with it.
  • No one ever checks if the file can actually be restored.

The Victory – Automated, Verified, Off‑Site Backup

I moved to a simple but robust pattern using a managed cron (Kubernetes CronJob or cloud scheduler) that:

  1. Dumps the database to a temporary location.
  2. Streams the dump straight to an object store (AWS S3, GCS, Azure Blob).
  3. Triggers a verification step that spins up a temporary instance, restores the dump, runs a quick sanity check, and reports success/failure via Slack.

Here’s the core script (bash‑ish, works on any Linux host with awscli and pg_dump):

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

DB_NAME="mydb"
DB_USER="myuser"
DB_HOST="prod-db.mycompany.com"
S3_BUCKET="s3://mycompany-db-backups"
TIMESTAMP=$(date +%Y%m%d%H%M%S)
DUMP_FILE="/tmp/${DB_NAME}_${TIMESTAMP}.sql"
LOG_FILE="/var/log/db_backup_${TIMESTAMP}.log"

# 1️⃣ Take the dump
echo "[$(date)] Starting dump of ${DB_NAME}..." | tee -a "$LOG_FILE"
pg_dump -U "$DB_USER" -h "$DB_HOST" "$DB_NAME" > "$DUMP_FILE" 2>>"$LOG_FILE"
echo "[$(date)] Dump completed. Size: $(du -h "$DUMP_FILE" | cut -f1)" | tee -a "$LOG_FILE"

# 2️⃣ Upload to S3 (with server‑side encryption)
echo "[$(date)] Uploading to ${S3_BUCKET}..." | tee -a "$LOG_FILE"
aws s3 cp "$DUMP_FILE" "${S3_BUCKET}/${DB_NAME}_${TIMESTAMP}.sql" \
    --sse AES256  >>"$LOG_FILE" 2>&1
echo "[$(date)] Upload finished." | tee -a "$LOG_FILE"

# 3️⃣ Verification – spin up a temporary RDS instance (or Docker) and restore
echo "[$(date)] Starting verification restore..." | tee -a "$LOG_FILE"
VERIFY_INSTANCE=$(aws rds create-db-instance \
    --db-instance-id verify-${TIMESTAMP} \
    --db-instance-class db.t4g.micro \
    --engine postgres \
    --allocated-storage 20 \
    --master-username verifyuser \
    --master-password $(openssl rand -base64 12) \
    --no-publicly-accessible \
    --query 'DBInstance.DBInstanceIdentifier' --output text)

# Wait for it to be available
aws rds wait db-instance-available --db-instance-identifier "$VERIFY_INSTANCE"
ENDPOINT=$(aws rds describe-db-instances \
    --db-instance-identifier "$VERIFY_INSTANCE" \
    --query 'DBInstances[0].Endpoint.Address' --output text)

# Restore the dump
PGPASSWORD=$(aws rds describe-db-instances \
    --db-instance-identifier "$VERIFY_INSTANCE" \
    --query 'DBInstances[0].MasterUserPassword' --output text) \
pg_dump -U verifyuser -h "$ENDPOINT" postgres < "$DUMP_FILE" > /dev/null 2>>"$LOG_FILE"

# Quick sanity check: count rows in a known table
ROW_COUNT=$(PGPASSWORD="$PGPASSWORD" psql -U verifyuser -h "$ENDPOINT" -d postgres -t -c "SELECT COUNT(*) FROM public.users;" 2>>"$LOG_FILE")
echo "[$(date)] Verification row count: $ROW_COUNT" | tee -a "$LOG_FILE"

# Clean up verification instance
aws rds delete-db-instance \
    --db-instance-id "$VERIFY_INSTANCE" \
    --skip-final-snapshot \
    --delete-automated-backups \
    >>"$LOG_FILE" 2>&1

echo "[$(date)] Backup & verification complete." | tee -a "$LOG_FILE"

# Optional: notify Slack if everything looks good
if [[ "$ROW_COUNT" -gt 0 ]]; then
    curl -X POST -H 'Content-type: application/json' \
        --data '{"text":"✅ DB backup verified for '"$DB_NAME"' at '"$(date)"'"}' \
        https://hooks.slack.com/services/XXX/XXX/XXX
else
    curl -X POST -H 'Content-type: application/json' \
        --data '{"text":"❌ DB backup verification FAILED for '"$DB_NAME"'!"}' \
        https://hooks.slack.com/services/XXX/XXX/XXX
fi
Enter fullscreen mode Exit fullscreen mode

Why this feels like leveling up:

  • Visibility: Every step logs to a file and echoes to the console; you can tail the log or set up a CloudWatch alarm on the string “Backup & verification complete”.
  • Off‑site safety: The dump never lingers on the production host; it streams straight to S3 with encryption.
  • Proof of life: The verification step guarantees you can actually restore. If the row count is zero, you know instantly something’s broken—no nasty surprises at 3 a.m.

Traps to Avoid (the “bosses” on our quest)

Trap What it looks like How to dodge it
Storing backups on the same disk pg_dump writes to /var/backups/ on the DB server. Always push to a different zone/account (S3, GCS, or a separate NAS).
Skipping the restore test You have a neat pile of .sql files but never try to load them. Automate a restore step (as above) and alert on failure.
Ignoring encryption Dumps float in plain text in the bucket. Enable server‑side encryption (--sse AES256) or client‑side GPG before upload.
Assuming “nightly” is enough Your app peaks at 2 PM; a nightly dump loses 12 h of work. Combine periodic base backups with continuous WAL archiving or point‑in‑time recovery (PITR) logs.

Why This New Power Matters

Now, when I push a schema migration that tweaks a critical index, I hit Enter with a grin, knowing that if something goes sideways I can roll back to a verified point‑in‑time backup in minutes, not hours. The team trusts the deployment pipeline because the backup step is visible, tested, and independent of the app servers.

Beyond peace of mind, this practice unlocks bold experiments: blue‑green deployments, feature flags that rewrite large tables, even multi‑region migrations. When the data safety net is solid, the engineering ceiling lifts dramatically.

Your Turn – The Challenge

I dare you to take one of your services that currently relies on a manual, unverified dump and implement the three‑step pattern above: automate the dump, ship it to an off‑site store, and add a verification restore that pings Slack (or your favorite chat) with success or failure.

Start small—maybe a staging DB—and watch how the confidence grows. When you see that first “✅ DB backup verified” message drop into your channel, you’ll feel like you’ve just cleared a boss level.

Now go forth, protect your realms, and may your backups always be verifiable! 🚀

Top comments (0)