How to Set Up Automated Database Backups

Quick answer

• Backups protect you from the failure mode that kills projects: the data is gone and there’s no copy.
• Automate three things: a scheduled dump, storage off the same machine, and a regular restore test.
• A backup you have never restored is a backup you are only hoping works.

Why backups first

The most common vibecoder data-loss story is “it worked until I lost the data” — a bad migration, an accidental delete, or a disk failure with no copy anywhere. Automated backups turn that from a catastrophe into an inconvenience, and they are cheap to set up relative to losing a customer’s data.

Step 1 — Write a dump script

For PostgreSQL, create backup.sh:

#!/usr/bin/env bash
set -euo pipefail
TIMESTAMP=$(date +%Y%m%d-%H%M%S)
pg_dump "$DATABASE_URL" --format=custom > "/backups/db-$TIMESTAMP.dump"

Make it executable (chmod +x backup.sh) and keep DATABASE_URL in the environment, not in the script.

Step 2 — Schedule it

Run it nightly with cron:

0 2 * * * /opt/app/backup.sh >> /var/log/backup.log 2>&1

How to verify it worked: the next morning, a new .dump file exists and the log has no errors.

Step 3 — Store copies off the same machine

A backup on the same disk as the database doesn’t survive that disk’s failure. Copy dumps to a second location, for example an object store:

aws s3 cp /backups/ s3://my-backups/ --recursive

Keep a retention rule so old backups are pruned and storage costs stay bounded.

Step 4 — Test the restore

The only proof a backup works is restoring it:

pg_restore --clean --dbname postgres://localhost:5432/restore_test /backups/db-20260815-020000.dump

How to verify it worked: the restore completes and a known row count matches the source. Run this drill on a schedule — monthly is a common starting point.

Step 5 — Alert on failures

A silent failure means you discover the missing backup only when you need it. Make the job fail loudly — send its errors to a channel you actually watch — so a broken backup is noticed within a day, not a month.

Where this bites vibecoders

An AI assistant will happily generate the backup script you ask for, but it won’t volunteer the restore test or the off-site copy — the two things that make a backup real. The script is the easy part; the discipline is automating the schedule, the second location, and the restore drill, then watching for silent failures.

Where AI coding assistants get this wrong

• Writing dumps to the same disk as the database, so one failure takes both.
• Hardcoding credentials in the backup script.
• Omitting retention, so storage fills up indefinitely.
• Never testing a restore, so the “backup” is unverified.

Checklist

• Automate the dump on a schedule, not by hand.
• Store a copy off the same machine, with a retention rule.
• Keep credentials in the environment, not the script.
• Restore-test on a regular schedule and verify row counts.
• Alert loudly when the backup job fails.

FAQ

How often should I back up?

Match it to how much data loss you can tolerate (your recovery point objective). Daily is a common baseline for small apps; databases with frequent writes or strict requirements may need continuous or more frequent backups.

What is the difference between a dump and a snapshot?

A dump is a logical, portable copy of the data (like pg_dump); a snapshot is a point-in-time image of the storage. Dumps are portable across versions; snapshots are faster to take and restore but tied to the storage. Many teams use both.

Why test restores?

Because backups fail silently: a dump can be truncated, encrypted with a lost key, or structurally broken. A restore test is the only way to know a backup actually works before the moment you depend on it.

Related topics

• What Is FinOps?
• How to Manage Secrets and Environment Variables Properly
• What Is a Software Supply Chain Attack?

Sources

• PostgreSQL — pg_dump
• PostgreSQL — pg_restore