Kepiye carane nggumantake lan mbalekake PostgreSQL?

Question

Accepted Answer

PostgreSQL nawakake sawetara metode **backup** — backup logis (`pg_dump`) lan backup fisik (base backup + WAL kanggo **point-in-time recovery**). Strategi backup-lan-pemulihan sing kokoh iku penting banget kanggo nglindungi saka kehilangan data, lan sing paling penting, **backup kudu diuji**.

## Backup logis — pg_dump

```bash
# pg_dump — dumps a database as SQL (or a custom archive) — portable, flexible
pg_dump -U user -d mydb -F c -f backup.dump      # custom format (compressed, flexible restore)
pg_dump -U user -d mydb > backup.sql              # plain SQL
pg_dumpall > all.sql                              # ALL databases + roles

# restore
pg_restore -U user -d mydb backup.dump            # from custom format
psql -U user -d mydb -f backup.sql                # from plain SQL
```

`pg_dump` nggawe backup logis (SQL utawa archive) saka database — portabel (bisa dikembalikan menyang versi/server sing beda-beda), apik kanggo database sing luwih cilik, migrasi, lan backup selektif. Nanging iku snapshot point-in-time (ora ana recovery sing berkelanjutan).

## Backup fisik + Point-in-Time Recovery (PITR)

```text
For larger/critical databases: a BASE BACKUP (a copy of the data files) + continuous
archiving of the WAL (write-ahead log) → enables POINT-IN-TIME RECOVERY:
  ✓ Restore the base backup, then REPLAY WAL up to ANY point in time
  ✓ Recover to JUST BEFORE a disaster (e.g. an accidental DROP TABLE at 2:47pm)
→ Tools: pg_basebackup, WAL archiving, and tools like pgBackRest / Barman automate this.
```

**PITR** iku kuat: kanthi ngrewang WAL terus-terusan, sampeyan bisa pulih menyang detik apa wae — dudu mung backup paling anyar — sing penting banget kanggo ngurangi kehilangan data saka musibah.

## Esensi strategi backup

```text
✓ Regular, AUTOMATED backups (scheduled, not manual/forgotten)
✓ Store backups OFF the database server (and offsite / cloud) — survive hardware failure
✓ Retention policy (keep daily/weekly/monthly as needed)
✓ Combine: periodic base backups + continuous WAL for PITR (critical data)
✓ Backups from a REPLICA to avoid loading the primary
```

## Aturan paling penting: UJI backup mu

```text
⚠️ A backup you've never restored is NOT a reliable backup.
UNTESTED backups fail when you need them most (corrupt, incomplete, wrong process).
→ Regularly TEST restores to verify they actually work and you know the procedure.
```

## Apa dene pentinge

Backup lan pemulihan iku **krusial** kanggo nangani PostgreSQL kanthi tanggung jawab — nglindungi saka kehilangan data (saka kegagalan hardware, korosi, penghapusan aksidental, musibah) iku salah siji tanggung jawab paling penting kanggo database produksi, saengga mangerteni strategi backup iku ilmu penting tingkat senior.

Mangerteni metode-metode iku perlu: **`pg_dump`** (backup logis — dump SQL/archive sing portabel, apik kanggo database sing luwih cilik, migrasi, lan backup selektif/lintas-versi, nanging snapshot point-in-time mung), lan **backup fisik karo Point-in-Time Recovery (PITR)** (base backup + archiving WAL berkelanjutan, ngaktifake pemulihan menyang *detik* apa wae — sing kuat kanggo ngurangi kehilangan data, kaya pulih mangsaa sadurunge `DROP TABLE` aksidental, penting banget kanggo data kritis).

Mangerteni **esensi strategi** (backup otomatis regular, nyimpen offsite lan jauh, kebijakan retensi, gabung base backup karo WAL kanggo PITR ing data kritis, nggumantake replica kanggo backup) iku ilmu operasional penting.

Paling krusial, mangerteni yen **backup kudu diuji** — backup sing ora diuji iku ora dipercaya lan asring gagal pas butuh (korosi, data ora lengkap, proses pemulihan sing ora familiar) — iku prinsip vital, sering-sering ora dipeduleni: uji pemulihan regular-regular iku sing bikin strategi backup bener-bener dipercaya.

Kasil kehilangan data bisa katastrofal (lan pemulihan mung bisa kanthi backup sing berfungsi), lan kasil strategi sing tepat (metode backup sing sesuai, PITR kanggo kehilangan mininal, penyimpanan offsite, lan sing paling penting *diuji* pemulihan) iku sing nglindungi data organisasi, mangerteni backup lan pemulihan PostgreSQL iku ilmu kritis tingkat senior kanggo operasi database sing bertanggung jawab — tanggung jawab sing berisiko tinggi ngendi perbedaan antarane strategi teruji lan strategi sing ora diuji bisa dadi beda antarane pulih saka musibah lan kehilangan data permanent, jadine iku topik penting, relevan-teles kanggo sapa wae sing nangani database produksi.