Belajar SQL PostgreSQL - Backup, Replication & PgBouncer

#Pendahuluan

Selamat datang di episode 19 series Belajar SQL PostgreSQL! Kita sudah membangun database yang cepat, aman, dan scalable. Tapi ada satu pertanyaan yang menentukan nasib produksi: apa yang terjadi jika server mati, disk rusak, atau seseorang menghapus tabel secara tidak sengaja? Jawaban untuk semua itu adalah backup dan replication — dua pilar kesiapan produksi yang tidak boleh ditawar.

Backup adalah jaring pengaman terakhir: salinan data yang bisa dipulihkan kapan pun dibutuhkan. Replication adalah sistem cadangan paralel: salinan yang terus hidup dan bisa mengambil alih jika primary tumbang. Dan di balik keduanya ada satu musuh diam-diam: koneksi yang membeludak — di situlah PgBouncer masuk sebagai connection pooler.

Di episode ini, kita akan membahas strategi logical backup dengan pg_dump, pg_dumpall, dan pg_restore, physical backup dengan Write-Ahead Logging (WAL) untuk Point-In-Time Recovery (PITR), physical streaming replication dan logical replication, lalu connection pooling dengan PgBouncer untuk mencegah connection exhaustion.

#Strategi Backup & Restore Database

Ada dua pendekatan backup yang saling melengkapi: logical dan physical.

#Logical Backup: pg_dump dan pg_restore

pg_dump menghasilkan dump SQL (atau format arsip) dari satu database. Ia bekerja di level logis — struktur dan data diekspor sebagai statement SQL.

pg_dump -U postgres -h localhost -d shop -Fc -f shop.dump

Flag -Fc menghasilkan format custom (terkompresi, bisa di-restore selektif). Untuk seluruh cluster database (termasuk role dan database definitions), gunakan pg_dumpall:

pg_dumpall -U postgres -h localhost -f all.sql

Restore dengan pg_restore untuk format custom, atau pipa langsung ke psql untuk format plain SQL:

pg_restore -U postgres -h localhost -d shop --clean --if-exists shop.dump

psql -U postgres -h localhost -d shop < all.sql

#Physical Backup & WAL: Point-In-Time Recovery

Physical backup menyalin file data mentah — jauh lebih cepat untuk database besar. Kombinasi pg_basebackup + WAL memungkinkan Point-In-Time Recovery (PITR): memulihkan database ke momen tertentu, bahkan setelah sebuah DROP TABLE yang disengaja.

pg_basebackup -U postgres -h primary-host -D /backup/base -P

Write-Ahead Logging (WAL) adalah buku catatan setiap perubahan — dikirim terus menerus ke arsip WAL. Dengan base backup + WAL lengkap, kalian bisa "memutar ulang" database ke detik tertentu:

1. Base backup lengkap di waktu T0
2. Arsip WAL menerima semua perubahan setelah T0
3. Recovery: restore base backup, lalu replay WAL sampai target waktu
4. Database pulih ke momen yang diinginkan (misal 5 menit sebelum DROP TABLE)

Inilah perbedaan utama logical vs physical: logical backup = potret; physical + WAL = VCR yang bisa di-rewind.

#PostgreSQL High Availability & Replication

Replication membuat salinan database yang terus terupdate secara otomatis — pondasi high availability.

#Physical Streaming Replication

Physical streaming replication menyalin WAL secara real-time dari primary (read-write) ke standby replica (read-only). Standby identik byte demi byte dengan primary.

Primary (read-write)  ---WAL stream--->  Standby (read-only)

Setup intinya: primary punya wal_level = replica dan max_wal_senders, lalu standby dikonfigurasi via primary_conninfo dan di-bootstrap dari pg_basebackup. Jika primary tumbang, standby bisa di-promote menjadi primary baru (failover) — downtime tinggal detik.

#Logical Replication

Logical replication bekerja di level logis, per tabel, dengan model publish-subscribe: publisher mempublikasikan tabel tertentu, subscriber menerimanya. Keunggulannya:

• Replikasi per tabel atau per subset — tidak harus seluruh database.
• Primary dan subscriber bisa berbeda versi PostgreSQL.
• Subscriber bisa ditulis (tidak harus read-only) — berguna untuk agregasi multi-source.

CREATE PUBLICATION shop_pub FOR TABLE orders, order_items;

CREATE SUBSCRIPTION shop_sub
CONNECTION 'host=primary-host dbname=shop user=replicator'
PUBLICATION shop_pub;

Logical replication menjadi pilihan ketika kalian ingin data tertentu mengalir ke database lain — misal data operasional ke data warehouse atau analytics cluster.

#Connection Pooling dengan PgBouncer

PostgreSQL menangani koneksi dengan satu proses per koneksi. Setiap proses memakan beberapa megabyte memory. Saat ribuan request datang bersamaan dari aplikasi, kalian akan kehabisan memory dan koneksi — fenomena yang disebut connection exhaustion.

PgBouncer adalah connection pooler yang berdiri di antara aplikasi dan PostgreSQL. Ribuan koneksi aplikasi dipool menjadi puluhan koneksi nyata ke server:

App x5000  -->  PgBouncer  -->  PostgreSQL (50 koneksi)

[databases]
shop = host=127.0.0.1 port=5432 dbname=shop
 
[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
pool_mode = transaction
max_client_conn = 2000
default_pool_size = 50

Aplikasi kini mengarah ke port 6432 (PgBouncer), bukan 5432 (PostgreSQL). pool_mode = transaction artinya koneksi server dipinjam hanya selama satu transaksi — paling cocok untuk aplikasi web.

#Mengapa Pooling Itu Penting?

• Menghemat memory server: ribuan koneksi client diubah menjadi puluhan koneksi nyata.
• Mencegah connection exhaustion: PostgreSQL tidak lagi menerima banjir koneksi langsung.
• Membatasi koneksi aplikasi: max_client_conn dan default_pool_size memberi batas yang terukur.

#Kesalahan Umum

#Penutup

Di episode 19 ini kita sudah menyiapkan kesiapan produksi: logical backup dengan pg_dump dan pg_restore, physical backup dan WAL untuk Point-In-Time Recovery, physical streaming replication dan logical replication, serta connection pooling dengan PgBouncer untuk mencegah connection exhaustion.

Inti yang harus dibawa pulang:

• pg_dump untuk backup logis per database; pg_dumpall untuk seluruh cluster.
• Physical backup + WAL memungkinkan Point-In-Time Recovery — seperti VCR yang bisa di-rewind.
• Backup tanpa uji restore bukan backup — uji secara berkala.
• Physical replication untuk HA identik; logical replication untuk distribusi data per tabel.
• PgBouncer mengubah ribuan koneksi client menjadi puluhan koneksi server.

Di episode 20 yang terakhir, kita merangkai semua kemampuan ini menjadi satu: Studi Kasus Production-Grade E-Commerce Database — merancang schema lengkap untuk users dengan UUID dan RLS, product catalog dengan JSONB dan full-text search, inventory dan order processing dengan locking dan audit triggers, serta analytics dengan materialized view.