Ubuntu中PostgreSQL数据库恢复技巧

Ubuntu下PostgreSQL数据库恢复技巧

一、常见恢复场景与对应方法

逻辑备份恢复（自定义/目录/TAR 或 SQL 脚本）：使用 pg_restore 或 psql 将备份导入到目标库，适合单库迁移、结构+数据恢复、跨机器恢复。
时间点恢复 PITR（WAL 归档）：基于基础备份 + WAL 归档 恢复到指定时间点或最新状态，适合误删表/行、介质故障、回滚错误事务。
版本兼容恢复：高版本备份在低版本恢复时，优先导出为 纯 SQL 脚本 再导入，规避自定义格式不兼容问题。
图形化工具恢复：使用 pgAdmin 的 Restore 向导执行自定义/目录/TAR 备份恢复，便于可视化管理。
以上方法覆盖日常运维与应急场景，建议按备份类型选择对应路径。

二、逻辑备份恢复步骤

准备目标库：先创建空库（注意与备份时一致的 owner/编码/模板 等），例如：sudo -u postgres createdb your_db。
自定义/目录/TAR 备份（.dump/.tar/.dir）：
- pg_restore -U postgres -d your_db -v /path/backup.dump
- 常用选项：-c（清理目标对象）、-n schema（仅某 schema）、-t table（仅某表）、-j N（并行导入）。
SQL 脚本备份（.sql）：
- psql -U postgres -d your_db -f /path/backup.sql
验证：连接数据库检查对象与数据，例如 \dt、SELECT COUNT(*) FROM your_table;
图形化方式：在 pgAdmin 中右键目标库 → Restore → 选择备份文件与格式 → 执行并查看日志。

三、时间点恢复 PITR 步骤

前提条件：已配置 WAL 归档（生产建议长期开启），并有可用的基础备份（如 basebackup）。
配置恢复参数（在目标数据目录的 postgresql.conf 或 recovery 配置段）：
- wal_level = replica
- archive_mode = on
- archive_command = ‘cp %p /var/lib/postgresql/pgbackup/archive_wals/%f’
执行基础备份（示例）：
- pg_basebackup -h primary_host -D /var/lib/postgresql/13/main -Ft -z -P
进入恢复模式：
- 在目标数据目录创建空文件：touch recovery.signal
- 配置恢复目标（三选一或组合）：
  - 恢复到最新：recovery_target_timeline = ‘latest’
  - 恢复到指定时间：recovery_target_time = ‘2025-12-10 10:00:00+08’
  - 恢复到指定 LSN：recovery_target_lsn = ‘0/12345678’
- 指定 WAL 获取方式：restore_command = ‘cp /var/lib/postgresql/pgbackup/archive_wals/%f %p’
启动实例：pg_ctl -D /var/lib/postgresql/13/main start
观察日志：tail -f server.log，看到 “archive recovery complete” 后，实例会自动以 read/write 模式打开；如需保留为备用库，可改为 standby_mode = on 并配置 primary_conninfo。
归档完整性检查：必要时手动切换 WAL 验证归档是否正常，例如：SELECT pg_switch_wal();。

四、版本兼容与常见错误处理

高版本到低版本恢复：
- 问题：自定义/目录/TAR 备份可能包含新版本特性，低版本 pg_restore 不兼容。
- 解决：备份时用 pg_dump -F p（纯 SQL 脚本），在低版本用 psql 导入。
权限与连接：
- 使用 postgres 系统用户或具备 CREATEDB/SUPERUSER 权限的角色执行恢复；必要时在命令前加 sudo -u postgres。
目标库状态：
- 恢复前确保目标库为空（或使用 -c 清理），避免对象冲突。
归档断档：
- 恢复时报 “could not open file … No such file or directory”，多为 WAL 归档缺失；需补齐归档或改用更早的基础备份。
时间/时区：
- 使用 recovery_target_time 时，注意与服务器时区一致，建议以 UTC 记录与指定。
空间与性能：
- 大库恢复时启用 -j 并行（自定义/目录格式），并确保 archive_command 目标磁盘空间充足。

五、实用命令清单

创建库：sudo -u postgres createdb your_db
逻辑恢复：
- pg_restore -U postgres -d your_db -v /path/backup.dump
- psql -U postgres -d your_db -f /path/backup.sql
基础备份：pg_basebackup -h primary_host -D /var/lib/postgresql/13/main -Ft -z -P
触发 WAL 切换：SELECT pg_switch_wal();
进入恢复：touch $PGDATA/recovery.signal
启动/查看日志：pg_ctl -D $PGDATA start；tail -f server.log
图形化：pgAdmin → 目标库 → Restore（选择 .dump/.sql 与相应选项）。

最新问答