Postgres incremental backup dengan pg_dump + WAL

Production database itu tabungan akhirat. Backup-nya wajib paranoid. Daily pg_dump aja kurang — kalau data corrupt jam 10 pagi, restore ke backup tadi malam berarti hilang 10 jam transaction. WAL archiving bikin RPO turun ke menit. Snippet ini setup nightly dump + continuous WAL ke S3.

Kode

#!/usr/bin/env bash
# /opt/backup/pg_backup_nightly.sh
# Run dari cron jam 02:00 WIB

set -euo pipefail

# Config
readonly DB_NAME="${DB_NAME:-tokopedia_prod}"
readonly DB_USER="${DB_USER:-backup_user}"
readonly DB_HOST="${DB_HOST:-localhost}"
readonly BACKUP_DIR="${BACKUP_DIR:-/var/backups/postgres}"
readonly S3_BUCKET="${S3_BUCKET:-s3://bcp-backup-jakarta}"
readonly RETENTION_DAYS="${RETENTION_DAYS:-30}"

readonly TIMESTAMP=$(date -u +"%Y%m%dT%H%M%SZ")
readonly DUMP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump"
readonly LOG_FILE="${BACKUP_DIR}/backup_${TIMESTAMP}.log"

mkdir -p "${BACKUP_DIR}"

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

cleanup_local() {
  log "Cleanup file lokal lebih tua dari ${RETENTION_DAYS} hari"
  find "${BACKUP_DIR}" -name "${DB_NAME}_*.dump" -mtime "+${RETENTION_DAYS}" -delete
  find "${BACKUP_DIR}" -name "backup_*.log" -mtime "+${RETENTION_DAYS}" -delete
}

main() {
  log "=== Mulai backup ${DB_NAME} ==="

  # Custom format (-Fc) lebih cepat & support pg_restore parallel
  if ! pg_dump \
      --host="${DB_HOST}" \
      --username="${DB_USER}" \
      --format=custom \
      --compress=6 \
      --no-owner \
      --no-privileges \
      --jobs=1 \
      --file="${DUMP_FILE}" \
      "${DB_NAME}"; then
    log "ERROR: pg_dump gagal"
    return 1
  fi

  local size
  size=$(du -h "${DUMP_FILE}" | awk '{print $1}')
  log "Dump selesai: ${DUMP_FILE} (${size})"

  # Verify dump bisa di-list (sanity check, tidak full restore)
  if ! pg_restore --list "${DUMP_FILE}" > /dev/null; then
    log "ERROR: dump file corrupt, tidak bisa di-list"
    return 1
  fi
  log "Verifikasi dump OK"

  # Upload ke S3 dengan SSE-KMS
  log "Upload ke ${S3_BUCKET}"
  aws s3 cp "${DUMP_FILE}" "${S3_BUCKET}/dump/${TIMESTAMP}.dump" \
    --storage-class STANDARD_IA \
    --sse aws:kms

  cleanup_local
  log "=== Backup ${DB_NAME} selesai ==="
}

main "$@"

#!/usr/bin/env bash
# /etc/postgresql/15/main/archive_wal.sh
# Dipanggil otomatis oleh Postgres untuk archive WAL ke S3
# Setting di postgresql.conf:
#   wal_level = replica
#   archive_mode = on
#   archive_command = '/etc/postgresql/15/main/archive_wal.sh %p %f'

set -euo pipefail

readonly WAL_PATH="$1"   # %p — path lengkap WAL file
readonly WAL_NAME="$2"   # %f — nama WAL file
readonly S3_BUCKET="${WAL_S3_BUCKET:-s3://bcp-backup-jakarta}"

# Postgres expect exit code 0 = success.
# WAL boleh diretry oleh Postgres kalau fail.
exec aws s3 cp "${WAL_PATH}" "${S3_BUCKET}/wal/${WAL_NAME}" \
  --storage-class STANDARD \
  --sse aws:kms \
  --quiet

# /etc/cron.d/pg_backup — schedule cron
# Daily dump jam 02:00 WIB (UTC+7) = 19:00 UTC sebelumnya
0 19 * * * postgres /opt/backup/pg_backup_nightly.sh >> /var/log/pg_backup.log 2>&1

Pemakaian

# Test backup manual
sudo -u postgres /opt/backup/pg_backup_nightly.sh

# Verifikasi WAL ter-archive
aws s3 ls s3://bcp-backup-jakarta/wal/ --recursive | tail -10

# Restore base dump ke server lain
pg_restore \
  --host=staging-db.tangerang.id \
  --username=restore_user \
  --dbname=tokopedia_restore \
  --jobs=4 \
  --verbose \
  tokopedia_prod_20260618T190000Z.dump

# Point-in-time recovery — restore ke 10:30 WIB tepat
# 1. Download base backup (full directory pg_basebackup)
# 2. Set recovery.conf:
echo "restore_command = 'aws s3 cp s3://bcp-backup-jakarta/wal/%f %p'
recovery_target_time = '2026-06-18 10:30:00 WIB'
recovery_target_action = 'promote'" \
  > /var/lib/postgresql/15/main/recovery.signal

# 3. Start Postgres — replay WAL sampai target time
systemctl start postgresql

Kapan dipakai

• Production Postgres untuk fintech, e-commerce, healthcare — RPO ketat.
• Database dengan transaction valuable (pembayaran QRIS, top-up e-wallet).
• Compliance audit OJK / BPJS yang butuh history transaksi.
• Multi-tenant SaaS yang punya commit per detik.

Catatan

• set -euo pipefail — exit on error, undefined var, dan pipe failure. Wajib di production script.
• pg_restore —list verify murah — cuma baca header. Untuk paranoid, restore ke staging environment seminggu sekali.
• SSE-KMS untuk encryption at rest. Backup berisi data sensitif, jangan plaintext di S3 walaupun bucket private.
• WAL retention harus lebih panjang dari interval base backup. Kalau backup harian, WAL minimal 2 hari supaya bisa PITR ke titik kemarin.
• monitor archive_command — kalau S3 down dan WAL gak ter-archive, disk Postgres akan penuh. Setup alert di pg_stat_archiver.
• test restore real, jangan cuma backup. Backup yang gak pernah di-test = bukan backup, cuma harapan.

pg_dump cuma backup database, bukan extension config atau cluster-level role. Tambahkan pg_dumpall --globals-only untuk backup role, tablespace, dll.