Ubuntu 上 PostgreSQL 的备份与恢复策略
一、策略总览与选型
- 逻辑备份：使用 pg_dump / pg_dumpall 导出为 SQL 脚本 或 自定义/目录/TAR 格式，便于跨版本迁移、选择性恢复与快速导入导出。适合大多数应用与日常备份。
- 物理备份：使用 pg_basebackup 获取数据目录一致性快照，配合 WAL 归档 实现 时间点恢复 PITR，适合生产环境的快速全量+增量保护与灾难恢复。
- 第三方工具：如 pg_rman（物理备份工具），支持全量/增量备份与校验，适合需要更完善备份集管理的场景。
- 版本兼容要点：尽量保持备份与恢复的 PostgreSQL 主版本一致；若需跨版本，优先使用 纯 SQL 脚本 方式导入以降低不兼容风险。
二、逻辑备份与恢复步骤
- 备份
- 单库导出为自定义格式（便于后续选择性恢复与压缩）：
pg_dump -h localhost -U postgres -d mydb -F c -b -v -f /backup/mydb_$(date +%F).dump --encoding UTF8
- 导出为 SQL 脚本（跨版本更稳妥）：
pg_dump -h localhost -U postgres -d mydb -f /backup/mydb_$(date +%F).sql --encoding UTF8
- 全库导出（含角色/表空间等全局对象）：
pg_dumpall -U postgres -f /backup/all_dbs_$(date +%F).sql
- 恢复
- SQL 脚本导入（先建库，建议基于 template0）：
createdb -T template0 -U postgres mydb_restored
psql -U postgres -d mydb_restored -f /backup/mydb_2025-12-10.sql
如需遇到错误即停或全量事务一致性：
psql --set ON_ERROR_STOP=on -U postgres -d mydb_restored -f /backup/mydb_2025-12-10.sql
psql -1 -U postgres -d mydb_restored -f /backup/mydb_2025-12-10.sql
- 自定义格式导入（使用 pg_restore）：
createdb -T template0 -U postgres mydb_restored
pg_restore -h localhost -U postgres -d mydb_restored -v /backup/mydb_2025-12-10.dump
- 自动化与保留
- 示例脚本（保留 7 天，使用环境变量避免交互）：
LOG_DATE=$(date +‘%F %T’)
echo “$LOG_DATE 开始备份” >> /var/log/pg_backup.log
OUT_DIR=/backup/pg
mkdir -p “$OUT_DIR”
DATE=$(date +%F)
DB=mydb
export PGPASSWORD=‘YourStrongPwd’
pg_dump -h localhost -U postgres -d “$DB” -F c -b -f “$OUT_DIR/${DB}${DATE}.dump" --encoding UTF8
tar czf "$OUT_DIR/${DB}${DATE}.tar.gz” -C “$OUT_DIR” “${DB}${DATE}.dump" && rm -f "$OUT_DIR/${DB}${DATE}.dump”
find “$OUT_DIR” -type f -mtime +7 -delete
echo “$LOG_DATE 备份完成” >> /var/log/pg_backup.log
- 定时任务（每天 01:30 执行）：
30 1 * * * /bin/bash /opt/scripts/pg_backup.sh >/tmp/pg_backup.log 2>&1
- 排查要点：cron 无环境变量与输出问题常见，建议用全路径命令、重定向日志，并查看 /var/log/syslog 与 /tmp/*.log。
三、物理备份与时间点恢复 PITR
- 启用 WAL 归档（编辑 /etc/postgresql/<版本>/main/postgresql.conf）：
wal_level = replica
archive_mode = on
archive_command = ‘cp %p /var/lib/postgresql/pgbackup/archive_wals/%f’
创建目录并授权：
mkdir -p /var/lib/postgresql/pgbackup/archive_wals
chown postgres:postgres /var/lib/postgresql/pgbackup/archive_wals
systemctl restart postgresql
- 全量备份（示例：每日一次到备份服务器或本地）：
pg_basebackup -h 192.168.1.10 -D /backup/pg_base/$(date +%F) -F t -z -P
- 恢复到指定时间点（PITR）
- 准备恢复目录（示例）：
systemctl stop postgresql
mv /var/lib/postgresql/13/main /var/lib/postgresql/13/main_old_$(date +%F)
mkdir -p /var/lib/postgresql/13/main
chmod 0700 /var/lib/postgresql/13/main
- 解压基础备份：
tar xzf /backup/pg_base/2025-12-10/base.tar.gz -C /var/lib/postgresql/13/main
tar xzf /backup/pg_base/2025-12-10/pg_wal.tar.gz -C /var/lib/postgresql/13/main/pg_wal
- 配置恢复目标（恢复到某个时间点，如 2025-12-10 18:20:00+08）：
cat > /var/lib/postgresql/13/main/recovery.signal <<‘EOF’
recovery_target_time = ‘2025-12-10 18:20:00+08’
restore_command = ‘cp /var/lib/postgresql/pgbackup/archive_wals/%f %p’
EOF
- 启动并完成恢复：
systemctl start postgresql
恢复完成后，PostgreSQL 会自动删除 recovery.signal 并进入正常运行。
- 校验与演练：定期在测试环境演练 PITR，核对关键表数据与业务一致性。
四、自动化与运维要点
- 定时任务与日志：使用 crontab 调度备份脚本，务必重定向输出到日志文件，并定期 tail /var/log/syslog 与脚本日志，排查 “No MTA installed, discarding output” 等常见 cron 问题。
- 安全与权限：备份文件与 WAL 归档目录 仅对 postgres 可读写；脚本中使用 PGPASSWORD 或 ~/.pgpass 管理凭据，避免明文密码与交互阻塞。
- 版本与扩展：跨版本迁移优先用 SQL 脚本；恢复前确保目标库已安装相同 扩展（如 PostGIS），否则导入会失败。
- 保留策略：按合规与容量设置保留周期（如 7–30 天），并定期清理过期备份与归档。
五、常见问题与快速处理
- 高版本备份在低版本恢复失败：优先改用 pg_dump 纯 SQL 脚本 方式导入，兼容性最好；自定义/目录/TAR 格式可能依赖新版本特性。
- 恢复中断或失败：SQL 脚本导入建议加 –set ON_ERROR_STOP=on 或 -1（单事务）以快速定位问题并保证一致性。
- cron 任务未执行或无声失败：检查 /var/log/syslog，为命令使用 绝对路径，并将 stdout/stderr 重定向到日志文件。
- PITR 找不到 WAL：核对 archive_command 是否成功执行、归档目录权限是否正确、WAL 文件名与路径是否匹配；必要时在 recovery.conf（或 recovery.signal + postgresql.auto.conf）中校正 restore_command 与目标时间。
