karawaci.kode

2026-09-28 · 11 min

Database Backup, Restore, and Testing in Production: A Practical Tutorial for 2026

Most teams discover their backup is broken at 2 AM during an actual incident. That is a bad time to learn that pg_restore hangs on a constraint violation, or that the S3 bucket policy silently stopped allowing writes six weeks ago, or that the database is now 80 GB larger than when anyone last tested the restore.

This tutorial is about building a backup and restore process you have actually tested — not one you assume works.

Why “backup exists” is not the same as “backup works”

I ran a restore drill for a client in Serpong last year. They had been running nightly pg_dump to S3 for two years, confident in their disaster recovery posture. The restore test took four attempts: the first backup file was corrupted mid-transfer, the second was from a deprecated pg_dump version incompatible with their current PostgreSQL 15 cluster, the third restored successfully but was missing two schemas because the dump user lacked USAGE privileges, and the fourth finally worked — five hours after we started.

None of these failures would have shown up in a monitoring dashboard. The backup job reported success every night.

The problem is structural. Backup success metrics measure whether the dump process exited with code 0. They do not measure whether the resulting file is restorable, complete, or usable within your recovery time target.

The backup script that actually covers edge cases

Start with PostgreSQL. The same principles apply to MySQL/MariaDB, but pg_dump and pg_restore are the tools I have the most production experience with.

#!/usr/bin/env bash
# scripts/backup-db.sh
set -euo pipefail

# --- Config ---
DB_HOST="${DB_HOST:-localhost}"
DB_PORT="${DB_PORT:-5432}"
DB_NAME="${DB_NAME:?DB_NAME is required}"
DB_USER="${DB_USER:?DB_USER is required}"
BACKUP_DIR="${BACKUP_DIR:-/var/backups/postgres}"
S3_BUCKET="${S3_BUCKET:?S3_BUCKET is required}"
RETENTION_DAYS="${RETENTION_DAYS:-30}"
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump"
LOG_FILE="${BACKUP_DIR}/backup_${TIMESTAMP}.log"

mkdir -p "$BACKUP_DIR"

log() {
  echo "[$(date -u +"%Y-%m-%dT%H:%M:%SZ")] $*" | tee -a "$LOG_FILE"
}

log "Starting backup: ${DB_NAME}"

# Dump in custom format — supports parallel restore and selective table restore
PGPASSWORD="$DB_PASSWORD" pg_dump \
  --host="$DB_HOST" \
  --port="$DB_PORT" \
  --username="$DB_USER" \
  --format=custom \
  --compress=9 \
  --no-password \
  --verbose \
  "$DB_NAME" \
  > "$BACKUP_FILE" 2>>"$LOG_FILE"

DUMP_EXIT_CODE=$?
if [ "$DUMP_EXIT_CODE" -ne 0 ]; then
  log "ERROR: pg_dump exited with code ${DUMP_EXIT_CODE}"
  exit 1
fi

BACKUP_SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
log "Backup file created: ${BACKUP_FILE} (${BACKUP_SIZE})"

# Structural integrity check before upload
log "Verifying backup integrity..."
pg_restore --list "$BACKUP_FILE" > /dev/null 2>>"$LOG_FILE"
VERIFY_EXIT_CODE=$?
if [ "$VERIFY_EXIT_CODE" -ne 0 ]; then
  log "ERROR: Backup integrity check failed (exit code ${VERIFY_EXIT_CODE})"
  exit 1
fi
log "Integrity check passed"

# Upload to S3 with server-side encryption
S3_KEY="${DB_NAME}/${TIMESTAMP}/${DB_NAME}_${TIMESTAMP}.dump"
log "Uploading to s3://${S3_BUCKET}/${S3_KEY}"

aws s3 cp "$BACKUP_FILE" "s3://${S3_BUCKET}/${S3_KEY}" \
  --sse aws:kms \
  --metadata "db=${DB_NAME},timestamp=${TIMESTAMP},size=${BACKUP_SIZE}" \
  2>>"$LOG_FILE"

UPLOAD_EXIT_CODE=$?
if [ "$UPLOAD_EXIT_CODE" -ne 0 ]; then
  log "ERROR: S3 upload failed (exit code ${UPLOAD_EXIT_CODE})"
  exit 1
fi

# Upload log alongside backup
aws s3 cp "$LOG_FILE" "s3://${S3_BUCKET}/${DB_NAME}/${TIMESTAMP}/backup.log" \
  2>/dev/null || true

# Clean up local file after confirmed upload
rm -f "$BACKUP_FILE"
log "Local backup file removed"

# Enforce retention: delete S3 objects older than RETENTION_DAYS
log "Enforcing retention policy (${RETENTION_DAYS} days)..."
CUTOFF_DATE=$(date -d "-${RETENTION_DAYS} days" +"%Y-%m-%dT%H:%M:%SZ" 2>/dev/null \
  || date -v "-${RETENTION_DAYS}d" +"%Y-%m-%dT%H:%M:%SZ")  # macOS fallback

aws s3api list-objects-v2 \
  --bucket "$S3_BUCKET" \
  --prefix "${DB_NAME}/" \
  --query "Contents[?LastModified<='${CUTOFF_DATE}'].Key" \
  --output text \
| tr '\t' '\n' \
| while read -r key; do
    [ -z "$key" ] && continue
    log "Deleting old backup: ${key}"
    aws s3 rm "s3://${S3_BUCKET}/${key}"
  done

log "Backup completed successfully: s3://${S3_BUCKET}/${S3_KEY}"

Three things this script does that most backup scripts skip:

  1. pg_restore --list runs before upload. If the dump is corrupt, you find out immediately — not during an incident.
  2. Server-side encryption with KMS on every upload. Not optional if you handle user data.
  3. The local file is deleted only after confirmed S3 upload, not unconditionally at the end.

The restore script

The backup script is half the story. The restore script is where most teams have no tested procedure at all.

#!/usr/bin/env bash
# scripts/restore-db.sh
set -euo pipefail

TARGET_DB="${TARGET_DB:?TARGET_DB is required}"
DB_HOST="${DB_HOST:-localhost}"
DB_PORT="${DB_PORT:-5432}"
DB_USER="${DB_USER:?DB_USER is required}"
S3_BACKUP_URI="${S3_BACKUP_URI:?S3_BACKUP_URI required (s3://bucket/key)}"
RESTORE_DIR="/tmp/db-restore-$$"

mkdir -p "$RESTORE_DIR"
trap 'rm -rf "$RESTORE_DIR"' EXIT

log() { echo "[$(date -u +"%Y-%m-%dT%H:%M:%SZ")] $*"; }

log "Downloading backup from ${S3_BACKUP_URI}"
BACKUP_FILE="${RESTORE_DIR}/restore.dump"
aws s3 cp "$S3_BACKUP_URI" "$BACKUP_FILE"

log "Verifying downloaded backup..."
pg_restore --list "$BACKUP_FILE" > /dev/null

# Safety check: refuse to overwrite production DB name unless explicitly forced
if [[ "$TARGET_DB" == *"prod"* || "$TARGET_DB" == *"production"* ]]; then
  if [ "${FORCE_PRODUCTION:-false}" != "true" ]; then
    log "ERROR: TARGET_DB looks like a production database."
    log "Set FORCE_PRODUCTION=true to override this safety check."
    exit 1
  fi
  log "WARNING: Restoring to production database (FORCE_PRODUCTION=true)"
fi

log "Dropping and recreating database: ${TARGET_DB}"
PGPASSWORD="$DB_PASSWORD" psql \
  --host="$DB_HOST" \
  --port="$DB_PORT" \
  --username="$DB_USER" \
  --dbname=postgres \
  --command="DROP DATABASE IF EXISTS \"${TARGET_DB}\" WITH (FORCE);"

PGPASSWORD="$DB_PASSWORD" psql \
  --host="$DB_HOST" \
  --port="$DB_PORT" \
  --username="$DB_USER" \
  --dbname=postgres \
  --command="CREATE DATABASE \"${TARGET_DB}\";"

log "Restoring into ${TARGET_DB}..."
START_TIME=$(date +%s)

PGPASSWORD="$DB_PASSWORD" pg_restore \
  --host="$DB_HOST" \
  --port="$DB_PORT" \
  --username="$DB_USER" \
  --dbname="$TARGET_DB" \
  --jobs=4 \           # parallel restore — significant speedup for large DBs
  --no-owner \         # don't fail on ownership differences
  --no-privileges \    # don't try to restore GRANTs to non-existent roles
  --verbose \
  "$BACKUP_FILE"

RESTORE_EXIT_CODE=$?
END_TIME=$(date +%s)
ELAPSED=$(( END_TIME - START_TIME ))

if [ "$RESTORE_EXIT_CODE" -ne 0 ]; then
  log "ERROR: pg_restore exited with code ${RESTORE_EXIT_CODE}"
  exit 1
fi

log "Restore completed in ${ELAPSED} seconds"

The --jobs=4 flag on pg_restore is something many engineers miss. For large databases, parallel restore can cut restore time by 60-70%. It only works with custom-format backups, which is one reason I always use --format=custom for pg_dump.

Testing restore: the part that actually matters

A restore test should be a scheduled, automated, unattended process — not something that only runs when someone remembers to do it manually.

#!/usr/bin/env bash
# scripts/test-restore.sh
# Run this on a schedule against a dedicated test environment
set -euo pipefail

SOURCE_DB="$1"          # production DB name
S3_BUCKET="$2"          # backup bucket
TEST_DB="${SOURCE_DB}_restore_test"
SLACK_WEBHOOK="${SLACK_WEBHOOK:-}"

log() { echo "[$(date -u +"%Y-%m-%dT%H:%M:%SZ")] $*"; }

notify() {
  local STATUS="$1"
  local MESSAGE="$2"
  if [ -n "$SLACK_WEBHOOK" ]; then
    curl -s -X POST "$SLACK_WEBHOOK" \
      -H "Content-Type: application/json" \
      -d "{\"text\": \"[DB Restore Test] ${STATUS}: ${MESSAGE}\"}" \
      > /dev/null
  fi
  log "${STATUS}: ${MESSAGE}"
}

# Find the most recent backup for this database
LATEST_KEY=$(aws s3api list-objects-v2 \
  --bucket "$S3_BUCKET" \
  --prefix "${SOURCE_DB}/" \
  --query "sort_by(Contents, &LastModified)[-1].Key" \
  --output text)

if [ -z "$LATEST_KEY" ] || [ "$LATEST_KEY" == "None" ]; then
  notify "FAIL" "No backup found for ${SOURCE_DB} in s3://${S3_BUCKET}"
  exit 1
fi

BACKUP_AGE_HOURS=$(( ( $(date +%s) - $(aws s3api head-object \
  --bucket "$S3_BUCKET" \
  --key "$LATEST_KEY" \
  --query "LastModified" \
  --output text | xargs date +%s -d) ) / 3600 ))

if [ "$BACKUP_AGE_HOURS" -gt 26 ]; then
  notify "WARN" "Latest backup for ${SOURCE_DB} is ${BACKUP_AGE_HOURS}h old — expected <26h"
fi

log "Testing restore from s3://${S3_BUCKET}/${LATEST_KEY} (${BACKUP_AGE_HOURS}h old)"

# Restore into the test database
TARGET_DB="$TEST_DB" \
DB_HOST="$TEST_DB_HOST" \
DB_USER="$DB_USER" \
DB_PASSWORD="$DB_PASSWORD" \
S3_BACKUP_URI="s3://${S3_BUCKET}/${LATEST_KEY}" \
  bash "$(dirname "$0")/restore-db.sh"

# Post-restore verification: row counts on critical tables
log "Running post-restore sanity checks..."
FAILURES=0

check_table() {
  local TABLE="$1"
  local MIN_ROWS="$2"
  local COUNT
  COUNT=$(PGPASSWORD="$DB_PASSWORD" psql \
    --host="$TEST_DB_HOST" \
    --username="$DB_USER" \
    --dbname="$TEST_DB" \
    --tuples-only \
    --command="SELECT COUNT(*) FROM ${TABLE};" | tr -d ' ')

  if [ "$COUNT" -lt "$MIN_ROWS" ]; then
    log "FAIL: ${TABLE} has ${COUNT} rows, expected >= ${MIN_ROWS}"
    FAILURES=$(( FAILURES + 1 ))
  else
    log "OK: ${TABLE} = ${COUNT} rows"
  fi
}

# Adjust these to your actual tables and expected minimums
check_table "users" 1000
check_table "orders" 5000
check_table "products" 50

if [ "$FAILURES" -gt 0 ]; then
  notify "FAIL" "Restore test failed: ${FAILURES} sanity check(s) failed for ${SOURCE_DB}"
  exit 1
fi

notify "OK" "Restore test passed for ${SOURCE_DB} — backup age: ${BACKUP_AGE_HOURS}h, all row count checks passed"

The row count checks are deliberately coarse. They are not checking data integrity — they are checking that the restore is not silently empty or catastrophically incomplete. Think of them as a smoke test: fast, cheap, and good enough to catch the most common failure modes.

Scheduling with cron

# /etc/cron.d/db-backup
# Backup at 01:00 UTC every day
0 1 * * * postgres DB_NAME=myapp DB_USER=backup_user DB_PASSWORD=secret S3_BUCKET=mycompany-db-backups /opt/scripts/backup-db.sh >> /var/log/db-backup.log 2>&1

# Restore test at 03:00 UTC every Monday
0 3 * * 1 postgres /opt/scripts/test-restore.sh myapp mycompany-db-backups >> /var/log/restore-test.log 2>&1

Two things about this schedule: backup runs at 01:00, and restore test runs at 03:00 the same day (Monday). That gives the test two hours to run after a fresh backup has landed. If the restore test runs before the backup completes, you end up testing last week’s backup, which masks any issues introduced this week.

The IAM policy for the backup user

Do not give your backup process full S3 access. Lock it down:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "BackupWrite",
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:ListBucket",
        "s3:DeleteObject"
      ],
      "Resource": [
        "arn:aws:s3:::mycompany-db-backups",
        "arn:aws:s3:::mycompany-db-backups/*"
      ]
    },
    {
      "Sid": "KMSEncryption",
      "Effect": "Allow",
      "Action": ["kms:GenerateDataKey", "kms:Decrypt"],
      "Resource": "arn:aws:kms:ap-southeast-1:123456789012:key/your-key-id"
    }
  ]
}

Separate the backup IAM role from the restore IAM role. Backup only needs PutObject. Restore needs GetObject. Neither should have DeleteBucket or any cross-account permissions unless you explicitly need cross-account restore.

What to monitor

Three signals that indicate backup health without requiring you to look at every log line:

Backup age alert. Alert if no successful backup exists for a given database within the last 26 hours. This catches backup job failures, permission errors, and silent skips due to cron misconfiguration.

Backup size anomaly. If today’s backup is more than 30% smaller than yesterday’s, something is wrong — either data was deleted, or the dump is truncated. A simple comparison in the backup script or a CloudWatch metric on the S3 object size catches this.

Restore test result. The scheduled restore test should push a metric or send a notification on both success and failure. Success is data too — a successful restore test three days ago is meaningful context during an incident.

A note on what this does not cover

This tutorial covers pg_dump-based logical backups for databases up to roughly 200-300 GB. For larger databases, the performance characteristics change meaningfully: a 500 GB dump takes hours to produce and hours to restore, which will likely breach any reasonable recovery time objective.

At that scale, investigate pgBackRest with WAL archiving for incremental physical backups and Point-in-Time Recovery, or Barman if you prefer a more opinionated tool. The concepts are the same — automated testing of restore is still the most important part — but the tooling is different.

The most dangerous assumption in database operations is that a backup that ran successfully will restore successfully. They are separate outcomes, and only testing the restore proves the second one.

Ditulis oleh Reza Pradipta