Postgres 17 Replication Zero-Downtime: ERP Enterprise

Empat bulan lalu, klien enterprise ERP — manufaktur dengan 8 pabrik di Jabodetabek, ~2.400 internal user, 24/7 operation — minta upgrade cluster Postgres 14.10 ke 17.4. Bisnis mereka tidak boleh berhenti sama sekali (production line scheduling bergantung di DB). 2.8TB data warehouse + OLTP gabungan, 3 primary partition by-region, 6 replica untuk read scaling.

Project akhirnya selesai dengan total app-perceived downtime 0 detik (cuti meminta canary route saja). Share teknis dan keputusan arsitektur.

Konteks cluster

• Workload mix: 70% OLTP (order, inventory, production), 30% reporting (jam non-puncak).
• Cluster topology:
  • 3 primary (per region: Tangerang-Cikande, Tangerang-Pasarkemis, Jakarta-Sunter)
  • 2 sync replica per primary (1 same DC, 1 cross-DC)
  • PgBouncer di tiap app server (~80 app server)
• Hardware: bare metal di colo Cyber Cibubur, Dell PowerEdge R750 (2× Xeon 6338, 256GB RAM, 6× NVMe 3.84TB RAID10).
• Replication mode: synchronous_commit = remote_apply untuk OLTP path, off untuk reporting.
• Throughput: peak 4.800 TPS write, ~22k QPS read.

Kenapa upgrade 14 → 17 bukan ke 16:

• MERGE … RETURNING simplify ETL job (saving ~840 LOC stored procedure).
• Logical replication slot failover native (sebelumnya pakai custom failover script).
• Incremental backup native (lihat juga catatan saya di Postgres 17 incremental backup).
• Streaming I/O untuk sequential scan (relevan untuk reporting query).

Kami skip 16 karena: investasi migrate ke 16 baru harus diulang untuk 17 dalam 18 bulan. Loncat sekalian.

Strategi: shadow cluster + traffic mirroring

Pendekatan tradisional (pg_upgrade in-place) tidak applicable — downtime > 4 jam untuk database segini. Logical replication standalone juga tidak ideal karena workload distributed primary.

Strategi yang dipilih:

1. Build shadow cluster Postgres 17 paralel.
2. Initial sync via pg_basebackup per partition (manfaatkan incremental Postgres 17).
3. Logical replication catch-up sampai lag stabil < 2 detik.
4. Shadow traffic mirror lewat ProxySQL-like middleware — semua write ke production cluster (14), mirror async ke shadow (17), compare result periodik.
5. Verifikasi 8 minggu dengan checksum harian + workload benchmark.
6. Cutover bertahap per partition (Tangerang-Pasarkemis dulu, paling kecil — fail-safe testing).
7. Cutover sisanya setelah 2 minggu stable di partition pertama.

Phase 1: build shadow cluster (3 minggu)

Hardware identical (Dell R750 baru), software stack baru:

• Postgres 17.4
• pg_stat_statements, pg_repack, pgaudit, pgvector (mereka mulai ada use case ML), pg_partman
• Patroni 4.0 untuk HA orchestration
• PgBouncer 1.23

Konfigurasi production-relevant:

# postgresql.conf
shared_buffers = 64GB
effective_cache_size = 192GB
work_mem = 64MB
maintenance_work_mem = 4GB
max_connections = 800
max_wal_size = 32GB
checkpoint_timeout = 15min
wal_compression = zstd
wal_level = logical
max_replication_slots = 32
max_wal_senders = 32
hot_standby_feedback = on
synchronous_commit = remote_apply
synchronous_standby_names = 'ANY 1 (replica_1, replica_2)'

wal_compression = zstd baru di Postgres 17, kasih ~28% WAL size reduction di workload kami (banyak audit_log write yang compressible).

Phase 2: initial sync (4 hari per partition)

Per partition:

# Di shadow primary
pg_basebackup \
  --host=primary-old.internal \
  --username=replicator \
  --pgdata=/var/lib/postgresql/17/main \
  --wal-method=stream \
  --checkpoint=fast \
  --progress \
  --verbose \
  --max-rate=200M  # throttle bandwidth

200 MB/s throttle untuk hindari saturate network ke production primary. Pada bandwidth ini, partition Tangerang-Cikande (1.1TB) selesai dalam ~92 menit. Setelah base, WAL streaming continue otomatis.

Sequence tidak terhandle by pg_basebackup standalone untuk logical replication — sudah handle by physical streaming yang dipakai initial sync.

Phase 3: shadow traffic mirroring (8 minggu)

Yang ini paling complex secara engineering. Saya pakai ProxySQL-style middleware (built in-house — pgmirror):

App → PgBouncer → pgmirror → 
                    ├─ Production primary 14 (synchronous, blocking)
                    └─ Shadow primary 17 (async fire-and-forget, log diff)

Setiap write:

• Dieksekusi di production 14, hasil return ke app.
• Async-mirrored ke shadow 17 dengan same SQL.
• Result diff (row affected count, error code, untuk SELECT: row hash) di-log ke ClickHouse.

Daily report: %compatible_results, slow_query_diff, error_diff.

Setelah 8 minggu shadow:

• 99.997% query result identical
• 23 query dengan diff (semua karena: timezone bug di Postgres 14 yang fixed di 17, atau urutan result implicit yang tidak deterministic — bukan masalah real)
• 4 query lebih lambat di 17 (planner regression, fix dengan pg_hint_plan atau rewrite)
• 89 query lebih cepat di 17 (median improvement 18%)

Yang lebih lambat saya fix pre-cutover:

-- Slow di Postgres 17: planner pakai parallel seq scan vs index scan
SELECT /*+ IndexScan(orders idx_orders_created_at_status) */
  ...
FROM orders
WHERE created_at > $1 AND status = 'pending';

Atau:

ALTER TABLE orders SET (parallel_workers = 0);

Untuk tabel kecil yang planner over-parallelize.

Phase 4: cutover Tangerang-Pasarkemis (partition terkecil)

Partition Tangerang-Pasarkemis: 420GB, ~1.200 TPS peak.

Cutover window: Selasa 02:00-04:00 WIB (off-peak production, tidak ada produksi shift malam di pabrik ini).

02:00 — pgmirror flip mode: production becomes shadow, shadow becomes production
        (atomic flag flip in pgmirror config, all in-flight transactions complete)
02:00:14 — Verify replication lag = 0 from new primary
02:00:22 — Sync sequence (yes, also for physical replication safety):
            DO $$ ... setval ... END $$ for all 248 sequences in partition
02:01:48 — Smoke test: 50 representative query, latency check
02:02:30 — Mark Tangerang-Pasarkemis as MIGRATED in service registry
02:03:00 — Monitoring intensif 2 jam
04:00 — Deklarasi success

Total write pause selama flip: ~14 detik (in-flight transaction drain). App-perceived sebagai high latency burst, bukan error — app retry pattern handle.

Phase 5: cutover sisa partition (2 minggu kemudian)

Setelah Tangerang-Pasarkemis stable 2 minggu, lanjut Jakarta-Sunter, lalu Tangerang-Cikande. Same playbook.

Total project: 4 bulan calendar time, ~340 jam engineering time (saya + 1 DBA klien + 1 SRE klien).

Postgres 17 features yang signifikan untuk workload kami

1. Incremental backup

# Full backup Sunday
pg_basebackup -D /backup/full --format=tar --compress=zstd

# Incremental Monday-Saturday
pg_basebackup -D /backup/inc-$(date +%a) \
  --incremental=/backup/full/backup_manifest \
  --format=tar --compress=zstd

Incremental size avg: 38GB vs full 2.8TB. Backup window untuk reporting replica turun dari 4 jam ke 38 menit.

2. MERGE … RETURNING

Sebelum (Postgres 14, 2 query):

WITH updated AS (
  UPDATE inventory SET qty = qty - $1 
  WHERE sku = $2 AND qty >= $1 RETURNING *
)
INSERT INTO movements (sku, qty, type) 
SELECT sku, $1, 'OUT' FROM updated;

Setelah (Postgres 17, atomic):

MERGE INTO inventory USING (VALUES ($2, $1)) AS m(sku, qty)
  ON inventory.sku = m.sku AND inventory.qty >= m.qty
WHEN MATCHED THEN UPDATE SET qty = inventory.qty - m.qty
RETURNING merge_action() AS action, inventory.sku, m.qty;

Latency p95 untuk inventory deduction: 8.4ms → 4.2ms. Throughput peak: 1.200 TPS → 1.850 TPS.

3. Streaming I/O sequential scan

Reporting query yang sequential scan tabel besar (production_logs 480GB):

SELECT date_trunc('hour', ts), avg(cycle_time) 
FROM production_logs 
WHERE ts > now() - interval '7 days'
GROUP BY 1;

Postgres 14: 4 menit 18 detik. Postgres 17: 2 menit 38 detik.

~39% faster. Postgres 17 streaming I/O lebih efektif memanfaatkan NVMe read-ahead.

Yang break

1. Replication slot orphan setelah test

Di environment QA, saya test failover dengan kill primary. Failover OK, tapi physical replication slot dari primary lama tidak di-drop. WAL accumulate selama 4 hari, fill 600GB disk sebelum saya catch via monitoring.

Fix permanen:

ALTER SYSTEM SET max_slot_wal_keep_size = '128GB';

Plus alert kalau pg_replication_slots.active = false selama > 1 jam.

2. PgBouncer prepared statement cache cross-version

Sama seperti issue di database migration Postgres 14→17 mid-career saya, tapi 80x lebih besar (80 app server × 50 connection pool). Kami pakai PgBouncer 1.23 yang punya server_reset_query_always = 1 mode, ada bug awal yang re-introduce stale plan cache.

Fix: downgrade ke PgBouncer 1.22.1 sementara, monitor PgBouncer 1.23 bugfix release, plan upgrade kuartal berikutnya.

3. pg_partman migration plan

Cluster lama pakai pg_partman 4.x (table-based partition config). Postgres 17 lebih cocok dengan native declarative partition.

Saya tidak rewrite — terlalu risky. Pertahankan pg_partman 5.0 (yang Postgres 17 compatible) untuk sekarang, plan rewrite ke native partitioning di kuartal Q4.

4. pgaudit log volume

pgaudit di Postgres 17 sedikit lebih verbose untuk DDL audit (good for compliance). Log volume naik 22%. Self-host ELK stack kami butuh upgrade storage dari 2TB ke 3TB.

5. Patroni 4.0 ZooKeeper deprecation

Patroni 4.0 default rekomendasi etcd v3, deprecate ZooKeeper. Cluster kami masih pakai ZooKeeper (legacy). Workaround sekarang: Patroni 4.0 still support ZooKeeper, tapi planned migration ke etcd Q1 berikutnya.

Verdict

Postgres 17 worth upgrade untuk workload mixed OLTP + reporting enterprise. Performance improvement nyata, feature MERGE + incremental backup sangat impactful operationally.

Tapi: jangan migrate naive. Untuk cluster enterprise dengan SLA tight, shadow traffic mirroring 8 minggu adalah investasi yang murah relatif terhadap risk silent regression. Project ini sukses karena 8 minggu itu, bukan karena cutover yang elegant.

Bukan magic — saya hampir kelewat 4 query regression yang baru muncul di hari ke-51 shadow. Saya tidak akan rekomendasi migrate < 6 minggu shadow untuk cluster sebesar ini.

Lihat juga pattern migration zero-downtime mid-career version untuk konteks SMB.