Debian系统PostgreSQL备份恢复策略

一、备份策略

1. 逻辑备份

   • 工具：pg_dump
   • 场景：适合中小型数据库、跨平台迁移或需保留特定时间点数据。
   • 命令示例：

     pg_dump -U username -h localhost -p 5432 -F c -b -v -f /backup/db_backup.backup mydb
     

     （-F c为自定义格式，支持压缩和恢复特定表）。
   • 压缩备份：

     pg_dump -U username -F c mydb | gzip > mydb_backup.sql.gz
     ```。  
     
     

2. 物理备份

   • 工具：pg_basebackup
   • 场景：适合大型数据库、需快速恢复或启用流复制。
   • 命令示例：

     pg_basebackup -U username -D /backup/full_backup -F t -z -P -h localhost -p 5432
     

     （-F t为tar格式，-z压缩，-P显示进度）。

3. 连续归档（PITR）

   • 配置：修改postgresql.conf，设置wal_level=replica、archive_mode=on，并指定归档路径archive_command。
   • 恢复流程：通过归档日志实现时间点恢复，需结合基础备份使用。

4. 自动化定时备份

   • 使用cron定时执行备份脚本，例如每日凌晨备份：

     0 1 * * * pg_dump -U postgres -F c -f /backup/daily_backup_$(date +\%Y\%m\%d).backup mydb
     ```。  
     
     

二、恢复策略

1. 逻辑备份恢复

   • 命令：pg_restore
   • 场景：恢复逻辑备份的完整数据库或特定表。
   • 示例：

     pg_restore -U postgres -d mydb /backup/db_backup.backup  # 恢复整个数据库
     pg_restore -U postgres -d mydb -t table_name /backup/db_backup.backup  # 恢复特定表
     ```。  
     
     

2. 物理备份恢复

   • 步骤：
     1. 停止PostgreSQL服务：sudo systemctl stop postgresql。
     2. 清空数据目录（谨慎操作）：rm -rf /var/lib/postgresql/12/main/*（路径需匹配实际版本）。
     3. 复制备份文件到数据目录：cp -r /backup/full_backup/* /var/lib/postgresql/12/main/。
     4. 启动服务：sudo systemctl start postgresql。

3. 连续归档恢复（PITR）

   • 步骤：
     1. 配置recovery.conf（PostgreSQL 10前）或postgresql.auto.conf（10后），设置restore_command和recovery_target_time。
     2. 启动服务进入恢复模式，完成后重启至正常模式。

三、注意事项

• 权限与安全：确保备份文件存储在安全路径，使用pg_dump时通过-W或环境变量PGPASSWORD避免密码泄露。
• 验证备份：定期用pg_restore测试备份可恢复性，检查日志文件/var/log/postgresql/确认无错误。
• 性能优化：大型数据库优先选择物理备份，逻辑备份可结合-j参数并行导出（需PostgreSQL 9.3+）。

参考来源：