Belajar Database Administrator - Backup & Recovery

#Pendahuluan

Di episode 4 kita mencegah masalah di desain; sekarang kita melindungi dari bencana: backup & recovery. Ini mungkin tanggung jawab paling sakral seorang DBA. Developer bisa salah, aplikasi bisa crash, server bisa terbakar — tetapi selama data bisa dipulihkan, kalian punya cerita. Tanpa recovery, perusahaan kehilangan aset paling berharganya.

Ada satu kalimat yang wajib kalian hafal sebagai DBA: backup yang tidak pernah di-restore bukanlah backup — hanya data yang mengisi disk. Seluruh episode ini mengarah ke satu hal: kalian harus bisa membuktikan bahwa restore benar-benar berhasil, berulang kali.

#Logical vs Physical Backup

Dua pendekatan fundamental dengan trade-off berbeda:

Aspek	Logical (`pg_dump`/`mysqldump`)	Physical (file-level / snapshot)
Isi	SQL statements / data terstruktur	Byte-for-byte salinan data + WAL
Fleksibilitas	Restore ke versi lain, pilih tabel	Harus versi sama, restore semua
Kecepatan	Lambat di database besar	Sangat cepat
PITR	Tidak langsung (butuh cara lain)	Ya, dengan WAL
Ukuran	Lebih kecil (tanpa index/duplikasi)	Lebih besar

Prinsip praktis: logical untuk schema & data kecil, physical + WAL untuk database besar dan PITR. Di produksi yang serius, kalian butuh keduanya.

#Logical Backup: pg_dump & mysqldump

pg_dump menghasilkan file SQL yang bisa diputar ulang:

pg_dump lengkap dengan schema + data

pg_dump -h 127.0.0.1 -U app_user -d appdb \
    --format=custom --file=/backup/appdb.dump

Format custom wajib dipakai: ia terkompresi, dan restore bisa parsial dengan pg_restore. Untuk migrasi lintas versi, format plain (-Fp) lebih portabel.

Di MySQL:

mysqldump dengan opsi produksi

mysqldump --single-transaction --routines --triggers \
    --set-gtid-purged=OFF -u app_user -p appdb > /backup/appdb.sql

--single-transaction menghasilkan snapshot konsisten tanpa mengunci tabel (InnoDB). Untuk kecepatan restore besar, gunakan gzip atau zstd.

#Physical Backup & PITR dengan WAL

Inilah cara "serius" untuk database besar. Konsep dari episode 2 terpakai penuh: full base backup + arsip WAL berkelanjutan = restore ke titik waktu mana pun.

Langkah di PostgreSQL:

Aktifkan archiving WAL di postgresql.conf:

postgresql.conf

archive_mode = on
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'

Buat base backup saat database aktif (tanpa downtime) memakai pg_basebackup:

pg_basebackup ke server standby

sudo -u postgres pg_basebackup -h 127.0.0.1 -D /backup/base/ \
    -U backup_user -Ft -z -P

Untuk restore ke titik waktu T, kombinasikan base backup + WAL archive hingga T:

Persiapkan direktori recovery

sudo -u postgres pg_basebackup -h 127.0.0.1 -D /var/lib/postgresql/16/main/ -R
echo "recovery_target_time = '2026-08-16 14:30:00'" \
    >> /var/lib/postgresql/16/main/recovery.conf

pg_basebackup juga menjadi dasar untuk membuat standby server (replikasi) — kita pakai lagi di episode 8. Satu mekanisme, dua tujuan.

#Menguji Restore: Budaya Drill

Pendekatan yang benar: restore bukan acara, melainkan kebiasaan rutin. Yang harus kalian latih:

Restore base backup ke server kosong, jalankan recovery dengan WAL sampai titik waktu tertentu.
Verifikasi isi: hitung baris tabel kunci, bandingkan checksum, jalankan query sanity.
Catat berapa lama restore — inilah angka RTO kalian di episode 15.
Jadwalkan drill bulanan dan dokumentasikan hasilnya.

Contoh drill restore PostgreSQL

# server kosong (new instance)
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
pg_restore -d appdb_restore /backup/appdb.dump
psql -d appdb_restore -c "SELECT count(*) FROM orders;"

Jika hasil count(*) tidak cocok dengan produksi saat backup diambil, drill gagal — dan lebih baik gagal di bulan Maret daripada saat CEO membutuhkan data.

#Otomasi Backup

Backup manual tidak pernah berjalan konsisten. Otomasi dengan cron:

Crontab backup harian

# Daily logical backup 02:00
0 2 * * * pg_dump -h 127.0.0.1 -U app_user -d appdb \
    --format=custom -f /backup/daily/appdb-$(date +\%F).dump
# Monthly base backup 03:00 tiap tanggal 1
0 3 1 * * sudo -u postgres pg_basebackup -h 127.0.0.1 \
    -D /backup/base/monthly-$(date +\%Y\%m) -Ft -z

Sertakan retensi: simpan harian 7-14 hari, mingguan 8 minggu, bulanan 12 bulan. Backup yang menumpuk tanpa retensi sama bahayanya dengan tanpa backup — disk penuh menjatuhkan server.

Warning

Backup paling umum gagal karena tiga hal: (1) tidak pernah di-test restore, (2) tidak ada retensi sehingga disk penuh, dan (3) backup disimpan di server yang sama dengan database — satu bencana menghapus keduanya. Selalu simpan salinan backup di lokasi berbeda (bisa disinkronkan ke object storage seperti episode 23).

#Pitfall Umum

pg_dump tanpa --format=custom: restore parsial jadi sulit dan file tidak terkompresi.
Melupakan archive_mode: kalau archiving mati, PITR tidak mungkin — walau base backup ada, data di antara dua base backup hilang.
Menguji restore hanya sekali saat setup: konfigurasi berubah, versi naik, WAL beralih — drill bulanan adalah satu-satunya penjamin.
mysqldump tanpa --single-transaction: backup mengunci tabel, downtime mengejutkan di jam kerja.
Backup di disk yang sama: bakar satu mesin, musnah semuanya.

#Penutup

Inti yang harus dibawa pulang:

Logical (pg_dump/mysqldump) untuk fleksibilitas; physical + WAL archive untuk PITR di database besar.
PITR = base backup + arsip WAL berkelanjutan; pg_basebackup juga bahan replikasi.
Drill restore adalah budaya: jadwalkan, verifikasi, catat RTO.
Otomasi backup + retensi + lokasi terpisah — tiga pilar yang mencegah "backup palsu".

Di episode 6 selanjutnya kita masuk ke performance tuning & query optimization — membaca EXPLAIN ANALYZE, memahami query plan dari sequential scan sampai index scan, mengenali parameter bufer yang perlu digeser, dan langkah sistematis mengoptimasi query lambat. Backup melindungi data; tuning menjaga kecepatannya. Sampai jumpa di episode 6!