Database Backup Strategies | The Garnet Wiki

Database backups are the foundation of data durability and recovery. When a database fails—due to hardware, software, or human error—backup strategies determine how much data is lost and how quickly services can resume. This page details the core practices, decisions, and trade-offs in designing and operating database backups across diverse environments, from single-instance PostgreSQL to distributed, multi-region systems.

Choosing a Backup Frequency

The primary decision is how often to capture a consistent point-in-time snapshot of the database. Backups must balance retention cost, recovery time, and data loss tolerance.

For daily, consistent backups, use a combination of full and incremental backups. A full backup captures the entire database state. An incremental backup captures only the changes since the last full or incremental backup.

On PostgreSQL, a full backup can be created with pg_basebackup:

pg_basebackup -D /backups/primary/2024-05-10 \
               -Ft -z -P \
               -h db-primary.example.com \
               -U backup_user \
               -W

-Ft: creates a tar format archive.
-z: compresses the output with gzip.
-P: shows progress during transfer.
-h, -U, -W: connect to remote host, use a dedicated user, prompt for password.

Incremental backups can be built using WAL (Write-Ahead Log) archiving. Configure postgresql.conf to archive WAL segments:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /archived_wals/%f'
max_wal_senders = 10

Each WAL file is a 16MB chunk of transaction log. When archive_command executes, it copies the WAL segment to a shared storage location. The backup system uses these segments to replay changes from a base backup.

To create an incremental backup from a base backup, use pg_receivewal to stream WAL logs to a directory:

pg_receivewal -D /backups/incremental/2024-05-10 \
              --start-timeline=1 \
              -h db-primary.example.com \
              -U backup_user \
              -W

The timeline tells the system which WAL sequence to start from. A timeline changes when the database is promoted (e.g., during failover). The backup system must track timelines to reconstruct the full history.

A common failure: forgetting to configure archive_command to be idempotent. If the command fails mid-copy, the same WAL file may be archived twice. The backup process then applies the same WAL records multiple times, leading to data corruption.

Selecting Backup Storage and Retention

Backups must be stored durably and efficiently. The storage choice affects cost, access speed, and recovery reliability.

Use a combination of local, network-attached, and cloud storage. For instance, store full backups locally on a file server, and archive incremental backups to object storage.

To upload a backup to AWS S3, use aws s3 cp:

aws s3 cp /backups/primary/2024-05-10.tar.gz \
             s3://backups.example.com/postgres/primary/2024-05-10.tar.gz \
             --storage-class STANDARD_IA

STANDARD_IA (Infrequent Access) reduces cost for backups that are written once and read less often. For cold backup storage, use GLACIER.

Retention policies must prevent storage bloat. Implement a rolling window retention: keep daily backups for 7 days, weekly for 4 weeks, monthly for 1 year.

Use find to delete old backups:

find /backups/primary -name "*.tar.gz" \
  -type f \
  -mtime +7 \
  -not -name "*2024-05-10.tar.gz" \
  -delete

This deletes all daily backups older than 7 days, except the most recent one. This preserves the "latest 7" while pruning older data.

A sharp edge: backing up the database while it is being updated. The backup may capture inconsistent state if the database is not quiescent. To avoid this, use pg_stop_backup() after pg_start_backup().

-- Start a backup
SELECT pg_start_backup('backup-2024-05-10');

-- Perform backup with pg_basebackup
-- ... (as above)

-- Complete backup
SELECT pg_stop_backup();

pg_start_backup() returns a WAL position. pg_stop_backup() returns the final WAL position and the backup label. The backup is consistent from the start to the stop WAL positions.

If pg_stop_backup() fails (e.g., due to network issues), the backup may be incomplete. The backup system must verify the backup by checking that the WAL segments between start and stop positions are present and correctly archived.

Automating Backup Execution

Automate backups using cron jobs or orchestration tools. A typical daily backup script:

#!/bin/bash


set -euo pipefail

DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/backups/primary/${DATE}"
WAL_DIR="/backups/incremental/${DATE}"

mkdir -p "${BACKUP_DIR}"
mkdir -p "${WAL_DIR}"

# Start backup
psql -c "SELECT pg_start_backup('${DATE}');"

# Run base backup
pg_basebackup -D "${BACKUP_DIR}" \
               -Ft -z -P \
               -h db-primary.example.com \
               -U backup_user \
               -W

# Stop backup
psql -c "SELECT pg_stop_backup();"

# Start WAL streaming
pg_receivewal -D "${WAL_DIR}" \
               --start-timeline=1 \
               -h db-primary.example.com \
               -U backup_user \
               -W &
WAL_PID=$!

# Upload base backup to S3
aws s3 cp "${BACKUP_DIR}.tar.gz" \
             s3://backups.example.com/postgres/primary/${DATE}.tar.gz \
             --storage-class STANDARD_IA

# Wait for WAL streaming to complete
wait "${WAL_PID}"

# Archive WALs to S3
aws s3 sync "${WAL_DIR}" \
             s3://backups.example.com/postgres/incremental/${DATE}/

# Clean up
find /backups/primary -name "*.tar.gz" -mtime +7 -not -name "*.tar.gz" -delete

This script ensures consistency, uploads data, and keeps retention. It also runs WAL streaming concurrently to reduce backup window.

Failures are common: pg_basebackup may timeout if the database is under load. The script must handle errors, such as missing WAL files or failed S3 uploads.

Use --wait in aws s3 sync to ensure all files are uploaded:

aws s3 sync "${WAL_DIR}" \
             s3://backups.example.com/postgres/incremental/${DATE}/ \
             --wait

The --wait flag blocks until all files are fully uploaded and the S3 bucket reflects the final state.

Verifying Backup Integrity

A backup is only useful if it can be restored successfully. Verification is often overlooked.

Run a periodic validation job to restore a backup into a test environment and validate it.

#!/bin/bash
# /opt/scripts/verify-backup.sh

BACKUP_DATE="2024-05-10"
RESTORE_DIR="/tmp/restore-${BACKUP_DATE}"
LOG_FILE="/var/log/backup-verify.log"

echo "Starting verification for ${BACKUP_DATE}" | tee -a "${LOG_FILE}"

# Extract backup
tar -xzf /backups/primary/${BACKUP_DATE}.tar.gz -C "${RESTORE_DIR}"

# Initialize test database
initdb -D "${RESTORE_DIR}/data"

# Configure PostgreSQL for test
cat > "${RESTORE_DIR}/data/postgresql.conf" <<EOF
data_directory = '${RESTORE_DIR}/data'
listen_addresses = 'localhost'
port = 5433
wal_level = replica
archive_mode = on
archive_command = 'echo "WAL archived: %p -> %f" > /tmp/archived.log'
EOF

# Start test instance
pg_ctl -D "${RESTORE_DIR}/data" -l "${RESTORE_DIR}/log" start

# Wait for startup
sleep 10

# Verify database is ready
if ! pg_isready -h localhost -p 5433 -U postgres -t 30; then
  echo "ERROR: Database failed to start" | tee -a "${LOG_FILE}"
  exit 1
fi

# Run health checks
psql -h localhost -p 5433 -U postgres -c "SELECT version();" | tee -a "${LOG_FILE}"
psql -h localhost -p 5433 -U postgres -c "SELECT count(*) FROM pg_tables;" | tee -a "${LOG_FILE}"

# Stop test instance
pg_ctl -D "${RESTORE_DIR}/data" stop

# Clean up
rm -rf "${RESTORE_DIR}"

echo "Verification completed for ${BACKUP_DATE}" | tee -a "${LOG_FILE}"

This script extracts the backup, starts a new PostgreSQL instance, and runs basic checks. It logs all output and exits with an error if anything fails.

The most common failure: not checking that pg_isready returns success. A test database may start but not accept connections due to misconfigured listen_addresses or port. The verification script must verify both startup and connectivity.

Handling Backup Failures and Recovery

When a backup fails, the system must detect and react.

Use systemd to monitor the backup script:

[Unit]
Description=Daily Database Backup
After=network.target

[Service]
Type=oneshot
ExecStart=/etc/cron.daily/db-backup
StandardOutput=journal
StandardError=journal
Environment=PATH=/usr/local/bin:/usr/bin:/bin
Restart=on-failure
RestartSec=300

[Install]
WantedBy=multi-user.target

This ensures the backup runs daily and restarts on failure. systemd logs the output and errors to journalctl.

To recover from a failed backup, restore the database from the latest full backup and apply the WAL logs.

On restore, use pg_recover to replay WAL segments:

pg_recover -D /var/lib/postgresql/data \
           -i \
           -w \
           -c "SELECT pg_start_backup('recovery-2024-05-10');"

-i: interactive mode, ask for WAL files.
-w: wait for WAL segments to be available.
-c: execute a command before starting recovery.

The pg_recover command reads the backup_label and tablespace_map files from the base backup and applies WAL segments in order.

If a backup is missing, use pg_archivecleanup to find and fetch missing WALs from a remote archive.

pg_archivecleanup /archived_wals/ \
                  /backups/primary/2024-05-10.tar.gz

This command checks the backup for all required WAL segments and removes those that are no longer needed.

A subtle but critical failure: incomplete WAL application. If the database crashes during recovery, the pg_wal directory may be missing or partially written. The backup system must verify that the recovery process completes and that the database is in a consistent state.

Use pg_isready and pg_controldata to check the state:

# After recovery, verify
pg_isready -h localhost -p 5433 -U postgres -t 30
pg_controldata /var/lib/postgresql/data | grep -E "(Latest checkpoint|Database system identifier)"

If the latest checkpoint is not the one from pg_stop_backup, the backup was not applied completely.

Combining Strategies for Resilience

A robust backup strategy combines frequency, storage, automation, verification, and recovery.

Use a dual-approach: daily full backups with continuous incremental WAL streaming. Store full backups locally and in cloud storage. Verify each backup with a test restore. Monitor execution with systemd and alert on failure.

For high availability, maintain a secondary database that can be promoted. Use pg_basebackup to create a standby server, and pg_receivewal to stream WAL logs.

When a primary database fails, promote the standby:

# On standby server
pg_ctl promote -D /var/lib/postgresql/data

Then, apply the latest WALs from the primary’s archive to the new primary.

The system can now recover from any failure with minimal data loss, thanks to a well-structured backup strategy.

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.