karawaci.kode

2026-06-18 · 7 min

Postgres 17 Incremental Backup di SaaS: 30 Hari Production

Saya operate Postgres 17 untuk 3 klien SaaS Jakarta. Bulan lalu saya migrate backup strategy dari pg_dump daily ke pg_basebackup --incremental setelah Postgres 17 ship feature ini. Hasil terukur setelah 30 hari production.

Setup

DB klien terbesar:

  • Postgres 17.2 di Hetzner cx41 (4 vCPU, 16GB RAM)
  • Data size: 84GB (active tables + index)
  • WAL generation: ~3GB/hari
  • Backup target: Cloudflare R2 (Singapore region)

Sebelum: pg_dump daily ke R2.

  • Backup time: 38 menit (84GB data + compression)
  • Compressed size: 12GB per backup
  • Daily storage growth: 12GB/hari
  • 7-day retention: 84GB di R2

Migrate ke incremental

Postgres 17 introduce pg_basebackup --incremental yang record full backup once, lalu incremental backup dari WAL diff. Mirip patern Litestream tapi native di Postgres.

Setup:

# Initial full backup
pg_basebackup -D /backup/full -X stream -P -v -F t

# Daily incremental (Postgres 17)
pg_basebackup -D /backup/incr-$(date +%Y%m%d) \
  --incremental=/backup/full/backup_manifest \
  -X stream -F t

Plus WAL archiving continuous:

# postgresql.conf
archive_mode = on
archive_command = 'rclone copy %p r2:wal-archive/%f'
archive_timeout = 60

Backup time

Sebelum (pg_dump):

  • Daily: 38 menit
  • Lock impact: pg_dump tidak lock, tapi long-running query bisa hit MVCC bloat

Sesudah (incremental):

  • Initial full: 28 menit (one-time)
  • Daily incremental: 9 menit (~76% reduction)
  • WAL archive continuous: < 1 detik per segment

Saving: 29 menit per backup × 30 hari = 14,5 jam compute time hemat per bulan. Cron schedule sekarang fit di window 00:00-00:15 instead of 00:00-00:45.

Storage

Sebelum:

  • Daily backup 12GB
  • 7-day retention: 84GB
  • Monthly retention (4 weekly): 48GB tambahan
  • Total: ~132GB di R2 = $1.98/mo

Sesudah:

  • Full backup 84GB compressed → 21GB di R2
  • Daily incremental: ~2GB rata-rata
  • WAL archive: ~3GB/hari, retain 7 hari = 21GB
  • 7-day retention: 21GB (full) + 14GB (incremental) + 21GB (WAL) = 56GB
  • Total: ~56GB = $0.84/mo (~62% reduction)

Saving cost: $1.14/mo. Trivial nominal, signifikan as ratio.

Recovery test

Setiap bulan saya jadwalkan disaster recovery test (ke VPS staging terpisah). RTO target klien: 30 menit.

Sebelum (pg_dump restore):

  • Download backup dari R2: 4 menit (12GB via Hetzner-R2 link)
  • Decompress + pg_restore: 32 menit
  • Verify checksum + smoke test: 5 menit
  • Total: 41 menit

Sesudah (incremental restore):

  • Download full + latest incremental + WAL: 3 menit (smaller files parallel)
  • pg_combinebackup (merge incremental ke full state): 4 menit
  • Start Postgres + recover WAL: 3 menit
  • Verify + smoke test: 2 menit
  • Total: 12 menit

RTO improvement: 71%. Lebih nyaman untuk weekday emergency.

Yang break

  1. Backup manifest mismatch: minggu pertama saya rotate full backup tanpa update reference di incremental command. Incremental gagal silent — log only di stderr yang saya tidak monitor. 3 hari incremental hilang sebelum saya notice di weekly DR test.

Fix: monitoring dengan healthcheck.io ping. Cron failure trigger alert via Telegram.

  1. WAL archive lag saat backup: saat pg_basebackup jalan, WAL archive sempat lag karena disk I/O contention. Pernah miss 3 WAL segment (auto-recovered after backup done). Fix: tambah max_wal_senders = 10 dan wal_keep_size = 8GB supaya master tidak purge WAL terlalu cepat.

  2. R2 multipart upload limit: file > 5GB butuh multipart upload. Rclone handle automatic, tapi pernah ada partial upload yang gak terdeteksi gagal. Sekarang saya verify checksum md5 setelah upload.

  3. pg_combinebackup memory: combine full + 3 incremental butuh ~6GB RAM working set. VPS dengan 16GB OK. VPS dengan 8GB butuh swap. Catatan untuk infra planning.

Patron yang saya adopt

#!/bin/bash
# /usr/local/bin/pg-backup.sh
set -euo pipefail

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_BASE="/var/lib/postgres-backup"
R2_REMOTE="r2:postgres-backup"

# Decide: full atau incremental
WEEK_DAY=$(date +%u)
if [[ "$WEEK_DAY" -eq 7 ]]; then
  # Sunday: full backup
  pg_basebackup -D "$BACKUP_BASE/full-$DATE" -X stream -F t -P
  rclone copy "$BACKUP_BASE/full-$DATE" "$R2_REMOTE/full-$DATE"
else
  # Other days: incremental
  LATEST_FULL=$(ls -d $BACKUP_BASE/full-* | sort | tail -1)
  pg_basebackup -D "$BACKUP_BASE/incr-$DATE" \
    --incremental="$LATEST_FULL/backup_manifest" -X stream -F t
  rclone copy "$BACKUP_BASE/incr-$DATE" "$R2_REMOTE/incr-$DATE"
fi

# Cleanup local > 14 days
find "$BACKUP_BASE" -mindepth 1 -maxdepth 1 -mtime +14 -exec rm -rf {} +

# Healthcheck ping
curl -fsS "https://hc-ping.com/$HEALTHCHECK_UUID"

Cron: 0 0 * * * /usr/local/bin/pg-backup.sh.

Healthcheck.io: monitor backup success. Kalau cron gagal 1 hari, alert via Telegram chat saya.

Monitoring metrics

Saya track via Prometheus + Grafana:

  • postgres_backup_duration_seconds (gauge)
  • postgres_backup_size_bytes (gauge)
  • postgres_wal_archive_lag_seconds (gauge)
  • postgres_backup_age_seconds (gauge — alert kalau > 26 jam)

Alert via Alertmanager → Telegram. Belum pernah false positive selama 30 hari.

Cost analysis vs managed service

Klien tanya: kenapa tidak pakai managed Postgres (Neon, Supabase) yang built-in backup?

Numbers:

  • Hetzner cx41 Postgres self-managed: $25/mo
  • Hetzner backup automation (R2 + WAL archive): $0.84/mo + my time ~3 jam/bulan
  • Total: ~$26/mo + 3 jam ops time

Equivalent di Neon Pro (autosuspend Singapore): ~$45-60/mo untuk 84GB DB Equivalent di Supabase Pro: $25/mo base + storage overage = ~$32/mo

Self-managed saving: ~$15-30/mo. Plus full control (PG extension, tuning, replication topology).

For klien ini: self-managed worth. Untuk klien yang tidak ada ops bandwidth: managed worth premium.

Verdict

Postgres 17 pg_basebackup --incremental adalah upgrade meaningful untuk self-hosted Postgres di SMB scale. Saving compute + storage real. Recovery time turun signifikan.

Bukan magic — chain corruption risk nyata, monitoring wajib. Saya rekomendasi: enable, plus weekly DR test, plus healthcheck monitoring otomatis.

Pattern serupa dengan Litestream untuk SQLite small SaaS saya tulis sebelumnya: incremental + continuous archive pattern berlaku universal untuk DB self-hosted.

Untuk DB > 50GB: lakukan. Untuk DB < 10GB: marginal benefit, stay dengan pg_dump saja.

Ditulis oleh Reza Pradipta