สำรองและกู้คืน PostgreSQL ด้วย pg_dump และ pg_restore
คำตอบสั้น ๆ: สำรองฐานข้อมูล PostgreSQL ด้วย pg_dump ทั้งแบบไฟล์ SQL และแบบ Custom Format แล้วกู้คืนด้วย psql หรือ pg_restore พร้อมสำรองผู้ใช้และสิทธิ์ด้วย pg_dumpall
คู่มือนี้อธิบาย PostgreSQL ทีละขั้นตอน พร้อมคำสั่งที่ใช้ได้จริงและปัญหาที่พบบ่อย
pg_dump เป็นเครื่องมือมาตรฐานของ PostgreSQL สำหรับสำรองฐานข้อมูลทีละฐานขณะที่ระบบยังใช้งานอยู่ ผลที่ได้เป็นภาพของข้อมูล ณ เวลาที่เริ่มสำรอง ผู้ใช้อื่นยังอ่านเขียนได้ตามปกติระหว่างนั้น ส่วน pg_restore ใช้กู้คืนไฟล์ที่สำรองไว้ในรูปแบบ Custom หรือ Directory
คู่มือนี้ใช้ PostgreSQL 14 ขึ้นไปบน Ubuntu หรือ AlmaLinux/Rocky Linux ตัวอย่างฐานข้อมูลชื่อ appdb ที่มีเจ้าของเป็น Role appuser
สิ่งที่ต้องเตรียม
- สิทธิ์
sudo เพื่อทำงานในนามผู้ใช้ระบบ postgres หรือ Role ที่มีสิทธิ์อ่านทุกตารางในฐานข้อมูลที่จะสำรอง - พื้นที่ดิสก์ว่างเพียงพอ ตรวจด้วย
df -h - ตรวจเวอร์ชันเครื่องมือด้วย
pg_dump --version และเวอร์ชันเซิร์ฟเวอร์ด้วย sudo -u postgres psql -c "SELECT version();"
รูปแบบไฟล์สำรองที่ควรเลือก
| รูปแบบ | ตัวเลือก | กู้คืนด้วย | เหมาะกับ |
|---|
| Plain SQL | (ค่าเริ่มต้น) | psql | ไฟล์ข้อความที่อ่านและแก้ไขได้ ย้ายข้ามเวอร์ชันง่าย |
| Custom | -Fc | pg_restore | บีบอัดในตัว เลือกกู้บางตารางได้ แนะนำสำหรับงานสำรองประจำ |
| Directory | -Fd | pg_restore | ฐานข้อมูลใหญ่ สำรองแบบขนานด้วย -j ได้ |
ขั้นตอนที่ 1: สำรองฐานข้อมูล
สำรองแบบ Custom Format (แนะนำ)
sudo -u postgres pg_dump -Fc appdb > ~/appdb-2026-09-19.dump
การใช้ > ทำให้ไฟล์ถูกเขียนด้วยสิทธิ์ของผู้ใช้ปัจจุบัน จึงบันทึกลงโฮมของคุณได้ แม้ผู้ใช้ postgres จะไม่มีสิทธิ์เขียนโฟลเดอร์นั้น คำสั่งจะไม่แสดงผลอะไรหากสำเร็จ ถ้าเห็นคำเตือน could not change directory to "/home/..." ไม่ต้องกังวล เป็นเพียงการแจ้งว่า postgres เข้าโฟลเดอร์ปัจจุบันไม่ได้ และไม่มีผลต่อไฟล์สำรอง
สำรองแบบ Plain SQL และบีบอัด
sudo -u postgres pg_dump appdb | gzip > ~/appdb-2026-09-19.sql.gz
สำรองจากเครื่องอื่นหรือด้วย Role ที่ใช้รหัสผ่าน
pg_dump -h 127.0.0.1 -U appuser -d appdb -Fc -f ~/appdb-2026-09-19.dump
ขั้นตอนที่ 2: สำรอง Role และสิทธิ์ส่วนกลาง
pg_dump สำรองเฉพาะข้อมูลภายในฐานข้อมูล ไม่รวม Role (ผู้ใช้) และรหัสผ่าน เมื่อย้ายไปเซิร์ฟเวอร์ใหม่จึงควรสำรองส่วนนี้แยกด้วย
sudo -u postgres pg_dumpall --globals-only > ~/globals-2026-09-19.sql
ไฟล์นี้มีค่า Hash ของรหัสผ่านอยู่ด้วย ให้เก็บอย่างปลอดภัยและกำหนดสิทธิ์ด้วย chmod 600
ขั้นตอนที่ 3: กู้คืนฐานข้อมูล
หมายเหตุ: อย่ากู้คืนทับฐานข้อมูลที่กำลังใช้งานโดยไม่ได้สำรองสถานะปัจจุบันไว้ก่อน วิธีที่ปลอดภัยคือกู้คืนลงฐานข้อมูลชื่อใหม่ ตรวจสอบจนมั่นใจ แล้วจึงสลับให้แอปใช้ฐานข้อมูลใหม่
หากเป็นเซิร์ฟเวอร์ใหม่ ให้สร้าง Role จากไฟล์ globals ก่อน (ข้อความ Error ว่า Role postgres มีอยู่แล้วเป็นเรื่องปกติ)
sudo -u postgres psql < ~/globals-2026-09-19.sql
สร้างฐานข้อมูลเปล่าที่มีเจ้าของถูกต้อง
sudo -u postgres createdb -O appuser appdb_restore
กู้คืนไฟล์ Custom Format
sudo -u postgres pg_restore -d appdb_restore --no-owner --role=appuser < ~/appdb-2026-09-19.dump
ส่งไฟล์เข้าทาง < เพื่อให้ผู้ใช้ postgres ไม่ต้องมีสิทธิ์อ่านโฮมของคุณ --no-owner --role=appuser ทำให้ทุกออบเจกต์ที่กู้คืนเป็นของ appuser ซึ่งสะดวกเมื่อชื่อ Role บนเครื่องใหม่ไม่ตรงกับเครื่องเดิม หาก Role ตรงกันและต้องการคงเจ้าของเดิมไว้ให้ตัดสองตัวเลือกนี้ออก
สำหรับฐานข้อมูลใหญ่ ให้วางไฟล์ในที่ที่ postgres อ่านได้ เช่น /tmp แล้วใช้หลายโปรเซสพร้อมกันด้วย -j (ใช้ได้เมื่อระบุชื่อไฟล์ ไม่ใช่รับผ่าน <)
sudo -u postgres pg_restore -d appdb_restore -j 4 --no-owner --role=appuser /tmp/appdb-2026-09-19.dump
ต้องการกู้คืนเพียงบางตาราง ดูรายการในไฟล์ด้วย pg_restore -l ไฟล์.dump แล้วใช้ -t ชื่อตาราง
กู้คืนไฟล์ Plain SQL
gunzip < ~/appdb-2026-09-19.sql.gz | sudo -u postgres psql -v ON_ERROR_STOP=1 -d appdb_restore
ON_ERROR_STOP=1 ทำให้หยุดทันทีเมื่อเจอ Error แรก แทนที่จะทำต่อจนได้ข้อมูลไม่ครบโดยไม่รู้ตัว
ขั้นตอนที่ 4: สำรองอัตโนมัติทุกวัน
สร้างโฟลเดอร์ที่ผู้ใช้ postgres เป็นเจ้าของ แล้วตั้ง Cronjob ในนามผู้ใช้ postgres ซึ่งเข้าฐานข้อมูลได้โดยไม่ต้องใช้รหัสผ่าน
sudo mkdir -p /var/backups/postgresql
sudo chown postgres:postgres /var/backups/postgresql
sudo chmod 700 /var/backups/postgresql
sudo crontab -u postgres -e
เพิ่มบรรทัดนี้ (ใน Crontab ต้องเขียน % เป็น \%)
15 3 * * * pg_dump -Fc appdb > /var/backups/postgresql/appdb-$(date +\%F).dump && find /var/backups/postgresql -name 'appdb-*.dump' -mtime +14 -delete
หากสำรองด้วย Role ที่ใช้รหัสผ่าน ให้เก็บรหัสผ่านในไฟล์ ~/.pgpass รูปแบบ hostname:port:database:username:password และตั้งสิทธิ์ chmod 600 ~/.pgpass แทนการใส่รหัสผ่านในสคริปต์ จากนั้นส่งไฟล์สำรองออกไปเก็บนอกเครื่องตามคู่มือ สำรองข้อมูลไปยังเซิร์ฟเวอร์อื่นด้วย rsync และ Cronjob
ตรวจสอบผลลัพธ์
- ตรวจว่าไฟล์ Custom Format อ่านได้:
pg_restore -l ~/appdb-2026-09-19.dump | head ต้องแสดงส่วนหัวและรายการออบเจกต์ - เปรียบเทียบฐานข้อมูลที่กู้คืนกับของจริง เข้าด้วย
sudo -u postgres psql -d appdb_restore แล้วใช้ \dt ดูรายการตาราง และ SELECT count(*) FROM ชื่อตาราง; เทียบจำนวนแถวของตารางสำคัญ - ทดลองเชื่อมต่อแอปในสภาพแวดล้อมทดสอบเข้ากับฐานข้อมูลที่กู้คืน
ปัญหาที่พบบ่อยเกี่ยวกับ PostgreSQL
pg_dump: error: aborting because of server version mismatch
pg_dump ต้องมีเวอร์ชันเท่ากับหรือใหม่กว่าเซิร์ฟเวอร์ที่สำรอง มักเกิดเมื่อสำรองจากเครื่องอื่นที่ติดตั้ง PostgreSQL Client รุ่นเก่า ให้ติดตั้ง Client รุ่นเดียวกับเซิร์ฟเวอร์ หรือรัน pg_dump บนเครื่องเซิร์ฟเวอร์เอง
pg_restore: error: unsupported version in file header
ไฟล์ถูกสร้างด้วย pg_dump ที่ใหม่กว่า pg_restore ที่ใช้กู้คืน ต้องใช้ pg_restore เวอร์ชันเท่ากันหรือใหม่กว่า หรือสำรองใหม่เป็นแบบ Plain SQL แล้วกู้ด้วย psql
input file appears to be a text format dump. Please use psql.
ไฟล์เป็น Plain SQL ไม่ใช่ Custom Format ให้กู้คืนด้วย psql ตามหัวข้อกู้คืนไฟล์ Plain SQL
role "appuser" does not exist
เครื่องปลายทางยังไม่มี Role ที่เป็นเจ้าของออบเจกต์ ให้กู้คืนไฟล์ globals ก่อน หรือใช้ --no-owner ร่วมกับ --role ตามตัวอย่าง หรือ --no-privileges หากไม่ต้องการคัดลอกสิทธิ์ GRANT เดิม
หากทำตามขั้นตอนแล้วยังติดปัญหา สามารถติดต่อทีมงาน Support ของ THAI DATA CLOUD ได้ที่ https://thaidata.cloud/contact/ โดยแจ้งชื่อเซิร์ฟเวอร์ ระบบปฏิบัติการ ขั้นตอนที่ทำไปแล้ว และข้อความ Error ที่พบ เพื่อให้ตรวจสอบได้รวดเร็วขึ้น
อ่านเพิ่มเติมเกี่ยวกับ PostgreSQL
