karawaci.kode

2026-06-27 · 8 min

Database Migration Zero-Downtime Postgres 150GB

Tiga bulan lalu, klien SaaS akuntansi UMKM saya minta upgrade Postgres 14 ke 17. Database production 150GB, ~12.000 tenant aktif, ~2k QPS peak. Kontrak SLA mereka: 99.9% uptime bulanan (max ~43 menit downtime/bulan). Migrasi naive via pg_upgrade butuh estimasi 35-50 menit downtime untuk database size segini. Tidak boleh.

Rencana: logical replication, dual-write window 14 hari, cutover 90 detik di tengah malam.

Setup awal

  • Source: Postgres 14.10 di Hetzner CCX33 (8 vCPU dedicated, 32GB RAM, 320GB NVMe).
  • Target: Postgres 17.2 di Hetzner CCX43 (16 vCPU dedicated, 64GB RAM, 600GB NVMe).
  • Data size: 152GB total, 89 tabel, 14 schema (per-tenant logical isolation).
  • Tabel terbesar: transactions (52GB, 380M rows), audit_log (38GB, 920M rows).
  • Indexes: 247 total, 12 partial, 4 BRIN.

Kenapa upgrade? Postgres 17 punya MERGE ... RETURNING, incremental backup native (lihat catatan saya di pg_basebackup incremental), dan logical replication slot failover. Worth the migrate.

Strategi: logical replication + dual-write

Steps:

  1. Pre-flight: snapshot baseline, enable wal_level = logical di source (sudah dari awal).
  2. Initial sync: pg_dump schema + COPY data per tabel ke target via custom script.
  3. Replication slot: setup CREATE PUBLICATION di source, CREATE SUBSCRIPTION di target.
  4. Catch-up: monitor lag sampai stabil < 1 detik.
  5. Dual-write window: app baca dari source, tulis ke source. Replication tetap jalan. Verify checksum periodik.
  6. Cutover: pause writes (~15 detik via app-level lock), verify lag = 0, flip connection string, resume.
  7. Decommission source: 7 hari grace period, lalu shutdown.

Initial sync: 6 jam 40 menit

pg_dump schema-only:

pg_dump -h source -U replicator -s -f schema.sql kami_prod
psql -h target -U postgres -f schema.sql kami_prod

Untuk data, saya tidak pakai pg_dump | psql pipeline (single-threaded, slow). Saya pakai parallel COPY:

# 8 parallel workers, satu per tabel besar
for table in transactions audit_log invoices customers items; do
  pg_dump -h source -U replicator -t $table --data-only --format=custom kami_prod \
    | pg_restore -h target -U postgres -d kami_prod --jobs=4 &
done
wait

Throughput observed: ~6.4 GB/menit (network bottleneck antar data center Hetzner Helsinki ↔ Falkenstein, ~12ms RTT).

Total initial sync: 6 jam 40 menit. Saya jalankan Jumat malam 22:00, beres Sabtu pagi 04:40.

Setup replication

Source (Postgres 14):

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET max_wal_senders = 10;
-- Restart needed
SELECT pg_reload_conf();

CREATE PUBLICATION kami_pub FOR ALL TABLES;

Target (Postgres 17):

CREATE SUBSCRIPTION kami_sub
  CONNECTION 'host=source dbname=kami_prod user=replicator password=...'
  PUBLICATION kami_pub
  WITH (copy_data = false, create_slot = true);

copy_data = false karena saya sudah copy manual via parallel COPY. Subscription langsung mulai apply WAL dari LSN saat slot dibuat.

Catch-up & monitoring

Saya tulis monitor lag tiap 30 detik:

SELECT 
  application_name,
  pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes,
  EXTRACT(EPOCH FROM (now() - reply_time)) AS lag_seconds
FROM pg_stat_replication;

Selama 6 jam pertama: lag fluktuasi 2-15 detik, normal untuk catch-up phase. Setelah 8 jam: lag stable < 800ms p95.

Threshold yang saya pakai untuk go/no-go:

  • Lag p95 < 1.5 detik selama 24 jam berturut-turut: OK lanjut dual-write.
  • Lag p99 spike > 30 detik > 3x dalam 1 jam: stop, investigate.

Dual-write window: 14 hari

Selama 14 hari, app baca-tulis tetap ke source. Replication continuous. Saya jalankan checksum harian:

-- Di source dan target, compare row count + checksum per tabel
SELECT 
  count(*) AS rows,
  md5(string_agg(t::text, ',' ORDER BY id)) AS checksum
FROM transactions
WHERE updated_at < now() - interval '5 minutes';

5 menit window untuk hindari false positive dari replication lag.

Drift detected: hari ke-3. Tabel audit_log di target kekurangan 1.247 row. Investigasi: ada satu trigger di source yang INSERT ke audit_log tapi tidak di-replikasi karena… saya lupa: audit_log di-define dengan UNLOGGED. Unlogged table tidak di-WAL, tidak di-replicate.

Fix: alter ke logged:

ALTER TABLE audit_log SET LOGGED;

Operation ini lock tabel ~4 menit (rewrite). Saya jalankan jam 03:00 WIB Minggu, traffic minimum. Setelah itu re-sync via COPY ulang, drift hilang.

Pelajaran: audit semua tabel UNLOGGED, TEMP, dan tabel dengan INHERITS sebelum migrate. Logical replication tidak handle ini transparan.

Cutover: 90 detik

Hari H, Selasa 02:15 WIB (traffic terendah ~120 RPS). Maintenance window announced 7 hari sebelumnya.

02:14:30 — Enable maintenance mode di app (read-only banner)
02:14:45 — Pause write workers via Redis flag (BLPOP timeout, drain queue)
02:15:00 — Verify lag = 0:
            SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) 
            FROM pg_stat_replication;
            -- Output: 0
02:15:08 — Sync sequences manual:
            SELECT setval(pg_get_serial_sequence(...), ...) for all sequences
            (logical replication TIDAK sync sequence)
02:15:42 — Update PgBouncer config, reload:
            pgbouncer -R
02:15:58 — Smoke test: SELECT 1 from app via new connection
02:16:00 — Disable maintenance mode

Total app-level write pause: 90 detik. User-perceived latency spike: ~95th percentile dari 35ms ke ~600ms selama window itu (queue depth). No errors, no failed transactions.

Sequence sync — yang hampir bikin saya kena

Logical replication tidak replicate sequence values. Kalau saya cutover dan langsung WRITE ke target tanpa sync sequence, saya akan dapat duplicate key error karena sequence di target masih di nilai awal (1, 2, 3…) sementara source sudah di puluhan juta.

Script sync sequence yang saya pakai:

DO $$
DECLARE
  seq_record RECORD;
  max_val BIGINT;
BEGIN
  FOR seq_record IN
    SELECT schemaname, sequencename, 
           (SELECT a.attname FROM pg_attribute a 
            JOIN pg_depend d ON d.refobjid = a.attrelid AND d.refobjsubid = a.attnum
            WHERE d.objid = (schemaname||'.'||sequencename)::regclass
            LIMIT 1) AS col,
           (SELECT relname FROM pg_class WHERE oid = (
             SELECT refobjid FROM pg_depend 
             WHERE objid = (schemaname||'.'||sequencename)::regclass
             LIMIT 1
           )) AS tbl
    FROM pg_sequences
  LOOP
    EXECUTE format('SELECT max(%I) FROM %I.%I', 
                   seq_record.col, seq_record.schemaname, seq_record.tbl) 
    INTO max_val;
    
    IF max_val IS NOT NULL THEN
      EXECUTE format('SELECT setval(%L, %s)', 
                     seq_record.schemaname||'.'||seq_record.sequencename, 
                     max_val + 1);
    END IF;
  END LOOP;
END $$;

Run di target setelah lag = 0, sebelum flip connection string. 34 detik untuk 184 sequence.

Yang break

  1. PgBouncer prepared statement cache: setelah cutover, PgBouncer dengan pool_mode = transaction punya cached prepared statement plan dari Postgres 14. Postgres 17 punya plan slightly berbeda. Manifest: 2 menit pertama, ~8% query error “prepared statement does not exist”. Fix: pgbouncer -R (reload) yang reset prepared statement cache. Sekarang saya tambahkan ke runbook.

  2. pg_stat_statements reset: extension stats hilang di target. Bukan critical, tapi monitoring dashboard kosong selama 24 jam. Workaround: pre-load extension di target sebelum cutover.

  3. Vacuum freeze backlog: target Postgres baru di-load 150GB tapi belum pernah autovacuum freeze. Hari ke-3 post-cutover, autovacuum kick massive freeze, CPU 70% selama 40 menit, latency p99 naik dari 35ms ke 180ms. Fix retrospektif: setelah initial sync, jalankan VACUUM (FREEZE, ANALYZE) tabel_besar manual selama maintenance window.

Verdict

Logical replication + dual-write itu pendekatan paling aman untuk Postgres migration > 100GB dengan SLA tight. Trade-off: 14 hari engineering time untuk monitor dan verify, vs. 30-50 menit downtime untuk pg_upgrade.

Untuk SaaS dengan paying customer dan SLA kontrak: worth it. Untuk side project pribadi: probably overkill, pg_upgrade cukup.

Catatan: saya tidak pakai tool seperti pgcopydb atau Bucardo karena database size masih bisa di-handle dengan tooling native. Untuk > 1TB atau cross-cloud migration, saya akan reach for pgcopydb. Untuk konteks ops setup awal lihat juga systemd VPS deployment.

Ditulis oleh Reza Pradipta