TRẦN VĂN BÌNH MASTER: 12.Nguyên lý Backup và Recovery trong PostgreSQL

Thứ Bảy, 31 tháng 5, 2025

12.Nguyên lý Backup và Recovery trong PostgreSQL

✅ 1. Tổng quan các loại backup

PostgreSQL hỗ trợ hai loại chính:

Loại Mục tiêu Dạng dữ liệu Công cụ
Logical Backup CSDL, bảng cụ thể Câu lệnh SQL `pg_dump`, `pg_dumpall`
Physical Backup Toàn bộ cluster Dữ liệu nhị phân gốc `pg_basebackup`, `rsync`, `pgBackRest`

Loại	Mục tiêu	Dạng dữ liệu	Công cụ
Logical Backup	CSDL, bảng cụ thể	Câu lệnh SQL	`pg_dump`, `pg_dumpall`
Physical Backup	Toàn bộ cluster	Dữ liệu nhị phân gốc	`pg_basebackup`, `rsync`, `pgBackRest`

📘 2. Logical Backup – `pg_dump`, `pg_dumpall`

🛠️ a. Backup một database

bash
pg_dump -U postgres -F c -f /backup/mydb.backup mydb

-F c: Custom format (phù hợp với pg_restore)

🛠️ b. Backup 1 bảng cụ thể

bash
pg_dump -U postgres -t orders -F p -f /backup/orders.sql mydb

-t: chỉ định bảng orders
-F p: Plain SQL script

🛠️ c. Backup toàn bộ cluster (roles + db)

bash
pg_dumpall -U postgres -f /backup/full_cluster.sql

♻️ 3. Phục hồi từ Logical Backup

🛠️ a. Restore từ custom backup

bash
pg_restore -U postgres -d mydb_restored /backup/mydb.backup

🛠️ b. Restore plain SQL

bash
psql -U postgres -d mydb_restored -f /backup/orders.sql

🧪 Tạo DB để phục hồi:

sql
CREATE DATABASE mydb_restored;

📦 4. Physical Backup – `pg_basebackup`

🛠️ Thực hiện backup toàn bộ dữ liệu binary:

bash
pg_basebackup -U replicator -D /backup/base_2025_05_20 -Fp -Xs -P

Tham số	Ý nghĩa
`-Fp`	Plain format
`-Xs`	Stream WAL trong quá trình sao lưu
`-P`	Hiển thị tiến độ

Thư mục /backup/... sẽ chứa đầy đủ dữ liệu giống thư mục $PGDATA

🔁 5. WAL (Write-Ahead Log) và PITR

Cấu hình trong `postgresql.conf`:

conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /mnt/pg_archive/%f'

WAL giúp PostgreSQL phục hồi dữ liệu chính xác sau crash và hỗ trợ phục hồi theo thời điểm (PITR)

🛠️ Kiểm tra trạng thái archiver:

sql
SELECT * FROM pg_stat_archiver;

⏮️ 6. Phục hồi theo thời điểm (Point-in-Time Recovery – PITR)

🧱 Chuẩn bị:

Base backup từ `pg_basebackup`

WAL đã archive

Đặt file `recovery.conf` (PG11) hoặc `recovery.signal` (PG12+)

📄 Nội dung file `recovery.conf` (PG11):

conf
restore_command = 'cp /mnt/pg_archive/%f "%p"'
recovery_target_time = '2025-05-20 10:30:00'

PostgreSQL sẽ replay WAL đến đúng thời điểm bạn chỉ định

🛠️ Câu lệnh kiểm tra thời gian checkpoint:

bash
pg_controldata /var/lib/pgsql/13/data | grep 'Time of latest checkpoint'

🔍 7. Theo dõi backup & restore

🧪 Xem checkpoint, timeline:

bash
pg_controldata | egrep 'Time|Timeline|Checkpoint'

🧪 Kiểm tra vị trí WAL hiện tại:

sql
SELECT pg_current_wal_lsn();

🧹 8. Dọn WAL cũ (khi không dùng PITR nữa)

bash
pg_archivecleanup /mnt/pg_archive/ <lsn hoặc file>

Thường dùng sau khi phục hồi hoặc giữ archive nhỏ gọn

🧠 9. So sánh Logical vs Physical (thực tiễn)

Tiêu chí Logical (`pg_dump`) Physical (`pg_basebackup`)
Mức độ DB/Table Toàn bộ cluster
Chọn lọc ✅ ❌
Dễ đọc ✅ ❌
PITR ❌ ✅
Dung lượng Thường nhỏ hơn Lớn hơn
Dễ phục hồi 1 bảng ✅ ❌
Dùng cho DR/HA ❌ ✅

Tiêu chí	Logical (`pg_dump`)	Physical (`pg_basebackup`)
Mức độ	DB/Table	Toàn bộ cluster
Chọn lọc	✅	❌
Dễ đọc	✅	❌
PITR	❌	✅
Dung lượng	Thường nhỏ hơn	Lớn hơn
Dễ phục hồi 1 bảng	✅	❌
Dùng cho DR/HA	❌	✅

🔐 10. Best Practice – Chính sách Backup & Recovery

Chính sách Gợi ý triển khai
Backup hàng ngày `pg_dump` hoặc `pg_basebackup + cron`
PITR mỗi giờ Kết hợp WAL + archive_command
Kiểm tra phục hồi Restore định kỳ vào môi trường test
Thông báo lỗi Script backup gửi Telegram/email
Bảo mật dữ liệu Mã hóa backup + hạn chế quyền ghi archive

Chính sách	Gợi ý triển khai
Backup hàng ngày	`pg_dump` hoặc `pg_basebackup + cron`
PITR mỗi giờ	Kết hợp WAL + archive_command
Kiểm tra phục hồi	Restore định kỳ vào môi trường test
Thông báo lỗi	Script backup gửi Telegram/email
Bảo mật dữ liệu	Mã hóa backup + hạn chế quyền ghi archive

✅ Kết luận

Luôn cần cả backup & kiểm thử phục hồi

Kết hợp nhiều cấp độ backup để đảm bảo:
`Backup toàn cluster + từng DB/table + WAL archive`

PITR là công cụ cực mạnh trong các tình huống "lỡ tay", lỗi dữ liệu theo thời gian

=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty

Thứ Bảy, 31 tháng 5, 2025

12.Nguyên lý Backup và Recovery trong PostgreSQL

✅ 1. Tổng quan các loại backup

PostgreSQL hỗ trợ hai loại chính: LoạiMục tiêuDạng dữ liệuCông cụLogical BackupCSDL, bảng cụ thểCâu lệnh SQLpg_dump, pg_dumpallPhysical BackupToàn bộ clusterDữ liệu nhị phân gốcpg_basebackup, rsync, pgBackRest

📘 2. Logical Backup – pg_dump, pg_dumpall

🛠️ a. Backup một database

bashCopyEditpg_dump -U postgres -F c -f /backup/mydb.backup mydb -F c: Custom format (phù hợp với pg_restore)

🛠️ b. Backup 1 bảng cụ thể

bashCopyEditpg_dump -U postgres -t orders -F p -f /backup/orders.sql mydb -t: chỉ định bảng orders -F p: Plain SQL script

🛠️ c. Backup toàn bộ cluster (roles + db)

bashCopyEditpg_dumpall -U postgres -f /backup/full_cluster.sql

♻️ 3. Phục hồi từ Logical Backup

🛠️ a. Restore từ custom backup

bashCopyEditpg_restore -U postgres -d mydb_restored /backup/mydb.backup

🛠️ b. Restore plain SQL

bashCopyEditpsql -U postgres -d mydb_restored -f /backup/orders.sql

🧪 Tạo DB để phục hồi:

sqlCopyEditCREATE DATABASE mydb_restored;

📦 4. Physical Backup – pg_basebackup

🛠️ Thực hiện backup toàn bộ dữ liệu binary:

bashCopyEditpg_basebackup -U replicator -D /backup/base_2025_05_20 -Fp -Xs -P Tham sốÝ nghĩa-FpPlain format-XsStream WAL trong quá trình sao lưu-PHiển thị tiến độ Thư mục /backup/... sẽ chứa đầy đủ dữ liệu giống thư mục $PGDATA

🔁 5. WAL (Write-Ahead Log) và PITR

Cấu hình trong postgresql.conf:

confCopyEditwal_level = replica archive_mode = on archive_command = 'cp %p /mnt/pg_archive/%f' WAL giúp PostgreSQL phục hồi dữ liệu chính xác sau crash và hỗ trợ phục hồi theo thời điểm (PITR)

🛠️ Kiểm tra trạng thái archiver:

sqlCopyEditSELECT * FROM pg_stat_archiver;

⏮️ 6. Phục hồi theo thời điểm (Point-in-Time Recovery – PITR)

🧱 Chuẩn bị:

Base backup từ pg_basebackup WAL đã archive Đặt file recovery.conf (PG11) hoặc recovery.signal (PG12+)

📄 Nội dung file recovery.conf (PG11):

confCopyEditrestore_command = 'cp /mnt/pg_archive/%f "%p"' recovery_target_time = '2025-05-20 10:30:00' PostgreSQL sẽ replay WAL đến đúng thời điểm bạn chỉ định

🛠️ Câu lệnh kiểm tra thời gian checkpoint:

bashCopyEditpg_controldata /var/lib/pgsql/13/data | grep 'Time of latest checkpoint'

🔍 7. Theo dõi backup & restore

🧪 Xem checkpoint, timeline:

bashCopyEditpg_controldata | egrep 'Time|Timeline|Checkpoint'

🧪 Kiểm tra vị trí WAL hiện tại:

sqlCopyEditSELECT pg_current_wal_lsn();

🧹 8. Dọn WAL cũ (khi không dùng PITR nữa)

bashCopyEditpg_archivecleanup /mnt/pg_archive/ <lsn hoặc file> Thường dùng sau khi phục hồi hoặc giữ archive nhỏ gọn

🧠 9. So sánh Logical vs Physical (thực tiễn)

Tiêu chíLogical (pg_dump)Physical (pg_basebackup)Mức độDB/TableToàn bộ clusterChọn lọc✅❌Dễ đọc✅❌PITR❌✅Dung lượngThường nhỏ hơnLớn hơnDễ phục hồi 1 bảng✅❌Dùng cho DR/HA❌✅

🔐 10. Best Practice – Chính sách Backup & Recovery

✅ Kết luận

Luôn cần cả backup & kiểm thử phục hồi Kết hợp nhiều cấp độ backup để đảm bảo: Backup toàn cluster + từng DB/table + WAL archive PITR là công cụ cực mạnh trong các tình huống "lỡ tay", lỗi dữ liệu theo thời gian

ĐỌC NHIỀU

PostgreSQL hỗ trợ hai loại chính:

Loại Mục tiêu Dạng dữ liệu Công cụ
Logical Backup CSDL, bảng cụ thể Câu lệnh SQL `pg_dump`, `pg_dumpall`
Physical Backup Toàn bộ cluster Dữ liệu nhị phân gốc `pg_basebackup`, `rsync`, `pgBackRest`

📘 2. Logical Backup – `pg_dump`, `pg_dumpall`

bash
`pg_dump -U postgres -F c -f /backup/mydb.backup mydb`

`-F c`: Custom format (phù hợp với `pg_restore`)

bash
`pg_dump -U postgres -t orders -F p -f /backup/orders.sql mydb`

`-t`: chỉ định bảng `orders`
`-F p`: Plain SQL script

bash
`pg_dumpall -U postgres -f /backup/full_cluster.sql`

bash
`pg_restore -U postgres -d mydb_restored /backup/mydb.backup`

bash
`psql -U postgres -d mydb_restored -f /backup/orders.sql`

sql
`CREATE DATABASE mydb_restored;`

📦 4. Physical Backup – `pg_basebackup`

bash
`pg_basebackup -U replicator -D /backup/base_2025_05_20 -Fp -Xs -P`

Tham số Ý nghĩa
`-Fp` Plain format
`-Xs` Stream WAL trong quá trình sao lưu
`-P` Hiển thị tiến độ

Thư mục `/backup/...` sẽ chứa đầy đủ dữ liệu giống thư mục `$PGDATA`

Cấu hình trong `postgresql.conf`:

conf
`wal_level = replica archive_mode = on archive_command = 'cp %p /mnt/pg_archive/%f'`

WAL giúp PostgreSQL phục hồi dữ liệu chính xác sau crash và hỗ trợ phục hồi theo thời điểm (PITR)

sql
`SELECT * FROM pg_stat_archiver;`

Base backup từ `pg_basebackup`

WAL đã archive

Đặt file `recovery.conf` (PG11) hoặc `recovery.signal` (PG12+)

📄 Nội dung file `recovery.conf` (PG11):

conf
`restore_command = 'cp /mnt/pg_archive/%f "%p"' recovery_target_time = '2025-05-20 10:30:00'`

PostgreSQL sẽ replay WAL đến đúng thời điểm bạn chỉ định

bash
`pg_controldata /var/lib/pgsql/13/data | grep 'Time of latest checkpoint'`

bash
`pg_controldata | egrep 'Time|Timeline|Checkpoint'`

sql
`SELECT pg_current_wal_lsn();`

bash
`pg_archivecleanup /mnt/pg_archive/ <lsn hoặc file>`

Thường dùng sau khi phục hồi hoặc giữ archive nhỏ gọn

Tiêu chí Logical (`pg_dump`) Physical (`pg_basebackup`)
Mức độ DB/Table Toàn bộ cluster
Chọn lọc ✅ ❌
Dễ đọc ✅ ❌
PITR ❌ ✅
Dung lượng Thường nhỏ hơn Lớn hơn
Dễ phục hồi 1 bảng ✅ ❌
Dùng cho DR/HA ❌ ✅

Luôn cần cả backup & kiểm thử phục hồi

Kết hợp nhiều cấp độ backup để đảm bảo:
`Backup toàn cluster + từng DB/table + WAL archive`

PITR là công cụ cực mạnh trong các tình huống "lỡ tay", lỗi dữ liệu theo thời gian