Debian 上 PostgreSQL 数据恢复实用指南
一、恢复前准备与定位
- 确认丢失范围与时间点：查看 PostgreSQL 日志（常见路径为 /var/log/postgresql/），定位误删或故障发生的时间点；同时核对现有备份是否可用与完整。
- 保护现场：在继续操作前，先对当前状态做一次完整拷贝（包含 $PGDATA 与 WAL 归档目录），避免二次损坏。
- 确认数据目录：常见数据目录为 /var/lib/postgresql/<版本号>/<集群名>/data（Debian 常见集群名为 main）。
- 规划恢复方式：有备份时优先用备份恢复；具备 WAL 归档 时做时间点恢复（PITR）；无任何备份时仅能尝试导出尚存数据并重建。
二、方法一 逻辑备份恢复（pg_dump/pg_restore）
- 适用场景：已有 SQL 脚本 或 自定义格式（-F c） 的逻辑备份。
- 基本步骤：
- 如库已损坏或需重建，先创建空库：
sudo -u postgres psql -c “CREATE DATABASE mydb;”
- 恢复自定义格式备份：
sudo -u postgres pg_restore -d mydb /path/to/backup.dump
- 恢复 SQL 脚本备份：
sudo -u postgres psql -d mydb -f /path/to/backup.sql
- 校验：
sudo -u postgres psql -c “\l”
sudo -u postgres psql -c “\c mydb” -c “SELECT COUNT(*) FROM your_table;”
- 注意：恢复前建议先备份当前残余数据；自定义格式可用 -t 表名 做表级恢复；SQL 脚本恢复本质是执行 DDL/DML，注意对象依赖顺序与权限。
三、方法二 物理备份恢复（基础备份 + WAL 归档，PITR）
- 适用场景：已配置 WAL 归档，希望恢复到误删前的任意时间点。
- 基本步骤：
- 准备基础备份：使用 pg_basebackup 获取一致性物理备份（示例）：
sudo -u postgres pg_basebackup -Ft -Pv -Xf -z -Z5 -p 5432 -D /backup/full_backup
- 准备恢复环境：将基础备份解压/拷回 $PGDATA，确保权限为 postgres:postgres。
- 配置恢复参数（写入 $PGDATA/postgresql.auto.conf 或 recovery.conf）：
restore_command = ‘cp /backup/pgarch/%f %p’
recovery_target_time = ‘2025-11-29 10:00:00+08’
recovery_target_timeline = ‘latest’
- 启动到恢复模式：
sudo -u postgres pg_ctl -D /var/lib/postgresql/<版本号>/main start
数据库到达目标时间后自动进入只读恢复，确认无误后执行：
touch $PGDATA/recovery.signal
再次启动进入正常运行。
- 校验数据一致性与对象完整性。
- 说明：若未配置归档，则无法做 PITR，只能回滚到最近的基础备份状态。
四、方法三 无备份或 WAL 损坏的应急
- 导出尚存数据：
- 若实例仍可启动：
sudo -u postgres pg_dumpall -U postgres > dump.sql
- 若实例无法启动但数据文件在：先以单用户维护模式启动（仅导出），或临时拷贝 base/ 等目录到新实例进行导出。
- 重建与导入：
- 全新初始化集群（使用 initdb 或 Debian 的 pg_createcluster），创建数据库后导入：
sudo -u postgres psql -d postgres -f dump.sql
- 极端情况下的 WAL 修复（高风险，可能导致事务一致性受损，务必先全量备份当前文件）：
- 停止数据库：sudo systemctl stop postgresql
- 备份当前 $PGDATA
- 执行：sudo -u postgres pg_resetwal -D /var/lib/postgresql/<版本号>/main -f
- 启动并尽快导出可恢复数据，随后重建集群。
- 建议：此路径仅作“尽量挽回”的最后手段，成功率和一致性无法保证。
五、恢复后验证与加固
- 恢复验证：
- 对象级：\l、\dn、\dt 检查库/模式/表是否存在；
- 数据级：对关键表执行 COUNT(*)、校验和或抽样对比；
- 依赖与权限：检查视图/函数/触发器、角色与权限是否正确。
- 加固与预防：
- 定期做逻辑备份（如每日全量 + 每小时增量脚本），并定期验证可恢复性；
- 启用并监控 WAL 归档 与 连续归档即时恢复（PITR） 能力；
- 重要变更前先做一次备份；为关键库配置监控与告警。
