Database Backup & Recovery Engineering

Design database backup strategies. Covers backup types, point-in-time recovery, RPO/RTO, WAL archiving, cross-region replication, backup testing, and disaster recovery for databases.

Database backup and recovery engineering ensures that data remains durable, consistent, and available after failures—whether hardware, network, software, or human-caused. It matters when a database becomes the source of truth for a service, and downtime or data loss incurs real cost: a failed order, a lost transaction, a corrupted user profile. This page details the engineering practices, tools, and failure modes of building robust, recoverable databases across systems that range from single-server PostgreSQL to distributed multi-region clusters.

Planning and Executing Backups

The primary goal is to create a consistent, versioned, and restorable copy of a database at a known point in time. The process begins with defining a backup strategy: how often to run backups, what to include, and how to store them.

Full Backups

A full backup captures the entire database state. It is the foundation of any recovery plan.

For PostgreSQL, a full backup is initiated with pg_basebackup:

pg_basebackup \
  --pgdata /backups/primary-2024-04-05 \
  --host db-primary.example.com \
  --port 5432 \
  --username backup \
  --password \
  --checkpoint fast \
  --compress \
  --progress \
  --wal-method stream \
  --label "Full backup for 2024-04-05"

Key flags:

The result is a directory with base.tar and a pg_wal directory containing WAL segments. The backup is self-contained and can be restored independently.

Incremental Backups

Incremental backups capture only the changes since the last full or incremental backup. They reduce storage and time costs.

PostgreSQL uses WAL archiving to support incremental recovery. After a full backup, WAL files are continuously archived and retained:


archive_mode = on
archive_command = 'cp %p /backups/wal/%f'
archive_timeout = 300

With archive_timeout = 300, WAL segments are archived every 5 minutes. To create an incremental backup, a new base backup is started while WAL archiving continues, and the pg_wal directory is copied and compressed.

To restore from an incremental backup:

  1. Restore the full base backup.
  2. Apply all archived WAL files from the full backup’s timeline to the current timeline.
  3. Use pg_wal from the incremental backup as the new WAL directory.

This allows recovery to any point in time, including during the backup.

Ensuring Consistency and Durability

Backups are only as good as their consistency. A backup taken while a transaction is in flight may capture inconsistent data.

Consistent Backups with Checkpoints

To ensure consistency, a backup must be taken during a checkpoint. A checkpoint flushes all dirty pages from memory to disk, writes a checkpoint record to WAL, and updates the pg_control file.

Use pg_start_backup() and pg_stop_backup() in a transaction to define a consistent backup window:

-- Start the backup
SELECT pg_start_backup('backup-2024-04-05-14:00');

-- Wait for all data to be flushed
-- Run backup process here (e.g., pg_basebackup, rsync)

-- Finalize the backup
SELECT pg_stop_backup();

Failure to call pg_stop_backup() results in a backup that lacks the final checkpoint, leading to incomplete recovery.

Handling Long-Running Backups

Long backups may span multiple checkpoints or even multiple WAL segments. To handle this:

When WAL is not archived fast enough, pg_basebackup may fail with:

ERROR: could not find WAL segment 000000010000000000000014 in archive

This indicates that the backup has advanced beyond the available WAL segments in the archive directory.

Recovery from Failures

Recovery begins when a database fails—disk corruption, server crash, network partition, or human error.

Point-in-Time Recovery (PITR)

PITR restores a database to a specific moment in time using a full backup and a sequence of WAL files.

To recover to 2024-04-05 14:32:17:

# Step 1: Restore the base backup
pg_basebackup \
  --pgdata /var/lib/postgresql/data \
  --host db-primary.example.com \
  --port 5432 \
  --username backup \
  --password \
  --restore \
  --label "PITR to 2024-04-05 14:32:17"

# Step 2: Create recovery configuration
cat > /var/lib/postgresql/data/recovery.conf << EOF
restore_command = 'cp /backups/wal/%f %p'
recovery_target_time = '2024-04-05 14:32:17'
recovery_target_action = 'promote'
EOF

The database starts in recovery mode, applying WAL records until it reaches the target time. Then, it promotes to primary and becomes operational.

Recovery from Crash

A crash recovery is automatic: upon startup, PostgreSQL checks the pg_control file and applies WAL records to bring the database to a consistent state.

If pg_control is corrupted, PostgreSQL may fail to start:

FATAL: could not open file "pg_control" in directory "/var/lib/postgresql/data"
DETAIL: The file is missing or corrupt.

To recover:

pg_ctl -D /var/lib/postgresql/data -o "-c fsync=off" -m fast -w start

Then, use pg_controldata to inspect the control file:

pg_controldata /var/lib/postgresql/data/pg_control

This outputs:

Use this information to validate the recovery.

Backup Validation and Monitoring

A backup is only useful if it can be validated and monitored.

Verification with pg_verifybackup

After a backup is created, run pg_verifybackup to validate its integrity:

pg_verifybackup --pgdata /backups/primary-2024-04-05

It checks:

If the backup is invalid, it returns:

ERROR: backup is inconsistent
  - missing file: /backups/primary-2024-04-05/pg_wal/000000010000000000000015
  - LSN mismatch: expected 0/12345678, got 0/12345670

Monitoring Backup Jobs

Use systemctl to manage backup jobs:

# Define a backup service
cat > /etc/systemd/system/db-backup.service << EOF
[Unit]
Description=Daily database backup
After=network.target

[Service]
Type=oneshot
User=postgres
ExecStart=/usr/local/bin/backup-database.sh
StandardOutput=journal
StandardError=journal

[Install]
WantedBy=multi-user.target
EOF

The script backup-database.sh includes:

#!/bin/bash
set -euo pipefail

LOG="/var/log/db-backup.log"
exec >> "$LOG" 2>&1

echo "Starting backup $(date)"

pg_basebackup \
  --pgdata "/backups/db-$(date +%Y-%m-%d)" \
  --host db-primary.example.com \
  --port 5432 \
  --username backup \
  --password \
  --checkpoint fast \
  --compress \
  --progress \
  --wal-method stream \
  --label "Daily backup $(date)"

# Validate the backup
pg_verifybackup --pgdata "/backups/db-$(date +%Y-%m-%d)"

echo "Backup completed $(date)"

This service runs daily via systemctl enable db-backup.timer, and logs output to journalctl.

Monitor with:

journalctl -u db-backup.service -f

If pg_basebackup fails during the backup, the service logs:

ERROR: could not connect to server: Connection refused
  Is the server running on host "db-primary.example.com" (192.168.1.10) and accepting
  TCP/IP connections on port 5432?

This indicates network issues or a failed PostgreSQL instance.

Handling Backup Failures

Backup Overwrites and Retention

Backups are written to a shared storage system. Use rsync or borg for efficient storage.

To manage retention, keep only the last 7 full backups:

#!/bin/bash
BACKUP_DIR="/backups/db"
MAX_BACKUPS=7

# Get list of backup directories sorted by name
BACKUPS=($(ls -tr "$BACKUP_DIR" | tail -n +$MAX_BACKUPS))

# Remove old backups
for dir in "${BACKUPS[@]}"; do
  rm -rf "$BACKUP_DIR/$dir"
done

Use borg create for deduplicated, compressed backups:

borg create \
  --compression lz4 \
  --exclude-caches \
  --exclude 'logs/*.log' \
  /backups/borg::db-$(date +%Y-%m-%d) \
  /backups/db-*

Borg automatically deduplicates, so only changed files are stored.

Failure Modes and Mitigations

  ERROR: could not find backup label file
  HINT: backup_label file missing in /backups/primary-2024-04-05

Solution: add pg_stop_backup() to all backup scripts and validate with pg_verifybackup.

Multi-Region and High Availability Recovery

In multi-region setups, backups are taken from a primary and restored to a standby in a secondary region.

Use pg_basebackup with --stream and --wal-method stream to create a base backup with continuous WAL streaming.

Then, set up a standby:

# On secondary region
pg_basebackup \
  --pgdata /var/lib/postgresql/data \
  --host db-primary.example.com \
  --port 5432 \
  --username standby \
  --password \
  --stream \
  --wal-method stream \
  --label "Secondary standby"

# Create recovery configuration
cat > /var/lib/postgresql/data/recovery.conf << EOF
primary_conninfo = 'host=db-primary.example.com port=5432 user=standby password=secret'
restore_command = 'cp /backups/wal/%f %p'
trigger_file = '/tmp/primary.trigger'
EOF

The standby connects to the primary, fetches WAL, and applies it. When the primary fails, the standby promotes automatically by creating the trigger_file.

This setup enables rapid recovery across regions, with minimal data loss.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.