How I Learned to Stop Worrying and Love Database Backups – A *Back to the Future* Adventure

The Quest Begins (The “Why”)

Picture this: it’s a Friday night, I’m about to shut down my laptop and binge the latest season of a sci‑fi show, when my phone buzzes. Production alerts are screaming — users can’t place orders, the checkout page is throwing 500 errors, and the logs are filled with “relation does not exist”. My heart drops. I dive into the database and discover that a recent migration script accidentally dropped a core table. The worst part? Our latest backup is from two days ago and it’s sitting on the same server that just got corrupted.

I spent the next three hours frantically trying to piece together data from binary logs, feeling like a detective in a noir film. When I finally restored enough to get the site limping back online, I was exhausted, frustrated, and swearing never to let that happen again. That night I made a promise to myself: I would build a backup and disaster‑recovery plan that could survive a rogue migration, a hardware failure, or even a rogue intern hitting “DELETE FROM users”.

The Revelation (The Insight)

The turning point came when I stopped thinking of backups as a copy‑and‑paste chore and started treating them as a time machine. If you can rewind to any point before disaster struck, you’re not just protecting data — you’re giving yourself the power to undo mistakes.

Two concepts changed everything for me:

1. Physical base backups + Write‑Ahead Log (WAL) archiving – Instead of dumping logical SQL every night, take a binary base backup once a week and continuously archive WAL files. This lets you restore to any second, not just the snapshot time.
2. Offsite, immutable storage – Store those base backups and WAL archives in a bucket that version‑locks objects (like AWS S3 Object Lock or Google Cloud Bucket Lock). Even if an attacker gains access to your server, they can’t erase or alter the backups.

With those two pieces, you get:

• Point‑in‑time recovery (PITR) – Roll back to right before the bad query.
• Fast restores – Base backup brings you close; replaying WAL gets you the rest.
• Peace of mind – Knowing your data lives somewhere safe, separate from your primary infrastructure.

Wielding the Power (Code & Examples)

The Old Way – Logical Dumps (the struggle)

# Crontab entry – runs every night at 02:00
0 2 * * * pg_dump -U prod_user mydb | gzip > /var/backups/mydb_$(date +\%F).sql.gz

Pros: Simple, human‑readable.

Cons:

• Only as good as the last dump (if you dump nightly, you lose up to 24 h of changes).
• Restoring a large database means replaying every INSERT/UPDATE — slow and error‑prone.
• Backup lives on the same disk; if the disk dies, the backup dies with it.

The New Way – Physical Base Backup + WAL Archive (the victory)

I chose pgBackRest because it handles the heavy lifting, but the same ideas apply with plain pg_basebackup and wal_keep_size.

1. Configure archiving in postgresql.conf

# Enable WAL archiving
archive_mode = on
archive_command = 'aws s3 cp %p s3://my-db-wal-archive/%f --sse AES256'
# Keep enough WAL for a week of recovery (adjust as needed)
wal_keep_size = '2GB'

2. Set up pgBackRest (stanza definition)

# /etc/pgbackrest.conf
[demo]
pg1-path=/var/lib/pgsql/14/data
pg1-port=5432

[global]
repo1-type=s3
repo1-s3-bucket=my-db-backups
repo1-s3-endpoint=s3.amazonaws.com
repo1-s3-region=us-east-1
repo1-s3-key=${AWS_ACCESS_KEY_ID}
repo1-s3-key-secret=${AWS_SECRET_ACCESS_KEY}
repo1-s3-bucket-region=us-east-1
# Enable object lock (immutable) if your provider supports it
repo1-s3-object-lock=true

3. Take a weekly base backup and continuously archive WAL

# Full backup every Sunday at 01:00
0 1 * * 0 /usr/bin/pgbackrest --stanza=demo --type=full backup

# Incremental backup daily (captures only changed files since last backup)
0 2 * * * /usr/bin/pgbackrest --stanza=demo --type=incr backup

pgBackRest streams WAL to the S3 bucket as they’re generated, so you always have an unbroken chain from the base backup to the present moment.

4. Point‑in‑time recovery (the magic spell)

Imagine the bad migration ran at 2025‑09‑24 14:32:00. To restore to just before that:

# Stop PostgreSQL
sudo systemctl stop postgresql

# Restore the latest backup that is older than the target time
/usr/bin/pgbackrest --stanza=demo --type=time --target="2025-09-24 14:30:00" restore

# Start PostgreSQL
sudo systemctl start postgresql

pgBackRest pulls the base backup, replays all WAL up to the requested timestamp, and leaves you with a database exactly as it was at 14:30:00 — no data loss, no guesswork.

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

Trap	Why it’s deadly	How to dodge it
Testing restores only once	You’ll discover a missing WAL file or a permission error when you actually need it.	Schedule a quarterly restore drill on a staging spin‑up.
Storing backups on the same volume	A disk failure nukes both live data and backup.	Use a different AZ, region, or at least a separate network‑attached store.
Skipping encryption	Anyone who gets hold of your backup can read passwords, PII, etc.	Enable server‑side encryption (SSE‑S3, SSE‑KMS) or encrypt before upload.
Ignoring retention policy	Old backups pile up, costing money and complicating recovery.	Define a lifecycle rule (e.g., keep daily for 30 days, weekly for 12 weeks, monthly for 12 months).
Relying solely on logical dumps for PITR	You lose sub‑second granularity and make restores painfully slow.	Pair logical dumps (for audit/export) with physical base backup + WAL for true PITR.

Why This New Power Matters

Now I can sleep peacefully knowing that a rogue DROP TABLE or a ransomware attack won’t mean weeks of data loss. I can spin up a fresh replica in minutes, run analytics on a point‑in‑time copy without touching production, and even satisfy compliance auditors who ask for “recoverable to any point in the last 30 days”.

The best part? The setup is code — version‑controlled, reviewable, and reproducible. I keep my pgbackrest.conf and postgresql.conf in a Git repo, so any tweak is tracked, and I can spin the exact same backup pipeline in a new environment with a single ansible-playbook run.

If you’ve ever felt that sinking feeling when the alerts start flashing, give this approach a try. You’ll go from “I hope the backup works” to “I know exactly how to rewind time”.

Your Turn – A Mini Quest

Here’s a challenge: spin up a cheap PostgreSQL instance (Amazon RDS Free Tier, Docker, or a local VM), configure WAL archiving to a public S3 bucket (or a local folder that mimics S3), take a base backup, and then deliberately DELETE FROM pg_class WHERE relname = 'your_table';. Try restoring to a point just before the delete using PITR.

When you see the table reappear, you’ll feel like you’ve just aced the final boss level. Share your experience — what surprised you, what tripped you up, and how you’d improve the pipeline.

Now go forth, back up your data like a pro, and remember: the best time to plant a backup tree was yesterday. The second best time is now. Happy restoring! 🚀