Skip to content
Navigation

Type at least two characters. Search covers page titles, headings, tags and database names.

↑ ↓ to navigateEnter to openEsc to close0 pages
Operationsintermediate

Backup Strategies

Choosing between logical, physical, incremental and snapshot backups, and building a schedule from a stated recovery objective.

3 min readIntermediateUpdated Edit this page

A backup strategy is derived from two numbers — how much data you can lose, and how long you can be down — not from a tool's defaults.

Full and Incremental Backups

Full — a complete copy. Simple to restore, expensive to take and to store.

Incremental — only what changed since the previous backup, full or incremental. Cheap to take, and a restore requires the full backup plus every increment in the chain.

Differential — everything changed since the last full backup. Larger than an incremental, but a restore needs only two pieces.

Backup costRestore costRisk
Full onlyHighLowestStorage cost forces a longer interval, worsening RPO
Full + incrementalLowestHighestOne corrupt link breaks the chain
Full + differentialMediumLowDifferentials grow through the week

Logical versus physical

Logical (pg_dump, mysqldump, mongodump) exports data in a portable form. It survives version changes and lets you restore a single table. It is slow at scale, because a restore re-inserts every row and rebuilds every index.

Physical (pgBackRest, XtraBackup, mariabackup, snapshots) copies data files. Fast to take and fast to restore, tied to the engine version and page format, and it is what point-in-time recovery requires.

Most production systems need physical plus continuous transaction-log archiving, with logical dumps kept for portability and single-object recovery.

Building the schedule

Work backwards from the recovery point objective:

RPO = 5 minutes   → transaction log archived at least every 5 minutes
RPO = 1 hour      → hourly log shipping, or hourly snapshots
RPO = 24 hours    → nightly full backup is sufficient

Then check the recovery time objective against a measured restore, not an estimate:

RTO = 1 hour, database = 2 TB
  → restore must sustain ~570 MB/s end to end
  → verify actual throughput before promising the number

If the measured restore cannot meet the RTO, the answer is a standby replica for failover, with backups covering the cases replication cannot — a mistaken delete, corruption, a compromised account.

Where backups live

The 3-2-1 shape: three copies, two media types, one off-site. In cloud terms, the important separations are:

  • A different account or subscription, so a compromised or misconfigured identity cannot delete both the database and its backups.
  • A different region, for the regional failure case.
  • Immutable retention — object lock or write-once storage — so nothing can delete backups inside the retention window, including you.

What else to capture

Data alone does not restore a service. Include roles and grants, configuration files, extensions and their versions, encryption keys, and the restore procedure itself — stored somewhere that does not depend on the system being restored.