Database Backups: A Simple Strategy That Actually Works

Every developer agrees backups are important. Far fewer have ever restored one. That gap is where disasters live: the backup job that silently failed months ago, the dump file that turns out to be empty, the restore that takes nine hours when the business expected one.

Here's a backup strategy that's simple enough to actually maintain.

Start with two questions

Before choosing tools, decide:

• RPO (recovery point objective): how much data can you afford to lose? An hour? A day? For a hobby project, a day may be fine. For a store taking orders, maybe minutes.
• RTO (recovery time objective): how long can you be down while restoring? Ten minutes? Half a day?

These two numbers decide everything else: how often to back up, which method to use, and how much to spend.

The 3-2-1 rule

A classic that still holds:

• 3 copies of your data (production plus two backups)
• on 2 different types of storage
• with 1 copy off-site, meaning a different provider or region

If your only backup lives on the same server or account as your database, one mistake, compromised account or provider outage can take both.

Backup types

Logical backups (dumps)

A dump exports your data as SQL or a compressed archive:

# PostgreSQL: custom format, compressed, restorable with pg_restore
pg_dump "$DATABASE_URL" -Fc -f backup_$(date +%F).dump

# MySQL
mysqldump --single-transaction --routines mydb | gzip > backup_$(date +%F).sql.gz

Pros: portable between servers and versions, easy to inspect, great for small and medium databases, and handy for copying data to staging. Cons: slow for very large databases, and you lose everything since the last dump.

Physical backups and point-in-time recovery

Physical backups copy the database's files, and combined with the transaction log they allow point-in-time recovery (PITR): restoring to any moment, like "two minutes before someone ran that bad DELETE."

Most managed databases (Supabase, Neon, cloud provider databases) offer automated backups and PITR, sometimes only on paid plans. Check exactly what your plan includes: retention period, PITR availability and how a restore actually works.

Snapshots

Disk or volume snapshots on a VPS are quick and convenient, but make sure they're consistent (the database flushed and quiet, or a snapshot method the database supports) and that they're not your only copy.

A practical setup for most projects

1. Use your provider's automated backups if you're on a managed database, and know the retention.
2. Add your own daily logical dump stored somewhere else: an object storage bucket at a different provider. A scheduled GitHub Action or a cron job on a server works well. (Scheduled GitHub Actions.)
3. Encrypt backups, because they contain all your users' data. Restrict who can access the bucket.
4. Set retention: for example, daily backups for 14 days, weekly for 3 months, monthly for a year. Lifecycle rules in object storage handle deletion automatically.
5. Monitor the job. Alert if a backup doesn't run or the file size suddenly drops.

The restore drill (don't skip this)

A backup you haven't restored is a hope, not a backup. At least every few months:

1. Create a fresh, empty database.
2. Restore the latest backup into it:

pg_restore --no-owner -d "$RESTORE_DATABASE_URL" backup_2026-03-01.dump

3. Check row counts on key tables and spot-check recent records.
4. Time it. Does it meet your RTO?
5. Write down the exact steps, so a stressed person at 3 a.m. can follow them.

Teams who do this regularly find problems early: missing extensions, permission issues, files that only contain the schema, a restore that takes far longer than anyone assumed.

Common backup mistakes

• Backups in the same place as the database. One deleted account or region outage removes both.
• Never testing a restore.
• Forgetting file uploads. User files in object storage need their own backup or versioning. (File uploads guide.)
• Storing backups unencrypted in a bucket with broad access.
• Relying on replicas as backups. A replica copies mistakes instantly. Deleted rows disappear on the replica too.
• No backup before a risky migration. (Migrations best practices.)

Free-tier note

Free database plans often have limited backup retention or none at all. If you're on one, your own scheduled dumps aren't optional. (Free database hosting.)

FAQ

How often should I back up my database?

It depends on how much data you can afford to lose. Daily is a common baseline for small apps; busy production systems typically use continuous backups with point-in-time recovery.

What is the 3-2-1 backup rule?

Keep three copies of your data, on two different types of storage, with one copy stored off-site at a different provider or location.

Is a database replica a backup?

No. Replicas copy every change, including accidental deletes, almost instantly. They help with availability and read scaling, but you still need independent backups.