Ubuntu 上 PostgreSQL 恢复技巧与实操步骤
一、先选对恢复路径
- 逻辑恢复：使用 pg_dump/pg_dumpall 生成的 .sql/.dump 文件，适合单库/多库迁移、跨版本升级、选择性对象恢复。恢复时 SQL 脚本用 psql 执行，自定义格式用 pg_restore。
- 物理恢复：使用 pg_basebackup 的全量物理备份，适合整机/集群级恢复、快速回滚、以及需要 时间点恢复 PITR 的场景。恢复时“替换数据目录 + 回放 WAL”。
- 第三方工具：如 pg_rman，支持在线备份与 PITR，适合企业级备份策略（需额外安装与维护）。
二、逻辑恢复步骤（pg_dump/pg_dumpall）
- 场景：误删表/数据、迁移到新库、结构+数据恢复。
- 准备：确认备份文件类型（纯 SQL 或自定义归档）、目标库是否存在、连接权限与 search_path。
- 恢复命令：
- 纯 SQL 备份（.sql）：
- 创建空库（如不存在）：createdb -U postgres -O owner dbname
- 执行恢复：psql -U postgres -d dbname -f backup.sql
- 自定义归档（.dump，便于选择性恢复）：
- 全库恢复：pg_restore -U postgres -d dbname backup.dump
- 只恢复某表/模式：pg_restore -U postgres -d dbname -t schema.table backup.dump
- 常见要点：
- 大库建议用压缩归档（pg_dump 的 -F c），恢复更快更稳。
- 若备份包含创建库语句，恢复时可先不建库，直接执行到目标库；否则先建库再恢复。
- 恢复失败多半是权限/属主/搜索路径问题，可在 psql 中 \set ON_ERROR_STOP on 定位。
三、物理恢复与时间点恢复 PITR（pg_basebackup）
- 前提条件：
- 已做过一次 pg_basebackup 基础备份；
- 已开启 WAL 归档：postgresql.conf 中设置
- wal_level = replica
- archive_mode = on
- archive_command = ‘cp %p /path/to/archive/%f’（确保归档目录可写且磁盘充足）
- 恢复到最新状态（回滚到备份后最新一致点）：
- 停止服务：sudo systemctl stop postgresql@-main
- 备份当前数据目录（可选）：mv /var/lib/postgresql//main /var/lib/postgresql//main.bak
- 将基础备份内容拷回数据目录：cp -a /path/to/basebackup/* /var/lib/postgresql//main/
- 创建恢复信号：touch /var/lib/postgresql//main/recovery.signal（PostgreSQL 12+ 使用此文件触发恢复）
- 启动服务：sudo systemctl start postgresql@-main（服务会自动回放 WAL 到最新并完成恢复）
- 时间点恢复（精确到某时刻/事务）：
- 按上节步骤 1–4 准备恢复环境；
- 在 数据目录 创建（或修改）恢复参数文件（PostgreSQL 12+ 可在 postgresql.auto.conf 中设置，或创建 recovery.signal 的同目录下放置 recovery.conf 片段）：
- recovery_target_time = ‘2025-11-15 10:00:00’（误删前的时间点）
- recovery_target_action = ‘promote’（达到目标后自动提升为主库）
- restore_command = ‘cp /path/to/archive/%f %p’（与备份端一致）
- 可选：recovery_target_timeline = ‘latest’（遵循时间线分支）
- 启动服务，观察日志直至出现 “archive recovery complete”，随后数据库自动提升；
- 连接验证：psql -U postgres -c “SELECT count(*) FROM your_table;”。
- 小贴士：
- 找不到数据目录时，可在 psql 中执行 SHOW data_directory; 确认；
- 若归档不完整，恢复会在缺段处停止，需补齐归档或调整目标时间。
四、常见恢复场景与命令速查
| 场景 |
备份来源 |
恢复要点 |
常用命令 |
| 误删表，恢复到 1 分钟前 |
pg_basebackup + WAL 归档 |
配置 recovery_target_time，启动到目标后自动 promote |
见第三节 PITR |
| 整库回滚到昨天的备份 |
pg_basebackup |
用昨天的 basebackup + 归档回放到最新 |
见第三节 物理恢复到最新 |
| 只恢复单库/单表 |
pg_dump 自定义归档 |
用 pg_restore 指定 -t/-n，目标库需存在 |
pg_restore -U postgres -d db -t schema.table file.dump |
| 迁移到新服务器 |
pg_dump 或 pg_basebackup |
逻辑迁移更灵活；物理迁移更快但需同平台/版本相近 |
见第二、三节 |
| 恢复全局对象（角色/表空间） |
pg_dumpall -g |
先恢复 globals.sql，再恢复各库 |
psql -U postgres -f globals.sql |
| 用 pg_rman 做企业备份 |
pg_rman 基础/增量 |
需初始化 catalog、配置归档；支持 PITR |
pg_rman backup …；pg_rman restore … |
五、排错与加固要点
- 权限与属主：数据目录与归档目录应为 postgres:postgres，权限 700/750；恢复后检查关键对象属主。
- 归档健康：定期执行 SELECT pg_switch_wal(); 并 ls 归档目录确认文件生成；若归档命令失败，WAL 不会切走，磁盘可能撑爆。
- 版本匹配：跨小版本恢复通常可行，跨大版本建议用 逻辑备份 或按官方升级路径执行。
- 一致性校验：恢复后用 psql 校验表数量、重要数据抽样、最近事务时间点（SELECT now() - txid_current_snapshot();）。
- 演练与监控：定期做恢复演练，监控 archive_command 返回值 与 WAL 堆积，并保留多份历史备份（如近 7 天日备 + 近 4 周一备）。