Database Backup Strategies That Actually Work

Every self-hosting guide mentions backups, but few cover the details that determine whether a backup actually saves you during a disaster. A backup file on the same disk as your database is not a backup. A backup you have never tested restoring is not a backup. A backup whose integrity you do not verify is a gamble.

The 3-2-1 Rule, Applied to Databases

Three copies of your data, on two different media types, with one copy off-site. For databases, this translates to the live database (copy one), a compressed dump on a local NAS or external drive (copy two), and an encrypted copy in cloud object storage (copy three). The off-site copy is critical: if your server room floods or your VPS provider goes bankrupt, your local backups are useless.

Backup Methods: Which One Is Right?

pg_dump and mysqldump (Logical Backups)

These tools export your database as SQL statements. The output is portable between versions, human-readable, and compressible. The downside: they are slow on large databases. A 50GB database might take hours to export. Use logical dumps for databases under 5GB or as a secondary backup alongside physical backups.

pg_basebackup (Physical Backups)

PostgreSQL's physical backup copies the entire data directory at the filesystem level. It is fast, consistent, and suitable for databases of any size. Combined with WAL (Write-Ahead Log) archiving, it enables point-in-time recovery. You can restore to any moment, not just to the last dump. This is the method VPS1 uses for production databases.

Automating and Verifying Backups

Your backup script should do more than create a file. It should verify the backup's integrity after creation (pg_restore --list or mysqlcheck), compress and encrypt the output, upload it to off-site storage, and log the result. If any step fails, you need an alert. Silent backup failures are worse than no backups at all because they give you false confidence.

The verification step prevents the most common backup disaster: creating backups of a corrupted database for weeks, only discovering the corruption when you need the backup.

Testing Restores

Schedule a restore test every quarter. Spin up a temporary database server, restore your latest backup, run a validation query (row counts, checksums), and verify that applications can connect. Document every step. If you cannot restore your backup under calm conditions, you will not be able to restore it during a crisis at 3 AM.

Retention Policy

Keep daily backups for the past 30 days, weekly backups for the past 3 months, and monthly backups for the past year. Delete older backups automatically to manage storage costs. Your backup script should enforce this retention, not rely on someone remembering to clean up.

VPS1 automates the entire backup pipeline for managed clients: scheduled dumps, integrity verification, encryption, off-site replication, and quarterly restore tests.