在 Ubuntu 上恢复 PostgreSQL 数据的实用步骤
一、先判断你的备份类型与恢复路径
- 逻辑备份：由 pg_dump/pg_dumpall 生成，常见为 .sql 文本 或 自定义格式 .dump/.backup。适合按库/按表恢复、跨小版本迁移、开发测试环境重建。
- 物理备份：由 pg_basebackup 或文件系统拷贝得到整个 $PGDATA，配合 WAL 归档 可做时间点恢复（PITR）。适合生产环境灾难恢复、精确到秒级回滚。
- 版本兼容：尽量保持 主版本号一致（如 14.x → 14.y）；若需跨大版本，优先用 pg_dump 生成 SQL 脚本 在低版本导入，避免自定义格式不兼容。
二、逻辑备份恢复（pg_dump/pg_dumpall 产出）
- 准备目标库：若库不存在先创建；确保目标库的角色/权限已存在，否则对象属主/权限可能无法保留。
- SQL 脚本（.sql）恢复：
- 创建空库（如库名 mydb）：createdb -U postgres -T template0 mydb
- 执行脚本：psql -U postgres -d mydb -f backup.sql
- 自定义格式（.dump/.backup）恢复：
- 创建空库：createdb -U postgres -T template0 mydb
- 恢复：pg_restore -U postgres -d mydb -v backup.dump
- 压缩包处理：.gz/.bz2 可先解压再恢复，或直接使用管道：
- gunzip -c backup.dump.gz | psql -U postgres -d mydb
- bunzip2 -c backup.dump.bz2 | psql -U postgres -d mydb
- 验证：连接数据库检查对象与数据，必要时执行 ANALYZE 更新统计信息。
三、物理备份恢复与时间点恢复 PITR（pg_basebackup + WAL 归档）
- 前提条件：一份可用的 基础备份（如 pg_basebackup 生成）与自备份起的 完整 WAL 归档。
- 恢复步骤：
- 准备目录与权限：将基础备份解压/放置到目标 $PGDATA，确保属主为 postgres:postgres，权限 700。
- 配置恢复参数（在 $PGDATA/postgresql.conf 或 standby.signal/recovery.signal 所在目录的 recovery 配置段）：
- 开启恢复模式：创建空文件 standby.signal（PostgreSQL 12+ 推荐）或 recovery.signal（旧版）。
- 指定 WAL 获取：restore_command = ‘cp /path/to/archive/%f %p’
- 指定目标：
- 恢复到最新：recovery_target_timeline = ‘latest’
- 恢复到指定 LSN：recovery_target_lsn = ‘0/2000060’
- 恢复到指定时间：recovery_target_time = ‘2025-12-24 10:00:00’
- 启动实例：pg_ctl -D $PGDATA start。恢复完成后会自动进入可读写状态并删除恢复标记文件。
- 验证：检查日志 “archive recovery complete / database system is ready to accept connections”，登录库确认数据一致性。
- 常见提示：若恢复过早会报 “FATAL: recovery ended before configured recovery target was reached”，将目标时间/LSN 适度延后重试。
四、常见问题与实用建议
- 版本不一致：从高版本向低版本恢复，优先选择 pg_dump 导出为纯 SQL 脚本 再导入；自定义/目录格式可能不兼容。
- 权限与属主：逻辑恢复前确保目标库已存在所需 角色/表空间；否则对象属主/权限可能丢失。
- 统计信息：大规模导入后执行 ANALYZE 提升执行计划质量。
- 演练与验证：定期在测试环境演练恢复流程，核对表数量、关键数据、约束与触发器。
