Postgres backups that will actually save you

What a real Postgres backup strategy looks like for an SMB — including point-in-time recovery, off-site copies, and restore drills.

"We have backups" is not enough

The question is not "do you have backups." It is "have you restored from one in the last 90 days." If the answer is no, you do not have backups. You have files.

What a real strategy looks like

Three layers:

Daily logical backup with pg_dump to S3
Continuous WAL archiving for point-in-time recovery
A monthly restore drill into a separate environment

Skip any one of these and you have a hole. We have seen every one of them bite a client.

Layer 1: pg_dump to S3

pg_dump produces a portable, restorable snapshot. Run it nightly:

pg_dump -Fc dbname | gzip | aws s3 cp - s3://backups/$(date +%F).dump.gz

Keep 30 daily, 12 monthly, 5 yearly. Use S3 lifecycle rules to move older copies to Glacier.

This protects against logical corruption (a bad migration, an accidental DELETE).

Layer 2: WAL archiving with WAL-G

pg_dump runs once a day. If something goes wrong at 11pm, you lose 23 hours. WAL archiving fixes that.

WAL-G ships the WAL (write-ahead log) to S3 continuously. You can restore to any second in the last N days.

wal_level = replica
archive_mode = on
archive_command = 'wal-g wal-push %p'

Set up a base backup weekly and let WAL-G stream WAL constantly. RPO drops to about a minute.

Layer 3: restore drills

The first time you try to restore is not when production is down. Drill monthly:

Spin up a fresh VM
Pull the latest dump and WAL
Restore to a point in time
Run a known query, check the count

If anything fails, fix it now. We have caught broken WAL chains, expired S3 credentials, and corrupted dumps this way. Every time, in a calm restore — not at 2am.

What we do not do

We do not rely on the cloud provider's "managed backups" alone. RDS backups are great until you delete the database and the snapshots go with it. Always keep an off-account, off-region copy.

The cost

For an SMB, a real backup strategy costs about 5 USD per month in S3 fees. The cost of not having one is the cost of the company.

That is not a hard decision.