Database Backup Strategies for Small Teams

Table of Contents

TL;DR
#

Start with managed database backups if using RDS/Cloud SQL—they’re automatic and battle-tested
For self-hosted databases, use native tools (pg_dump, mysqldump) + cron + S3 lifecycle policies
Test restores quarterly. A backup you can’t restore from is just wasted storage
3-2-1 rule still applies: 3 copies, 2 different media types, 1 offsite
Don’t overthink it—a simple working backup beats a complex one that fails silently

Who this is for
#

Teams of 3-10 engineers running production databases without a dedicated DBA. You know backups matter but aren’t sure if you’re overdoing it or setting yourself up for data loss. This covers both managed cloud databases and self-hosted setups.

The backup pyramid: match complexity to risk
#

Not all data is created equal. Your backup strategy should match your actual recovery needs:

Tier 1: “We’d be annoyed but fine”

Development databases
Analytics data you can regenerate
Solution: Daily snapshots, 7-day retention

Tier 2: “This would hurt but we’d recover”

Production data with paper trails elsewhere
User-generated content with recent backups
Solution: Hourly snapshots — block-level, from RDS or your disk layer — with 30-day retention, tested quarterly. Note this is not hourly pg_dump: a logical dump at this cadence is a different and much heavier proposition, as the sizing note below explains

Tier 3: “Company-ending if lost”

Financial records
Core user data
Compliance-regulated data
Solution: Continuous replication, point-in-time recovery, 90+ day retention, monthly restore drills

Most small teams treat everything as Tier 3. This wastes time and money. Be honest about what actually matters.

Option 1: Managed database backups (boring is good)
#

If you’re on RDS, Cloud SQL, or Azure Database, use their built-in backups. Yes, it costs more than self-hosting. No, you shouldn’t care at your scale.

AWS RDS example:

Automated backups: enabled by default, 7-day retention
Manual snapshots: unlimited retention, ~$0.095/GB/month
Point-in-time recovery: restore to any second within retention window
Cross-region backup: one click in console

Cost for 100GB production database:

Automated backups: free (within retention period)
Monthly snapshots kept for a year: ~$9.50 per snapshot, so this grows to ~$114/month once twelve of them exist
Cross-region replication: ~$20/month

Budget around $30/month in the first months and a bit over $100 by the end of the year — and note that snapshots are incremental, so in practice you’ll pay less than the naive multiplication suggests. Either way it’s cheaper than the engineering time you’d spend rolling your own.

Option 2: Self-hosted database backups
#

Running databases on EC2/VMs? You’ll need to roll your own. Here’s a production-ready setup that won’t wake you at 3 AM.

The simple approach: cron + native tools + S3
#

#!/bin/bash
# /opt/scripts/backup-postgres.sh

set -euo pipefail

# Configuration
DB_NAME="production"
S3_BUCKET="mycompany-db-backups"
BACKUP_PREFIX="postgres"

# Credentials come from ~/.pgpass (chmod 600) or IAM auth — never inline.
# Note: a plain assignment would NOT reach pg_dump; the variable has to be
# exported to appear in the child process environment.
export PGPASSFILE="/root/.pgpass"

# Generate backup filename with timestamp
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_PREFIX}_${DB_NAME}_${TIMESTAMP}.dump"
BACKUP_PATH="/tmp/${BACKUP_FILE}"

# Create backup. Custom format (-Fc) is compressed, restorable in parallel
# by pg_restore -j, and lets you restore single tables.
# Note the dump itself is single-threaded here: pg_dump -j needs the
# directory format (-Fd), which writes a directory rather than one file.
echo "Starting backup of ${DB_NAME}..."
pg_dump -h localhost -U postgres -d "${DB_NAME}" -Fc -Z 6 -f "${BACKUP_PATH}"

# Upload to S3
echo "Uploading to S3..."
aws s3 cp "${BACKUP_PATH}" "s3://${S3_BUCKET}/daily/${BACKUP_FILE}"

# Cleanup local file
rm "${BACKUP_PATH}"

echo "Backup complete: ${BACKUP_FILE}"

Add to crontab for daily backups:

15 3 * * * /opt/scripts/backup-postgres.sh >> /var/log/db-backup.log 2>&1

Notice what this script does not do: it never deletes anything.

Don’t prune with a script, and especially not by counting objects. The tempting one-liner — list the bucket, sort, tail -n +31, rm — deletes by count, not by age. Pair it with an hourly cron and “keep 30 days” silently becomes “keep 30 hours”; you discover this the morning after an incident you noticed a day late.

Use an S3 Lifecycle rule instead. It expires objects by age, it can’t be confused by filenames or clock skew, and it costs nothing to run.

There’s a second reason to keep deletion out of the script: it runs with credentials on the database host. If that host is compromised, a script that can s3 rm hands the attacker your backups along with your data — which is precisely the ransomware scenario backups exist for. Give the backup role s3:PutObject and nothing else, turn on Versioning and Object Lock on the bucket, and let lifecycle policy handle expiry with a separate principal.

Sizing reality check: pg_dump is a logical dump — it reads every row through a single long-lived transaction. On 100 GB that’s hours of work, and the open snapshot blocks VACUUM from reclaiming space and holds back DDL the whole time. Daily is reasonable; hourly is not, and at that size you want a parallel dump at minimum — which means switching format, because -j only works with the directory format:

pg_dump -h localhost -U postgres -d production -Fd -j 4 -Z 6 -f /backup/production

-Fc -j 4 fails outright with parallel backup only supported by the directory format. The directory form writes one file per table into /backup/production, so the upload becomes aws s3 sync rather than aws s3 cp. If that trade is unappealing, skip ahead to the WAL archiving section below.

Cost, honestly: this script stores full dumps, so change rate doesn’t help you. Thirty daily dumps of a 100 GB database compressing to ~25 GB is roughly 750 GB in S3 Standard, about $17/month — and less if you add a lifecycle transition to Glacier Instant Retrieval after a week.

Level up: Add monitoring
#

The scariest backup failure is the silent one. Add simple monitoring:

Backup freshness check: Alert if latest backup is >25 hours old
Backup size check: Alert if backup size drops >20% (corruption indicator)
Test restore: Monthly cron job that restores to a test instance

Most monitoring tools (Datadog, New Relic, even CloudWatch) can check S3 object age. Use them.

Option 3: Continuous replication (when you need point-in-time)
#

Need to recover to a specific transaction? You want continuous archiving:

PostgreSQL: WAL archiving to S3

Set archive_mode = on and wal_level = replica
Use archive_command to ship WAL files to S3
Combine with daily base backups
Recovery: restore base backup, replay WAL files

MySQL: Binary log shipping

Enable binary logging
Ship logs to S3 with mysqlbinlog
Similar recovery process

This is more complex but gives you point-in-time recovery. Only worth it for Tier 3 data.

The 3-2-1 rule for small teams
#

The classic backup rule: 3 copies, 2 different storage types, 1 offsite. It’s worth being honest about how much of it a pure-cloud setup actually satisfies:

Primary: Your production database
Secondary: S3 in the same region
Tertiary: S3 in a different region or Glacier

That gives you three copies and a genuine offsite, but not two different media — copies 2 and 3 are the same storage service under the same account and the same provider. The failure modes that survive a region are billing suspension, a compromised root account, and an IAM mistake that deletes both at once.

If that matters to you, the cheap fix is a fourth copy somewhere structurally different: another provider’s object storage, or a monthly dump pulled down to hardware you control. If it doesn’t matter to you, say so deliberately rather than believing you’ve implemented 3-2-1.

For most small teams, the practical version is:

Daily backups to S3 with Versioning and Object Lock enabled
Lifecycle transition to Glacier after 30 days, expiry by policy rather than by script
Monthly copy to a different region
Quarterly copy to a different provider (if the paragraph above bothered you)

Testing restores: the part everyone skips
#

A backup you’ve never restored is Schrödinger’s backup—simultaneously working and broken until observed.

Quarterly restore checklist:

Pick a random daily backup from last month
Restore to test instance
Run basic smoke tests (row counts, recent data present)
Document time to restore (your RTO)
Delete test instance

Put it in the calendar. Make it someone’s OKR. Track it like deploys. Whatever it takes to actually do it.

When to upgrade your approach
#

Your simple backup strategy stops being simple when:

Restores take >4 hours (business can’t wait)
You’re backing up >1TB (restore time becomes painful)
Compliance requires specific retention/encryption
You need cross-region HA (not just DR)
Multiple databases need coordinated backups

At that point, look at:

Dedicated backup tools (Percona XtraBackup, pgBackRest) — these are also the answer if daily logical dumps have become too slow, since they do incremental physical backups and point-in-time recovery
Managed services (AWS Backup) or self-hosted backup platforms (Veeam, which is software you run, not a service you subscribe to)
Database-native solutions (RDS Multi-AZ, Cloud SQL HA)

But don’t jump there prematurely. Most teams under 10 engineers don’t need enterprise backup solutions.

Common mistakes to avoid
#

Assuming you must back up from the primary: You don’t, and usually you shouldn’t. Taking the dump from a read replica is standard practice precisely because it keeps hours of sequential reads off the instance serving your users — RDS supports it, and pg_dump against a standby is routine. Physical streaming replication doesn’t “diverge”; it lags, and lag is a number you can check before and after. What you do need to watch for on a standby is query cancellation during a long dump (max_standby_streaming_delay / hot_standby_feedback). Back up from the replica, verify the lag, and leave the primary alone.

Not testing encryption: That encrypted backup is useless if you lose the key. Store keys separately from backups.

Forgetting the schema: mysqldump --no-data for schema-only backups. Version control these.

Ignoring backup windows: That 3 AM backup might coincide with batch jobs. Check your backup impact.

Over-retaining: 7 years of daily backups for a startup MVP is hoarding, not strategy.

TL;DR #

Who this is for #

The backup pyramid: match complexity to risk #

Option 1: Managed database backups (boring is good) #

Option 2: Self-hosted database backups #

The simple approach: cron + native tools + S3 #

Level up: Add monitoring #

Option 3: Continuous replication (when you need point-in-time) #

The 3-2-1 rule for small teams #

Testing restores: the part everyone skips #

When to upgrade your approach #

Common mistakes to avoid #

Related reads #