DEV Community

Timevolt
Timevolt

Posted on

The Backup Awakens: A Database Backup Quest

#devops #cicd #kubernetes #docker

The Quest Begins (The “Why”)

Honestly, I used to think backups were the boring chores you shoved onto a cron job and forgot about—until the day our production PostgreSQL cluster decided to take an unscheduled nap. A rogue script dropped a crucial table, and the only thing we had to show for it was a nightly tarball that was… three days old. The panic was real: customers were seeing errors, the support queue was exploding, and I felt like I was trying to rebuild a Lego Death Star with only half the bricks.

That night, after a frantic restore attempt that left us with a half‑populated schema and a lot of “did we just lose a week of data?” questions, I swore I’d never be caught off‑guard again. I needed a strategy that wasn’t just “copy the files” but something that could give me point‑in-time recovery, verify integrity, and actually work when the dragon showed up.

The Revelation (The Insight)

The treasure I uncovered wasn’t a new tool—it was a mindset shift. Backups aren’t just about having a copy; they’re about knowing you can restore that copy exactly when you need it, down to the second. The real magic happens when you combine three ideas:

  1. Physical base backups (a snapshot of the data directory at a given moment).
  2. Write‑ahead log (WAL) archiving (every change after the base backup, stored safely).
  3. Regular, automated restore tests (because a backup you haven’t tried to restore is just a wish).

When you have a solid base backup plus an unbroken WAL chain, you can replay the logs to any point in time—like rewinding a movie to the exact frame before the villain appears. And if you test that restore weekly, you turn fear into confidence.

Wielding the Power (Code & Examples)

The Struggle: A Naïve Cron‑Job

Here’s what my first attempt looked like—a simple pg_dump dumped to a file and rotated out after a week:

# /etc/cron.daily/pg_backup
#!/bin/bash
DB_NAME="myapp"
DUMP_DIR="/var/backups/postgres"
TIMESTAMP=$(date +%Y%m%d%H%M%S)
pg_dump -Fc $DB_NAME > $DUMP_DIR/${DB_NAME}_$TIMESTAMP.dump
# keep only last 7 days
find $DUMP_DIR -name "${DB_NAME}_*.dump" -mtime +7 -delete
Enter fullscreen mode Exit fullscreen mode

What’s wrong?

  • Logical dumps miss indexes, large objects, and any WAL‑only changes.
  • Restoring means replaying the whole dump—slow and error‑prone.
  • No way to recover to a specific timestamp; you’re stuck with whatever snapshot you took.

The Victory: pgBackRest with WAL Archiving

Enter pgBackRest—a robust, open‑source backup solution that handles base backups, incremental backups, WAL archiving, and even remote storage (S3, GCS, Azure). Below is a streamlined setup that gets you from zero to hero in under an hour.

1. Install & Configure 

# On the PostgreSQL server (assuming Ubuntu/Debian)
sudo apt-get install -y pgbackrest

# Create the config directory
sudo mkdir -p /etc/pgbackrest
sudo chown postgres:postgres /etc/pgbackrest
sudo -u postgres pgbackrest --stanza=myapp --cfg=/etc/pgbackrest/pgbackrest.conf stanza-create
Enter fullscreen mode Exit fullscreen mode

/etc/pgbackrest/pgbackrest.conf

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2          # keep 2 full backups
process-max=4
log-level-console=info
log-level-file=debug

[myapp]
pg1-path=/var/lib/postgresql/14/main
Enter fullscreen mode Exit fullscreen mode

2. Enable WAL Archiving in PostgreSQL

Edit postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=myapp archive-push %p'
Enter fullscreen mode Exit fullscreen mode

Reload PostgreSQL:

sudo systemctl reload postgresql
Enter fullscreen mode Exit fullscreen mode

3. Take a First Backup 

sudo -u postgres pgbackrest --stanza=myapp --type=full backup
Enter fullscreen mode Exit fullscreen mode

You’ll see output like:

INFO: executing full backup
INFO: backup starts after the requested immediate checkpoint
INFO: backup size = 12.3GB, file total = 1542
INFO: backup completed successfully
Enter fullscreen mode Exit fullscreen mode

4. Incremental Backups (run nightly via cron) 

0 2 * * * postgres pgbackrest --stanza=myapp --type=incr backup >> /var/log/pgbackrest.log 2>&1
Enter fullscreen mode Exit fullscreen mode

Each incremental backup only stores the WAL segments that changed since the last backup, making it fast and storage‑efficient.

5. Point‑In‑Time Recovery (PITR)

Suppose disaster strikes at 2025-09-24 14:32:00 and we need to recover to just before that—say 2025-09-24 14:30:00.

sudo -u postgres pgbackrest --stanza=myapp --type=time \
    --target="2025-09-24 14:30:00" restore
Enter fullscreen mode Exit fullscreen mode

pgBackRest will:

  • Pull the latest full backup that precedes the target time.
  • Replay the necessary WAL archives to roll forward exactly to the requested timestamp.
  • Bring PostgreSQL back online, ready to accept connections.

6. Verify Your Restore (The Real Test)

Never trust a backup you haven’t tried to restore. Schedule a weekly restore test to a spare instance:

# Restore to a test directory
sudo -u postgres pgbackrest --stanza=myapp --type=time \
    --target="2025-09-23 00:00:00" --delta restore \
    --pg1-path=/var/lib/postgresql/14/test

# Start the test cluster
sudo -u postgres /usr/lib/postgresql/14/bin/pg_ctl start \
    -D /var/lib/postgresql/14/test -l /var/log/postgresql-test.log

# Run a quick sanity check
sudo -u postgres psql -p 5433 -c "SELECT COUNT(*) FROM orders;"
Enter fullscreen mode Exit fullscreen mode

If the count matches expectations, you know your backup chain is healthy. If not, you’ve caught a problem before it hits production.

Traps to Avoid

Trap Why It’s Bad How to Dodge
Relying only on logical dumps (pg_dump) Misses WAL, indexes, large objects; restores are slow and can be incomplete. Use physical base backups + WAL archiving (pgBackRest, Barman, or built-in pg_basebackup).
Never testing restores You might discover corruption only when you need the data most. Automate a weekly restore to a staging server and verify key checksums or row counts.
Storing backups on the same disk A single hardware failure wipes both live data and backups. Push backups to remote storage (S3, GCS, Azure) or a separate NAS.
Ignoring WAL archive retention Old WAL segments get deleted, breaking the chain for PITR. Set repo1-retention-archive or use a WAL‑archiving service with adequate retention.

Why This New Power Matters

Now, when the inevitable “oops” happens—whether it’s a fat‑fingered DROP TABLE, a rogue migration, or a ransomware attack—I don’t panic. I know I have a full backup from last night, a chain of incremental backups filling the gaps, and the ability to rewind time to any second I choose. The recovery process is a few commands, not a frantic scavenger hunt through dusty tarballs.

More importantly, the team’s confidence has skyrocketed. Junior engineers no longer fear the “backup” ticket; they treat it like any other routine task, knowing the safety net is real and tested. And that peace of mind? It’s priceless—it lets us focus on building features instead of rebuilding databases.

Your Turn: Embark on Your Own Backup Quest

Here’s a challenge for you: pick one of your non‑critical databases tonight, set up a pgBackRest stanza with a full backup, enable WAL archiving, and schedule a nightly incremental. Then, tomorrow morning, restore to a point exactly one hour ago and run a simple SELECT COUNT(*) on a big table. If the numbers line up, you’ve just leveled up your database‑survival skills.

What’s the first backup strategy you’ll implement today? Drop a comment below—I’d love to hear how your quest unfolds! 🚀

Top comments (0)