Linux 上 PostgreSQL 数据恢复方法概览
- 逻辑恢复：使用 pg_dump/pg_restore 对单个库或对象做导出/导入，适合误删表/库、迁移与选择性恢复。
- 时间点恢复 PITR：基于基础备份 + WAL 归档，可将库恢复到任意时间点或最近一致性点。
- 物理恢复：用 文件系统/块级备份 或 pg_basebackup 做整库恢复，适合实例级故障、介质损坏。
- 单用户模式修复：数据库无法正常启动时，进入单用户模式执行检查/修复（如重建索引、VACUUM）。
- 第三方工具：如 pgBackRest 等，提供并行、压缩、加密与更简化的备份恢复流程。
方法对比与适用场景
| 方法 |
前提条件 |
典型命令 |
适用场景 |
优点 |
局限 |
| 逻辑恢复（pg_dump/pg_restore） |
有逻辑备份文件（.sql 或 -F c 自定义归档） |
pg_dump -U u -F c db > db.backup；pg_restore -U u -d db db.backup |
误删表/库、跨版本迁移、选择性对象恢复 |
灵活、粒度细、跨平台 |
非崩溃一致性；大数据量恢复较慢 |
| PITR（基础备份 + WAL 归档） |
已开启 WAL 归档，有基础备份 |
基础备份（pg_basebackup 或文件系统拷贝）；设置 restore_command、recovery_target_time；启动至恢复完成 |
误删数据后按时间点回滚、合规审计回退 |
可精确到时间点、连续性好 |
需提前配置归档与备份 |
| 物理恢复（文件系统/块级） |
有可用的文件系统/块级备份 |
停库；用备份覆盖 $PGDATA；启动 |
实例损坏、磁盘故障、无法启动 |
恢复快、一致性好 |
依赖同平台/版本；粒度粗 |
| 单用户模式修复 |
实例无法正常启动 |
postgres --single -D $PGDATA；执行 REINDEX/VACUUM |
系统表/索引损坏、启动失败 |
可在不启动服务时修复 |
风险高，需有经验 |
| 第三方工具（pgBackRest 等） |
已部署工具并配置仓库 |
pgbackrest --stanza=demo backup/restore |
大规模、自动化、加密压缩备份 |
易用、功能强、可并行 |
需学习成本与额外部署 |
关键操作要点
-
逻辑恢复
- 备份：pg_dump -U postgres -F c -b -v -f mydb.backup mydb
- 恢复：先创建空库 createdb mydb；再 pg_restore -U postgres -d mydb -v mydb.backup
- 若备份为纯 SQL 脚本（非 -F c），用 psql -d mydb -f mydb.sql 导入。
-
PITR（时间点恢复）
- 准备：已开启 WAL 归档（postgresql.conf 中 archive_mode=on，archive_command 如 ‘cp %p /path/%f’），并有基础备份。
- 恢复：将基础备份恢复到 $PGDATA；在 $PGDATA 下创建 recovery.signal；在 postgresql.auto.conf 中设置 restore_command=‘cp /path/%f %p’ 与 recovery_target_time=‘2024-12-07 14:14:29.975833+08’（或 recovery_target_timeline=‘latest’）；启动数据库，完成恢复后自动切为读写。
-
物理恢复（文件系统/块级）
- 停库：systemctl stop postgresql 或 pg_ctl -D $PGDATA stop
- 用备份覆盖 $PGDATA（文件系统拷贝或 tar 包解压）
- 启动：systemctl start postgresql 或 pg_ctl -D $PGDATA start
-
单用户模式修复
- 停库后执行：postgres --single -D /var/lib/pgsql/data mydb
- 在单用户提示符下可执行：REINDEX DATABASE mydb; VACUUM FULL; 等修复命令；完成后重启。
恢复前检查与恢复后验证
-
恢复前
- 备份当前状态：对现有 $PGDATA 与关键配置文件做快照/拷贝，避免恢复失败导致二次损失。
- 校验备份完整性：对备份文件做 MD5/SHA-256 校验，确认可读、大小合理。
-
恢复后
- 基本校验：连接数据库，核对关键表行数、抽样数据、约束与索引可用性。
- 一致性检查：外键、触发器、存储过程是否能正常运行；必要时执行 VACUUM ANALYZE。
常见注意事项与风险提示
- 误删后应立即停止写入，避免新 WAL 覆盖可回滚窗口；尽快进行备份（哪怕是文件系统级拷贝）。
- 使用 pg_resetxlog/pg_resetwal 仅在极端情况下、且已做好完整备份后尝试，操作不当会导致数据丢失。
- 恢复路径与参数（如 restore_command、recovery_target_time）务必核对，避免恢复到错误时间点。
- 生产环境建议在恢复演练环境验证流程与时效，再对生产执行。
