CentOS 上 PostgreSQL 数据库恢复技巧
一、恢复方式总览与选择
- 逻辑恢复：使用 pg_dump/pg_dumpall 生成的 SQL 脚本 或 自定义格式（-F c），通过 psql 或 pg_restore 导入，适合跨小版本迁移、单库/多库/指定对象恢复、开发测试环境快速搭建。
- 物理恢复：基于文件系统拷贝或 pg_basebackup 的基础备份，配合 WAL 归档进行时间点恢复（PITR），适合生产环境的整机/单库级恢复与精确回滚。
- 工具辅助：使用 pg_rman 做全量/增量备份与 PITR，简化备份集管理与恢复流程。
- 选择建议：
- 仅需恢复部分表/模式或跨版本迁移 → 选逻辑恢复。
- 需要恢复到故障前某一时刻或做灾备演练 → 选物理恢复/PITR。
- 希望有备份集管理、校验与增量能力 → 选 pg_rman。
二、逻辑恢复步骤与常用命令
- 场景 A：SQL 脚本恢复（pg_dump 默认或 -F p）
- 恢复整个库到现有实例（库需事先存在）：
- psql -h 127.0.0.1 -p 5432 -U postgres -d mydb -f mydb_20241219.sql
- 在空集群中一次性恢复所有库（使用 pg_dumpall）：
- psql –f /backup/pg_all.dmp postgres
- 压缩包直导：
- gunzip -c mydb.sql.gz | psql -d mydb
- 场景 B：自定义格式恢复（pg_dump -F c，适合大库与选择性恢复）
- 全库恢复：
- pg_restore -h 127.0.0.1 -p 5432 -U postgres -d mydb mydb_20241219.dump
- 只恢复某些表/模式：
- pg_restore -d mydb -t schema1.tbl1 -t schema2.tbl2 mydb_20241219.dump
- pg_restore -d mydb -n schema1 mydb_20241219.dump
- 实用提示：
- 使用 -O 导出可不带 OWNER，便于在目标库用不同用户导入。
- 跨库/跨用户恢复前，检查并调整 dump 中的 CREATE SCHEMA、OWNER、search_path。
- 大对象（LOB）与复杂依赖建议用 pg_restore 而非纯 SQL 脚本。
三、物理恢复与时间点恢复 PITR
- 前提条件
- 已配置 WAL 归档：postgresql.conf 中设置
- wal_level = replica
- archive_mode = on
- archive_command = ‘test ! -f /archive/%f && cp %p /archive/%f’
- 有可用的基础备份（文件系统拷贝或 pg_basebackup 产出）。
- 文件系统级基础备份恢复（停机）
- 停止数据库：systemctl stop postgresql-12
- 备份当前数据目录（可选）：mv /var/lib/pgsql/12/data /var/lib/pgsql/12/data.bak_$(date +%F)
- 恢复基础备份到数据目录：tar -xf /backup/base_20241219.tar.gz -C /
- 在 $PGDATA 创建 recovery.conf（PostgreSQL 12 之前）或 postgresql.auto.conf 中写入恢复指令（PostgreSQL 12+ 使用 recovery.signal 文件触发恢复）：
- echo “restore_command = ‘cp /archive/%f %p’” > $PGDATA/recovery.conf
- touch $PGDATA/recovery.signal # PostgreSQL 12+
- 启动数据库：systemctl start postgresql-12；恢复完成后会自动切换到正常运行模式并删除 recovery.signal。
- 时间点恢复（PITR）
- 在 recovery.conf 或 postgresql.auto.conf 增加：
- recovery_target_time = ‘2025-12-19 10:30:00’
- recovery_target_timeline = ‘latest’
- 启动实例，观察日志直至达到目标时间点后自动完成恢复。
- 使用 pg_rman 的简化流程
- 初始化与备份：
- pg_rman init -B /backup/pg_rman
- pg_rman backup --backup-mode=full -B /backup/pg_rman
- 恢复：
- pg_rman restore -B /backup/pg_rman --recovery-target-time ‘2025-12-19 10:30:00’
- 启动数据库完成回放。
四、常见坑与排查要点
- 版本匹配与兼容性
- 尽量避免跨主版本直接恢复（如 9.6 → 12/14/15），优先在相同主版本间恢复；跨版本建议先逻辑导出/导入或评估使用 pg_upgrade。
- 权限与对象归属
- 使用 pg_dump -O 导出可避免 OWNER 冲突；恢复后按需执行 ALTER … OWNER TO。
- 搜索路径与模式
- 若 dump 中包含 SET search_path，确认目标库存在对应 schema；必要时在 dump 或会话中调整。
- 大对象与依赖
- 包含 large object 或复杂依赖时，优先使用 pg_restore -F c，避免 SQL 脚本导入失败或不完整。
- WAL 归档与空间
- 确保 archive_command 返回 0 且归档目录磁盘充足；缺失 WAL 将导致 PITR 无法前滚到目标时间。
- 恢复后验证
- 核对关键表行数、约束、索引、触发器、视图与函数；检查 pg_stat_database、pg_tablespace、pg_locks 等视图确认实例健康。
五、实用恢复命令清单
- 全库 SQL 脚本恢复：psql -h 127.0.0.1 -p 5432 -U postgres -d mydb -f mydb.sql
- 压缩脚本直导：gunzip -c mydb.sql.gz | psql -d mydb
- 自定义格式全库恢复：pg_restore -h 127.0.0.1 -p 5432 -U postgres -d mydb mydb.dump
- 自定义格式按表/模式恢复：pg_restore -d mydb -t schema.tbl mydb.dump 或 -n schema
- 全库逻辑恢复（空集群）：psql –f /backup/pg_all.dmp postgres
- 触发 PITR（PostgreSQL 12+）：echo “restore_command = ‘cp /archive/%f %p’” > $PGDATA/recovery.conf && touch $PGDATA/recovery.signal && systemctl start postgresql-12
- 使用 pg_rman 恢复到时间点：pg_rman restore -B /backup/pg_rman --recovery-target-time ‘2025-12-19 10:30:00’ && systemctl start postgresql-12