Ubuntu 上恢复 PostgreSQL 数据的常用方法
一 准备与版本要求
- 确认目标库版本：恢复工具版本需与目标库匹配，建议大于或等于源库版本；如使用自定义格式转储，工具需PostgreSQL 13+。
- 安装客户端工具（若未安装）：sudo apt update && sudo apt install postgresql-client。
- 规划恢复方式：
- 逻辑备份：使用 pg_dump/pg_restore 导入到现有或新建数据库。
- 时间点恢复 PITR：基于基础备份 + WAL 归档恢复到指定时间或 LSN。
- 权限与目录：准备恢复目录，确保 postgres 用户可读写；如从云上下载备份，目标端安装与备份相同大版本的 PostgreSQL。
二 逻辑恢复 使用 pg_dump 与 pg_restore
- 场景：有 .sql 脚本或 自定义格式（-Fc） 的备份文件，恢复到现有或新建数据库。
- 步骤：
- 若目标库已存在且有数据，建议新建空库：
- psql -U postgres -c “CREATE DATABASE targetdb;”
- SQL 脚本恢复（纯文本 .sql）：
- psql -U postgres -d targetdb -f /path/backup.sql
- 自定义格式恢复（-Fc，适合保留对象权限、并行等）：
- pg_restore -U postgres -d targetdb /path/backup.dump
- 如需先清空目标对象冲突：pg_restore -U postgres -d targetdb /path/backup.dump -c
- 验证：
- psql -U postgres -d targetdb -c “\dt”
- psql -U postgres -d targetdb -c “SELECT COUNT(*) FROM your_table;”
- 提示：
- 使用 -Fc 时优先用 pg_restore；纯文本 .sql 用 psql。
- 目标库为空可避免对象冲突；必要时用 -c 清理再导入。
三 时间点恢复 PITR 基于基础备份与 WAL 归档
- 适用：误删表/行后，需恢复到某个时间点或某个 LSN。
- 前提：已配置并验证 WAL 归档（archive_mode=on，archive_command 正常），且有可用的基础备份（如 pg_basebackup 或文件系统拷贝）。
- 步骤：
- 准备恢复目录并放置基础备份（示例：$PGDATA 为 /var/lib/postgresql/16/main）：
- sudo -u postgres cp -a /backup/base/ $PGDATA
- sudo chown -R postgres:postgres $PGDATA
- 在 $PGDATA 下创建恢复信号文件并配置恢复参数（postgresql.conf 或 recovery.conf，取决于版本）：
- touch $PGDATA/recovery.signal
- 基本参数示例：
- restore_command = ‘cp /path/to/wal_archive/%f %p’
- recovery_target_time = ‘2025-11-27 10:00:00’ # 恢复到该时间点
-
或使用 LSN：recovery_target_lsn = ‘0/3000028’
-
如有多时间线：recovery_target_timeline = ‘latest’
- 启动数据库进入恢复模式：
- sudo systemctl start postgresql
- 观察日志，确认达到目标后自动切换为读写：
- tail -f /var/log/postgresql/postgresql-16-main.log
- 成功会看到 “archive recovery complete” 与 “database system is ready to accept connections”
- 验证数据后，可按需移除或重命名 recovery.signal，防止再次进入恢复模式。
- 关键要点：
- 时间点必须落在已归档 WAL 的可恢复范围内；设置过早会丢失后续正确事务，过晚可能报 “recovery ended before configured recovery target was reached”。
- 若需恢复到故障前的最新状态，可用 recovery_target_timeline=latest。
四 常见问题与排查
- 版本不兼容：pg_dump/pg_restore 版本需≥目标库；自定义格式转储建议 13+。
- 权限与目录：恢复目录与数据文件需 postgres:postgres 权限；云上下载的备份先解压到恢复目录再导入。
- 目标库对象冲突：已有同名表/索引/角色会报错，建议用新建空库或加 -c 清理后再导入。
- PITR 目标不可达：出现 “recovery ended before configured recovery target was reached”，将 recovery_target_time 或 recovery_target_lsn 适当提前后重试。
- 云数据库限制：如源库开启 TDE（透明数据加密），通常不支持恢复到自建数据库。