Linux系统PostgreSQL如何恢复

Linux系统下PostgreSQL数据库恢复指南

PostgreSQL数据库恢复需根据备份类型（逻辑备份、物理备份、WAL日志）选择对应方法，以下是详细操作步骤及注意事项：

一、恢复前准备工作

确认备份文件完整性
确保拥有有效的备份文件，包括：
- 逻辑备份：通过pg_dump生成的SQL文件（如db_backup.sql）或自定义格式文件（如db_backup.dump）；
- 物理备份：通过pg_basebackup生成的数据库集群副本（如/var/lib/postgresql/12/main目录）；
- WAL日志：Write-Ahead Logging日志（如pg_wal目录下的文件），用于点恢复。
停止PostgreSQL服务
为避免数据冲突，恢复前需停止数据库服务：
```
sudo systemctl stop postgresql
```
准备恢复环境
- 确保Linux系统已安装与备份时相同版本的PostgreSQL（可通过pg_dump --version验证）；
- 备份文件需复制到服务器本地（如/tmp目录），并确保PostgreSQL用户（通常为postgres）有读取权限。

二、根据备份类型选择恢复方法

1. 逻辑备份恢复（SQL/自定义格式）

逻辑备份是最常用的恢复方式，适用于需要迁移数据库或恢复特定对象的场景。

SQL格式备份恢复（.sql文件）：
使用psql命令直接导入备份文件，需指定数据库名和备份文件路径：
```
sudo -u postgres psql -f /path/to/db_backup.sql
```
自定义格式备份恢复（.dump文件）：
使用pg_restore命令，支持选择性恢复（如特定表、数据）：
```
sudo -u postgres pg_restore -d your_database_name /path/to/db_backup.dump
```
常用选项：
- --table=table_name：仅恢复指定表；
- --data-only：仅恢复数据（不包含表结构）；
- --schema=schema_name：恢复到指定模式。

2. 物理备份恢复（pg_basebackup）

物理备份是数据库集群的完整副本，适用于大规模数据恢复或系统崩溃场景。

步骤：
1. 停止PostgreSQL服务（见“恢复前准备”）；
2. 清空现有数据库目录（如/var/lib/postgresql/12/main），避免旧数据冲突；
3. 将物理备份文件复制到数据库目录；
4. 启动PostgreSQL服务，系统会自动应用WAL日志完成恢复：
```
sudo systemctl start postgresql
```
注意：若备份时开启了流复制，可使用pg_basebackup重新同步数据：
```
sudo -u postgres pg_basebackup -h hostname -U username -D /var/lib/postgresql/12/main -R
```
其中-R表示自动配置recovery.conf（PostgreSQL 12及以上版本已整合到postgresql.conf）。

3. WAL日志点恢复（时间点/事务ID恢复）

若需要恢复到特定时间点（如误删除数据的时间）或事务ID，需结合物理备份和WAL日志进行点恢复。

配置恢复参数：
编辑数据库目录下的postgresql.conf文件，设置恢复模式及目标：

restore_command = 'cp /path/to/archive/%f %p'  # 从归档目录复制WAL日志
recovery_target_time = '2025-10-01 12:00:00'  # 恢复到指定时间（可选）
recovery_target_xid = '123456789'             # 恢复到指定事务ID（可选）
recovery_target_name = 'recovery_point'       # 恢复到指定恢复点名称（可选）

说明：

restore_command：用于从归档目录获取WAL日志（需提前将WAL日志归档到指定路径）；
三个恢复目标参数只需设置一个，优先级为recovery_target_name > recovery_target_time > recovery_target_xid。

启动恢复：
无需重启服务，直接发送信号触发恢复：
```
sudo -u postgres touch /var/lib/postgresql/12/main/recovery.signal
```
或修改postgresql.conf中的pause_at_recovery_target为on，暂停在目标点以便验证：
```
pause_at_recovery_target = on
```
完成恢复：
恢复完成后，PostgreSQL会自动重命名recovery.signal为recovery.done，并进入正常运行状态。可通过日志文件（/var/log/postgresql/postgresql-12-main.log）监控恢复进度。

三、恢复后验证

检查数据库状态：
登录PostgreSQL，列出所有数据库和表，确认结构是否完整：

sudo -u postgres psql -c "\l"  # 列出所有数据库
sudo -u postgres psql -d your_database_name -c "\dt"  # 列出指定数据库的所有表

验证数据内容：
查询关键表的数据行数或具体字段，确认数据是否正确：

sudo -u postgres psql -d your_database_name -c "SELECT COUNT(*) FROM your_table;"
sudo -u postgres psql -d your_database_name -c "SELECT * FROM your_table LIMIT 10;"

测试应用连接：
让应用程序连接数据库，验证业务功能是否正常。

四、注意事项

测试恢复流程：
定期在测试环境中模拟恢复过程，确保备份文件有效且恢复步骤正确，避免生产环境恢复失败。
备份文件存储：
将备份文件存储在异地（如云存储、另一台服务器），防止本地磁盘损坏导致备份丢失。
版本一致性：
恢复环境的PostgreSQL版本需与备份时的版本一致，避免因版本差异导致恢复失败。
权限问题：
确保恢复操作由postgres用户或具有足够权限的用户执行，避免权限不足导致文件无法访问。