PostgreSQL Disaster Recovery: Automated Streaming Backups to S3

1. Why Local Disk Backups Are Inadequate

A pg_dump written to a file on the same VPS as the database it backs up protects against exactly one failure mode: accidentally deleting a table. It protects against nothing else that actually causes real data loss in practice — disk failure, a compromised host where an attacker wipes both the database and its 'backup' sitting on the same volume, an accidental rm -rf during maintenance, or the hosting provider losing the instance entirely. If your backup lives on the same failure domain as your primary data, it is not a backup, it is a second copy of the same single point of failure.

The 3-2-1 rule exists precisely for this reason: at least three copies of your data, on two different types of storage media, with at least one copy stored offsite. For a solo-maintained production database, a practical minimum is the live database itself, a local backup on the VPS for fast recovery from simple mistakes, and an encrypted copy shipped to object storage (S3, Backblaze B2, or similar) in a different provider and region than the VPS itself. The offsite copy is the one that actually saves you when the VPS provider has a regional outage or your account gets locked.

Backup frequency should be driven by your actual recovery point objective (RPO) — how much data loss is acceptable — not by convenience. A nightly pg_dump gives you an RPO of up to 24 hours, which is fine for a low-write blog or portfolio site and completely unacceptable for a transactional application processing payments or user-generated content continuously. For anything beyond a low-traffic side project, continuous WAL archiving (via a tool like WAL-G or pgBackRest) that ships write-ahead log segments as they're generated brings RPO down to minutes rather than a full day.

It is worth stating plainly: the backup strategy is not complete until it has been sized against your actual data volume growth, not the volume at launch. A pg_dump that takes ninety seconds today will take much longer once your tables have grown for a year, and a backup window that quietly creeps past your maintenance window is a problem you want to catch in monitoring, not during an actual incident.

2. Automated Daily Snapshot Script with Encryption

A logical backup via pg_dump -Fc (custom format) is the right starting point for most small-to-medium production databases: it's portable across PostgreSQL minor versions, supports selective table restore, and compresses well. Pipe the dump directly through gpg for encryption before it ever touches disk unencrypted, so a compromised backup destination doesn't hand an attacker your entire dataset in plaintext, and use a symmetric passphrase (or better, a dedicated GPG keypair) stored as a secret outside the backup script itself.

Naming backups with a timestamp and uploading immediately to object storage, rather than accumulating them locally first, minimizes the window where a backup exists only on the same host as the database it protects. The AWS CLI (or an S3-compatible client for other providers) handles the upload; wrap the whole sequence in a script triggered by a systemd timer or cron, with explicit error handling so a failed dump or a failed upload is loud rather than silent.