Database backup is the practice of capturing database state - logical (SQL dump) or physical (filesystem snapshot) - and storing copies offsite with periodic restore testing. The team that has full + incremental + transaction-log backups restores to any point in time, including a moment before a bad migration or accidental delete. The team that has only nightly dumps loses a day’s work on every disaster. The team that does not test restores has backups that may not work when actually needed.
Table of contents
- Logical vs physical backups
- The 3-2-1 rule applied to databases
- Backup frequency and retention
- Restoration patterns
- Common pitfalls
- FAQ
Logical vs physical backups
Logical backup: dump the database as SQL statements. pg_dump for PostgreSQL, mysqldump for MySQL, mongodump for MongoDB. The output is a SQL or JSON file that can be restored on a different server, different version, or different architecture. The team that uses logical backups has portability; the team that uses physical backups is locked to the same DBMS version and architecture.
Physical backup: snapshot the underlying files. Filesystem snapshots (LVM, ZFS, EBS snapshots), or DBMS-specific physical backups (Percona XtraBackup for MySQL, pg_basebackup for PostgreSQL). Faster to back up and restore on large databases; less portable. The team that has terabyte-scale databases uses physical backups for speed.
The right choice depends on database size and recovery requirements. The team that has a 10GB PostgreSQL database uses logical backups (fast enough, portable). The team that has a 10TB database uses physical backups (logical dump/restore takes too long). The team that has both does nightly physical + transaction-log archiving for point-in-time recovery.
The 3-2-1 rule applied to databases
3 copies: production database + local backup + offsite backup. The team that has all three has data loss only if two of three fail simultaneously - rare for properly maintained infrastructure.
2 media types: don’t store backups on the same disk type as production. The team that backs up an SSD-backed database to the same SSD array has both copies vulnerable to disk controller failure. The team that backs up to S3 or tape has different failure modes.
1 offsite: a backup in the same data center as production is not a real backup. The team that stores backups on the same cloud region as production has data loss on regional outage. The team that uses a different region (or a different cloud, or tape shipped offsite) has true geographic redundancy.
Backup frequency and retention
Full backup: complete database state. Daily or weekly, depending on database size. The team that has 10GB databases does nightly full; the team that has 1TB databases does weekly full + daily incremental.
Incremental backup: changes since last backup. Faster than full; requires the previous full + all incrementals to restore. The team that uses incrementals has faster nightly backups; the restore is more complex (apply full + all incrementals in order).
Transaction log backup / WAL archiving: continuous capture of every change. PostgreSQL WAL archiving, MySQL binlog backup, SQL Server transaction log backup. The team that archives transaction logs has point-in-time recovery (restore to any second in the past). The team that only has full + incremental restores to backup times, not in between.
Retention: how long to keep backups. The team that has 7-day retention satisfies most compliance; the team that has 30-day retention satisfies PCI-DSS; the team that has 1-year retention satisfies long-term compliance and forensic needs. The cost is storage - keep what is required, not more.
Restoration patterns
Point-in-time recovery: restore to a specific second in the past. Requires transaction log archiving + ability to replay logs to a specific time. The team that has PITR recovers from accidental deletes, bad migrations, and corruption that happened at a known time.
Full restore to a new server: spin up a new instance, restore the backup, point clients at it. The team that practices this regularly has confidence in the restore process; the team that has never done it discovers issues during the actual disaster.
Restore to a test environment, then validate. The team that restores a backup to a test environment, runs queries, and confirms data integrity has verified the backup. The team that only restores to test if a disaster happens may find the backup is corrupt or incomplete.
Time the restore. The team that times a restore knows the RTO. ‘4 hours to restore a 1TB database’ is information the team uses for capacity planning and SLA negotiation.
Common pitfalls
Backing up the wrong thing. The team that has a database on a VM backs up the VM but not the database - if the database is corrupt, the backup has the corrupt data. The team that uses DBMS-native backup tools (pg_dump, mysqldump, pg_basebackup) gets a consistent database state.
Backing up during peak load without throttling. The team that runs a full backup at noon locks tables and slows production. The team that runs backups during off-peak hours (or uses online backup tools like XtraBackup) does not impact production.
Encryption without key management. The team that encrypts backups and loses the encryption key has data loss. The team that uses a managed key service (AWS KMS, HashiCorp Vault) has key recovery.
No alert on backup failure. The team that does not alert when backups fail discovers the failure when restoring. The team that alerts on backup success/failure catches issues immediately. Use the backup tool’s monitoring or wrap it in a script that sends alerts.
FAQ
How often should I back up my database?
Depends on RPO. RPO of 1 hour: hourly backups. RPO of 1 day: daily backups. RPO of 5 minutes: transaction-log archiving with 5-minute intervals. The team that picks frequency based on RPO knows what they can lose; the team that picks based on convenience has unpredictable RPO.
What is the difference between pg_dump and pg_basebackup?
pg_dump creates a logical SQL dump. pg_basebackup creates a physical copy of the database cluster files. The team that uses pg_dump has portability and point-in-time flexibility. The team that uses pg_basebackup has faster restore on large databases.
Should I use cloud-native backup or DBMS-native tools?
Cloud-native (AWS RDS automated backup, Azure SQL automated backup) is simpler and integrates with the cloud provider’s snapshot system. DBMS-native (pg_dump, XtraBackup) gives more control. The team that uses RDS is happy with cloud-native; the team that runs self-managed databases usually uses DBMS-native.
How long should I retain database backups?
Depends on compliance and storage cost. The team that has no compliance requirement: 7-30 days. PCI-DSS: 1 year for transaction data. HIPAA: 6 years for some healthcare data. The team that picks retention based on requirements + storage cost has the right answer.
Can I back up a live database?
Yes, with the right tools. PostgreSQL: pg_dump uses MVCC, doesn’t block writes. MySQL: mysqldump with --single-transaction flag uses a transaction, doesn’t block writes. The team that uses DBMS-native tools gets online backups; the team that uses filesystem snapshots needs to coordinate with the DBMS to flush dirty buffers (and risks inconsistency).
What is a hot backup vs cold backup?
Hot backup: taken while the database is running and serving traffic. Cold backup: taken while the database is stopped. The team that runs hot backups has continuous availability; the team that runs cold backups needs a maintenance window. Modern DBMS tools support hot backups; the team that uses cold backups does so for simplicity or specific consistency needs.
How this fits the rest of the stack
For a sense of what the full project costs before it commits, the RunxBuild hosting calculator shows the line items together. The API, the database, the storage, the worker, the bandwidth - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers.
Useful related references: