คุณ back up และ recover PostgreSQL อย่างไร?

Question

Accepted Answer

PostgreSQL มีวิธี **backup** หลายแบบ ทั้ง logical backup (`pg_dump`) และ physical backup (base backup + WAL สำหรับ **point-in-time recovery**) กลยุทธ์การ backup-and-recovery ที่แข็งแกร่งเป็นสิ่งจำเป็นต่อการป้องกันการสูญเสียข้อมูล และที่สำคัญคือ **backup ต้องถูกทดสอบ**

## logical backup 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` สร้าง logical backup (SQL หรือ archive) ของฐานข้อมูล พกพาได้ (restore ไปยัง version/เซิร์ฟเวอร์ที่ต่างกันได้) ดีสำหรับฐานข้อมูลขนาดเล็ก, migration และ backup แบบเลือกได้ แต่มันเป็น snapshot ณ จุดเวลาหนึ่ง (ไม่มี continuous recovery)

## physical backup + 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** ทรงพลัง: ด้วยการ archive WAL อย่างต่อเนื่อง คุณสามารถ recover ไปยังช่วงเวลาใดก็ได้ ไม่ใช่แค่ backup ครั้งล่าสุด ซึ่งจำเป็นต่อการลดการสูญเสียข้อมูลจากภัยพิบัติ

## สิ่งจำเป็นของกลยุทธ์ 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
```

## กฎที่สำคัญที่สุด: ทดสอบ backup ของคุณ

```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.
```

## ทำไมจึงสำคัญ

backup และ recovery เป็นความรู้ที่ **วิกฤต** ต่อการดำเนินการ PostgreSQL อย่างรับผิดชอบ การป้องกันการสูญเสียข้อมูล (จากฮาร์ดแวร์ล้มเหลว, การเสียหาย, การลบโดยไม่ตั้งใจ, ภัยพิบัติ) เป็นหนึ่งในความรับผิดชอบที่สำคัญที่สุดสำหรับฐานข้อมูล production ใด ๆ ดังนั้นการเข้าใจกลยุทธ์ backup จึงเป็นความรู้ระดับ senior ที่จำเป็น

การเข้าใจวิธีต่าง ๆ เป็นสิ่งจำเป็น: **`pg_dump`** (logical backup ดัมพ์ SQL/archive ที่พกพาได้ ดีสำหรับฐานข้อมูลขนาดเล็ก, migration และ backup แบบเลือกได้/cross-version แต่เป็น snapshot ณ จุดเวลาหนึ่งเท่านั้น) และ **physical backup พร้อม Point-in-Time Recovery (PITR)** (base backup + การ archive WAL อย่างต่อเนื่อง ทำให้ recover ไปยัง *ช่วงเวลาใดก็ได้* ซึ่งทรงพลังต่อการลดการสูญเสียข้อมูล เช่น การ recover ไปยังก่อน `DROP TABLE` ที่ไม่ตั้งใจ จำเป็นสำหรับข้อมูลวิกฤต)

การรู้ **สิ่งจำเป็นของกลยุทธ์** (backup อัตโนมัติเป็นประจำ, เก็บไว้นอกเซิร์ฟเวอร์และนอกสถานที่, นโยบายการเก็บรักษา, การรวม base backup กับ WAL สำหรับ PITR บนข้อมูลวิกฤต, การ backup จาก replica) เป็นความรู้เชิงปฏิบัติการที่สำคัญ

ที่วิกฤตที่สุดคือ การเข้าใจว่า **backup ต้องถูกทดสอบ** backup ที่ไม่ถูกทดสอบนั้นไม่น่าเชื่อถือและมักล้มเหลวพอดีตอนที่ต้องการ (เสียหาย, ข้อมูลไม่ครบ, กระบวนการ restore ที่ไม่คุ้นเคย) เป็นหลักการสำคัญที่มักถูกละเลย: การทดสอบ restore เป็นประจำเป็นสิ่งที่ทำให้กลยุทธ์ backup น่าเชื่อถือได้จริง

เนื่องจากการสูญเสียข้อมูลอาจเป็นหายนะ (และการ recovery ทำได้เฉพาะเมื่อมี backup ที่ใช้งานได้) และเนื่องจากกลยุทธ์ที่เหมาะสม (วิธี backup ที่เหมาะสม, PITR สำหรับการสูญเสียน้อยที่สุด, การจัดเก็บนอกสถานที่ และที่สำคัญคือ restore ที่ *ถูกทดสอบ*) เป็นสิ่งที่ปกป้องข้อมูลขององค์กร การเข้าใจ backup และ recovery ของ PostgreSQL จึงเป็นความรู้ระดับ senior ที่วิกฤตต่อการดำเนินการฐานข้อมูลอย่างรับผิดชอบ เป็นความรับผิดชอบที่มีความเสี่ยงสูงที่ความแตกต่างระหว่างกลยุทธ์ที่ถูกทดสอบกับที่ไม่ถูกทดสอบอาจเป็นความแตกต่างระหว่างการ recover จากภัยพิบัติได้กับการสูญเสียข้อมูลอย่างถาวร ทำให้เป็นหัวข้อที่จำเป็นและเกี่ยวข้องบ่อยสำหรับทุกคนที่ดำเนินการฐานข้อมูล production