Debian上PostgreSQL备份恢复策略

一、备份策略

1. 逻辑备份

   • pg_dump：导出数据库结构和数据为SQL脚本，支持自定义格式（-F c）、压缩（gzip）及选择性备份（如指定表-t）。
     ▶ 示例：pg_dump -U postgres -F c -b -v -f /backup/db_backup.backup mydb
   • pg_dumpall：备份整个集群（含所有数据库和角色），仅支持文本格式。

2. 物理备份

   • pg_basebackup：复制数据库文件，支持增量备份（--incremental）和压缩，适用于快速恢复。
     ▶ 示例：pg_basebackup -D /backup/full -F t -z -P -h localhost -p 5432 -U postgres

3. 连续归档（PITR）

   • 启用WAL归档：修改postgresql.conf，设置wal_level=replica、archive_mode=on，并配置archive_command（如cp %p /backup/pgarch/%f）。
   • 结合全量备份与WAL日志，可实现时间点恢复。

4. 自动化与定时任务

   • 使用cron定期执行备份脚本，如每日凌晨备份：
     0 1 * * * /usr/bin/pg_dump -U postgres -F c -f /backup/db_$(date +%Y%m%d).backup。

二、恢复策略

1. 逻辑备份恢复

   • pg_restore：恢复SQL格式或自定义格式备份，支持指定数据库、表或恢复到特定时间点。
     ▶ 示例：pg_restore -U postgres -d mydb /backup/db_backup.backup

2. 物理备份恢复

   • 停止PostgreSQL服务，用pg_basebackup恢复全量备份，配合WAL日志实现PITR。
     ▶ 步骤：
     1. 停止服务：sudo systemctl stop postgresql
     2. 恢复备份：cp -r /backup/full/* /var/lib/postgresql/版本号/
     3. 配置postgresql.auto.conf指定restore_command和恢复时间点，重启服务。

3. 误删数据恢复

   • 若有WAL日志，可通过pg_resetwal回滚到一致状态点（可能导致部分数据丢失）。

三、注意事项

• 存储安全：备份文件需存储在异地或加密云存储，避免单点故障。
• 权限管理：使用专用用户执行备份/恢复，避免权限滥用。
• 测试验证：定期测试备份的可恢复性，确保恢复流程可靠。

参考来源：[1,2,3,4,5,6,7,8,9,10]