Backup and Recovery: Full, Incremental, Differential, and Point-in-Time

Introduction

If production data is corrupted, deleted, or inaccessible, your backup and recovery strategy determines how much data you lose and how long service stays unavailable. Having backup files is not enough; teams need to know that restores work and meet their recovery targets.

This guide compares full, incremental, and differential backups, explains point-in-time recovery with WAL archiving, and covers recovery point and recovery time objectives. It also looks at restore testing and the limits of relying on cloud-managed backups alone.

For example, if an operator drops a table at 15:00, a base backup from 02:00 alone only restores the database to 02:00. Archived WAL lets you replay changes and recover to a point just before the mistake.

Backup Types Explained

Full Backups

A full physical base backup captures the database cluster’s files. A logical backup such as pg_dump captures selected database objects in a portable format. The right baseline depends on the restore method and tooling you choose.

# PostgreSQL full backup example
pg_basebackup -h localhost -U postgres -D /backups/full -Ft -z -P

Advantages:

• Complete restoration in a single operation
• Simple to understand and verify
• No dependency on other backups for restore
• Self-contained disaster recovery

Disadvantages:

• Largest storage footprint
• Longest backup duration
• Maximum impact on production system I/O
• Cannot be applied incrementally

Full backups are the foundation of any backup strategy. You should always have at least one full backup as your recovery base.

Incremental Backups

An incremental backup captures changes relative to an earlier backup. PostgreSQL 17 and later support incremental physical backups with pg_basebackup --incremental, using an earlier backup’s manifest; -X stream only streams WAL while the base backup runs.

# PostgreSQL 17+: incremental physical backup based on a prior backup manifest
pg_basebackup -h localhost -U postgres -D /backups/incr_$(date +%Y%m%d_%H%M%S) \
  --incremental=/backups/full/backup_manifest -Ft -z -P

Incremental backups depend on their reference backups and must be combined with pg_combinebackup before recovery. Older PostgreSQL releases need a backup tool that explicitly supports incremental backups.

Advantages:

• Minimal storage consumption
• Fast backup operations
• Low production system impact
• Frequent backups possible

Disadvantages:

• Restore requires chain of all backups from last full
• Complex restore procedures
• Backup chain corruption means data loss
• Longer recovery time

The key insight: incremental backups are delta changes only. To restore, you need the last full backup plus every incremental backup in sequence.

Differential Backups

A differential backup captures changes since the last full backup. PostgreSQL’s pg_basebackup does not provide a differential-backup mode; use a backup tool that documents this strategy if you need it.

Differential backup: changed data since the latest full backup
Restore set: latest full backup + latest differential backup

Advantages:

• Smaller than full backups
• Simpler restore than incremental (only need full + one differential)
• Moderate backup duration
• Good balance of storage and complexity

Disadvantages:

• Larger than incremental backups (grows over time)
• Backup duration increases as differential grows
• Still requires full backup + one differential to restore

Differential backups offer a middle ground: you trade some storage for restore simplicity.

Understanding Point-in-Time Recovery

Point-in-time recovery (PITR) allows you to restore a database to any specific moment—not just the moment when a backup completed. This is essential for recovering from user errors, partial data corruption, or transactions that shouldn’t have happened.

How PITR Works

PITR relies on Write-Ahead Logging (WAL). Every database modification is written to a sequential log file before being applied to data files. This log captures:

• Transaction begin and commit records
• Every row-level change (insert, update, delete)
• Page-level changes for efficiency

-- PostgreSQL: Configure WAL archiving
wal_level = replica
max_wal_senders = 3
archive_mode = on
archive_command = 'cp %p /archive/wal/%f'

To perform PITR:

1. Restore the latest full backup to a recovery location
2. Configure recovery settings in postgresql.conf and create a recovery.signal file in the restored cluster
3. Replay archived WAL segments sequentially
4. Database reaches target state and becomes available

# postgresql.conf in the restored cluster
restore_command = 'cp /archive/wal/%f %p'
recovery_target_time = '2026-03-26 14:30:00 UTC'

Create an empty recovery.signal file in the restored data directory before starting PostgreSQL. Recovery settings are no longer stored in recovery.conf.

WAL Archiving Considerations

WAL archiving is the backbone of robust backup strategies. Key considerations:

Archive Storage: WAL volume depends on the workload and can differ substantially from database write volume. Measure generated WAL over representative peak and quiet periods, then size the archive for that measured rate and your retention window.

Archive Verification: Untested WAL archives are worthless. Regularly verify that archived WAL can actually restore.

# Remove archived WAL older than a known restart point; this is cleanup, not verification
pg_archivecleanup /archive/wal 00000001000000000000001C

Archiving and replication serve different recovery needs: PITR replays archived WAL from a base backup. Streaming replication can keep a standby current for failover; its retention and recovery behavior depend on configuration.

Method	Typical use	Key consideration
WAL archiving	Point-in-time recovery	Archive frequency and gaps determine the recoverable window.
Physical streaming replication	Standby and failover	Replication lag and failover behavior must be monitored and tested.
Logical replication	Selective data replication	It is not a substitute for physical backups and WAL-based PITR.

RTO and RPO Planning

Recovery Time Objective (RTO) and Recovery Point Objective (RPO) are your contractual commitments to stakeholders about recovery capabilities.

Defining Your Objectives

RTO (Recovery Time Objective): Target time to restore service after an outage. This is a duration—typically hours or minutes.

RPO (Recovery Point Objective): Maximum acceptable data loss measured in time. If your RPO is 1 hour, you accept losing up to 1 hour of transactions.

These aren’t technical decisions—they’re business decisions. Work with stakeholders to define realistic objectives based on:

• Cost of downtime per hour
• Cost of data loss per transaction
• Regulatory requirements
• Customer expectations

Translating to Technical Requirements

Once you have RTO and RPO targets, translate them to technical specifications:

RPO	Technical Requirement
0 (no data loss)	Synchronous replication or synchronous commit
15 minutes	Frequent WAL archiving + tested recovery
1 hour	Hourly differential backups + WAL
24 hours	Daily full backups

RTO	Technical Requirement
Minutes	HA architecture, automatic failover
Hours	Tested restore procedures, on-call staff
Days	Documented manual recovery procedures

The Math of Recovery

Calculate realistic recovery times:

def estimate_recovery_time(
    full_backup_size_gb: float,
    differential_size_gb: float,
    network_mbps: float,
    restore_speed_mbps: float,
    wal_replay_seconds: int
) -> dict:
    transfer_time = (full_backup_size_gb * 1024 * 8) / network_mbps  # seconds; decimal network Mbps
    restore_time = (full_backup_size_gb * 1024) / restore_speed_mbps  # seconds; MB/s
    wal_time = wal_replay_seconds

    total_restore_minutes = (transfer_time + restore_time) / 60
    total_rto_minutes = total_restore_minutes + (wal_time / 60)

    return {
        "transfer_minutes": round(transfer_time / 60, 1),
        "restore_minutes": round(restore_time / 60, 1),
        "wal_replay_minutes": round(wal_time / 60, 1),
        "total_rto_minutes": round(total_rto_minutes, 1)
    }

Cloud Backup Solutions

AWS RDS

Amazon RDS provides automated backups with configurable retention:

# RDS backup configuration (AWS Console or CLI)
aws rds modify-db-instance \
    --db-instance-identifier my-db-instance \
    --backup-retention-period 30 \
    --preferred-backup-window "03:00-04:00" \
    --preferred-maintenance-window "sun:04:00-sun:05:00"

Key features:

• Automated daily backups with configurable retention (1-35 days)
• Point-in-time recovery to any second within retention period
• Manual snapshots for long-term retention
• Cross-region snapshot copying for disaster recovery
• Automated backup to S3 with encryption

Limitations:

• Restore time depends on snapshot size and network transfer
• PITR availability and retention depend on engine and configuration
• Cross-region copy latency for disaster recovery

Azure SQL

Azure SQL provides automated backups with geo-redundancy options:

# Azure SQL backup configuration
Set-AzSqlDatabaseBackupShortTermRetentionPolicy \
    -ResourceGroupName "my-resource-group" \
    -ServerName "my-server" \
    -DatabaseName "my-database" \
    -RetentionDays 30

Key features:

• Full backup every week, differential every 12 hours, log backup every 5-10 minutes
• Point-in-time restore within retention period (7-35 days)
• Long-term retention (LTR) for years
• Geo-restore with RA-GRS redundancy
• Active geo-replication for read scale and failover

Comparing Cloud Providers

Managed backup features vary by provider, database engine, service tier, region, and configuration. Verify the current retention window, PITR coverage, cross-region copy behavior, and long-term retention options for the specific service you plan to use. Treat provider documentation as a capability description, then test a restore in your own environment to measure its RTO and confirm application behavior.

Evaluation question	Why it matters
What are the configured backup and PITR retention windows for this engine and service tier?	Defaults and maximums vary by provider, engine, and configuration.
Can recovery use a separate region or account?	Regional recovery needs a copy or replica outside the failure boundary.
How long does an actual restore take, including application cutover?	The provider’s feature list does not establish your RTO.
Can required backups be retained for the applicable compliance period?	Long-term retention may need a separate archive and restore procedure.

When to Use / When Not to Use Each Backup Type

Full Backups — Use when:

• You need a standalone recovery point
• Recovery time is more important than backup time
• Storage is not a constraint
• You’re establishing a new backup baseline

Full Backups — Do not use when:

• Your database is very large (>10TB) and backup windows are tight
• You’re doing incremental backup chains

Incremental Backups — Use when:

• RPO targets are tight (under 1 hour)
• Storage costs must be minimized
• Backup window is limited

Incremental Backups — Do not use when:

• Your backup chain complexity is a operational risk
• You need faster recovery over storage savings

Differential Backups — Use when:

• You need a middle ground between full and incremental
• Restore simplicity matters but storage savings still appeal

Differential Backups — Do not use when:

• Differential growth becomes nearly as large as a full backup
• Your RPO requires more granular recovery

Backup Type Trade-offs

Dimension	Full Backup	Incremental Backup	Differential Backup
Backup size	Largest	Smallest	Grows over time
Backup speed	Slowest	Fastest	Moderate
Restore complexity	Simplest — one step	Complex — chain required	Moderate — full + one diff
Storage cost	Highest	Lowest	Moderate
RTO (restore time)	Lowest	Highest	Moderate
Best for	Small DBs, baseline	Large DBs, tight RPO	Medium RPO, moderate complexity

Production Failure Scenarios

Failure	Impact	Mitigation
WAL gap from missed archive	PITR restore impossible at exact point	Monitor archive status continuously, alert on gaps
Backup chain corruption	Full data loss — restore impossible	Verify backup integrity on every job, store checksums
Storage exhaustion during backup	Backup fails, disk fills	Monitor disk space, alert at 70% threshold
Restore failing due to permissions	DR procedure fails when needed most	Test restore with minimal permissions weekly
Cloud snapshot restore taking hours	Extended downtime exceeds RTO	Pre-stage recovery environment, test RTO monthly
Incremental backup overflow	Disk fills, backup deleted	Set retention enforcement, monitor growth rate

Capacity Estimation: Backup Storage Sizing and WAL Growth Rate

Backup storage sizing depends on your backup strategy, retention period, and database growth rate.

Full backup storage formula:

full_backup_size = database_size × compression_factor
compressed_full_backup_size = database_size / compression_ratio

For a 500GB PostgreSQL database with pg_dump compression (typical ratio 3:1 to 5:1 for row data, less for indexes):

• Row data with 3:1 compression: 500GB / 3 = ~167GB compressed dump
• With gzip at 5:1: 500GB / 5 = 100GB compressed dump
• Actual size varies significantly: highly compressible data (text, logs) compresses 10:1; binary data (JSON blobs) compresses 2:1

WAL growth rate formula:

WAL_daily_growth_bytes = measured_WAL_bytes_per_second × 86400

Measure WAL generation rather than inferring it from database write throughput. For example, 100MB/s of WAL generation is about 8.64TB per day before compression. wal_keep_size controls retained WAL for replication; it does not set the retention period for an external archive.

Incremental backup sizing: Track the change rate per day, not the total database size:

-- PostgreSQL: current WAL position since cluster initialization, not since checkpoint
SELECT
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), '0/00000000')::bigint
    ) AS wal_bytes_since_cluster_start;

Typical change rates: OLTP databases 1-5% of database size per day. Data warehouses with nightly batch loads: 20-50% per day during loads, 0% between loads. Knowing your change rate lets you size incremental backups correctly.

Observability Hooks: Backup Success/Failure Alerting and RTO Tracking

Key backup metrics: job success/failure, backup duration trends, restoration time, and backup size anomalies. The metric names below are examples from a site-specific exporter; map them to metrics your own collectors actually expose.

-- Track job duration and size using your backup tool's status/history command.

For PostgreSQL archiving, pg_stat_archiver exposes the last successful and failed archive attempts; it does not report end-to-end restore readiness.

# Critical: backup job failed
- alert: BackupJobFailed
  expr: backup_job_status != 0
  for: 1m
  labels:
    severity: critical
  annotations:
    summary: "Backup job {{ $labels.job }} failed: {{ $labels.error }}"

# Warning: backup duration increased significantly
- alert: BackupDurationAnomaly
  expr: backup_duration_seconds > 1.5 * avg_over_time(backup_duration_seconds[7d])
  for: 10m
  labels:
    severity: warning
  annotations:
    summary: "Backup taking {{ $value }}s vs typical {{ $labels.typical }}s"

# Warning: backup size anomaly (could indicate data growth or corruption)
- alert: BackupSizeAnomaly
  expr: backup_size_bytes > 2 * backup_size_bytes_offset_1d
  for: 1h
  labels:
    severity: warning
  annotations:
    summary: "Backup size {{ $value }} bytes is 2x yesterday's {{ $labels.yesterday }}"

# Critical: WAL archive failures (PITR at risk)
- alert: WalArchiveFailures
  expr: pg_archiver_failed_count > 0
  for: 5m
  labels:
    severity: critical
  annotations:
    summary: "PostgreSQL WAL archiving has failed; check archive health and PITR coverage"

Security and Compliance Notes

• Confidentiality: Encrypt database backups, snapshots, and WAL archives in transit and at rest. Keep encryption keys and their recovery path separate from production database credentials; an encrypted backup is unusable if its key is lost.
• Integrity and tamper resistance: Verify checksums before restoring, and keep an immutable or offline copy in a separate account or administrative boundary. Production credentials should not be able to alter or delete every recovery copy.
• Access: Separate backup-write and restore permissions. Restrict destructive actions such as deleting snapshots or changing retention, require an approved break-glass path for them, and audit reads, restores, and policy changes.
• Retention and recovery: Set retention and legal holds to match applicable data rules, including WAL archives and cross-region copies. Restore into an isolated recovery environment, verify the data and application before directing production traffic to it.

Quick Recap Checklist

Use this checklist when designing or reviewing backup and recovery for a database system:

• Full backup completed and verified within last 24 hours
• Incremental or differential backups scheduled based on RPO requirements
• WAL archiving enabled and verified for PITR capability
• Backup integrity verified via checksum validation after every job
• Restore procedure tested in isolated environment within last 30 days
• RTO and RPO documented and communicated to stakeholders
• Cross-region backup copies exist for disaster recovery
• Backup retention policy aligned with compliance requirements
• Backup monitoring alerts configured for job failures and capacity
• Recovery runbook documented with step-by-step restore procedures

---

Testing Recovery Procedures

Here’s the uncomfortable truth: a backup that cannot be restored is not a backup. This is non-negotiable.

Regular Recovery Testing

Weekly verification: Automated test restore to confirm backup chain validity.

#!/bin/bash
# Weekly backup verification script
set -euo pipefail

# Validate the latest physical base backup manifest. Set this path from your backup inventory.
pg_verifybackup /backups/latest

Monthly full disaster simulation: Complete restore to isolated environment, verify data integrity, measure actual RTO against targets.

What to Verify

Verification is not a single step. It is a chain of checks that must all pass before you trust a backup. Each stage catches different failure modes, and skipping any of them leaves a gap that only shows up during a real disaster.

Backup completion status confirms the job ran without errors. Check the exit code, the job log, and the backup size against expected ranges. A job that exits successfully but produces a 1KB file is not a successful backup — it is a false positive. Alert thresholds should trigger when backup size drops below 80% of the moving average.

Backup chain integrity validates that all required WAL segments are present and usable. Do not use pg_archivecleanup for verification; it deletes archived WAL that is older than the supplied restart point. Check the backup tool’s validation output and test recovery through the intended target time.

Restore completion means the backup actually restores without errors — not just that the backup command exits cleanly. For a pg_dump archive, pg_restore --list inspects its contents; for a physical base backup, use pg_verifybackup and perform a restore test. After restore, compare expected database contents and size against a known baseline.

Data integrity goes beyond row counts. Run checksum validation on critical tables, verify foreign key relationships, and spot-check high-value rows against known-good baseline values. Row count alone does not catch silent data corruption.

Application functionality testing happens after restore: run your application’s standard query suite against the restored database. If your app queries a specific customer record, query that record. If it aggregates daily totals, run that aggregation. This catches restore issues that only surface at the application layer.

Performance baseline establishes that restore does not degrade database performance. Measure query latency on the restored system before and after restore. If p99 query time doubles after restore, the backup is technically complete but operationally useless.

Common Recovery Failures

• WAL gap: Missing WAL segments break the restore chain
• Corrupted backup: Checksum failures on backup files
• Storage exhaustion: Restore fails due to insufficient disk space
• Permission issues: Restored database has wrong ownership
• Timezone handling: Timestamps differ between backup and restore environments

---

Recovery During GitHub’s 2018 MySQL Incident

In October 2018, a network partition led GitHub’s MySQL clusters to accept writes in two data centers, leaving divergent data that could not be reconciled by a simple failback. GitHub chose a controlled recovery to protect data integrity. Restoring multi-terabyte backups from remote object storage and rebuilding replication took hours; GitHub said its backup procedure was tested daily, but this was the first time it had to rebuild an entire cluster from backup rather than rely on delayed replicas.

The incident illustrates two separate recovery questions: whether a backup can be restored, and how long a full restore takes under real conditions. GitHub’s postmortem describes the transfer, checksum, preparation, and load work involved in restoring large backups. GitHub’s incident analysis explains the failure and recovery timeline.

The lesson: keep tested backups and replicas with clear roles, and measure complete restore time instead of assuming it from a successful backup job.

---

Interview Questions

---

Further Reading

• Disaster Recovery — DR patterns and planning
• Relational Databases — Foundational concepts
• PostgreSQL: Backup and Restore — Base backups, logical dumps, and recovery
• PostgreSQL: Continuous Archiving and PITR — WAL archiving and point-in-time recovery

---

Conclusion

Backup and recovery is not a set-it-and-forget-it operation. It requires ongoing attention, testing, and refinement.

Start with the basics: full backups are non-negotiable. Layer on incremental or differential backups based on your RPO requirements. Enable WAL archiving if you need point-in-time recovery. Test everything—regularly.

The time to discover your backup strategy fails is not during an actual disaster. Run fire drills. Simulate failures. Know exactly what your RTO and RPO actually are under real conditions, not just on paper.

For deeper exploration of disaster recovery patterns, see our disaster recovery guide. And for monitoring your database health, check out relational databases fundamentals.