PostgreSQL 不完全恢复实验：备份恢复实战
概述
本文在 PostgreSQL 中模拟类似 Oracle 不完全恢复的场景，通过 pg_dump 逻辑备份和 pg_restore 恢复的方式，实现数据的时间点恢复。同时介绍 PostgreSQL 的 PITR（Point-In-Time Recovery）机制。
一、实验环境
| 项目 | 信息 |
|---|---|
| 数据库 | PostgreSQL 16.13 |
| 操作系统 | Ubuntu 24.04.4 LTS |
| WAL级别 | replica |
| 归档模式 | 已开启 |
前置条件检查
-- 检查WAL级别（需要replica或logical）
SHOW wal_level;
-- 检查归档模式
SHOW archive_mode;
-- 检查归档命令
SHOW archive_command;
-- 检查当前WAL位置
SELECT pg_current_wal_lsn();
二、PostgreSQL 备份恢复方式对比
| 方式 | 类型 | 说明 | 适用场景 |
|---|---|---|---|
| pg_dump | 逻辑备份 | 单数据库备份，SQL格式 | 小型数据库、迁移 |
| pg_dumpall | 逻辑备份 | 整个集群备份 | 全集群迁移 |
| pg_basebackup | 物理备份 | 文件系统级备份 | PITR、主从复制 |
| pgBackRest | 物理备份 | 第三方备份工具 | 企业级备份 |
| WAL归档 | 增量备份 | 配合基础备份使用 | PITR恢复 |
三、实验流程图
┌─────────────────────────────────────────────────────────────────────────────┐
│ PostgreSQL 备份恢复实验流程 │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ 时间线: │
│ ──────────────────────────────────────────────────────────────────────▶ │
│ │
│ T1: 初始数据 T2: pg_dump备份 T3: 正常操作 T4: 误操作 │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │ 5条记录 │ ──▶ │ pg_dump │ ───▶ │ INSERT │ ──▶ │ DELETE │ │
│ │ │ │ -F c │ │ UPDATE │ │ 全表删除 │ │
│ └─────────┘ └─────────┘ └─────────┘ └─────────┘ │
│ │ │ │
│ ▼ ▼ │
│ ┌─────────────┐ ┌───────────┐ │
│ │ 备份文件 │ │ 恢复目标 │ │
│ │ .dump │ │ 时间点 T2 │ │
│ └─────────────┘ └───────────┘ │
│ │ ▲ │
│ │ ┌─────────────┐ │ │
│ └────────▶│ pg_restore │────────────┘ │
│ │ 恢复数据 │ │
│ └─────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
四、详细步骤
步骤1：开启归档模式（PITR前提）
# 创建归档目录
sudo mkdir -p /var/lib/postgresql/16/archive
sudo chown postgres:postgres /var/lib/postgresql/16/archive
# 修改postgresql.conf配置
sudo tee -a /etc/postgresql/16/main/postgresql.conf > /dev/null << 'EOF'
# PITR Archive Settings
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/16/archive/%f'
EOF
# 重启PostgreSQL
sudo systemctl restart postgresql
# 验证配置
psql -c "SHOW archive_mode;"
psql -c "SHOW archive_command;"
验证结果：
archive_mode
--------------
on
archive_command
-----------------------------------------
cp %p /var/lib/postgresql/16/archive/%f
步骤2：准备测试数据
-- 创建测试数据库
DROP DATABASE IF EXISTS recovery_test;
CREATE DATABASE recovery_test;
-- 连接到测试数据库
\c recovery_test
-- 创建测试表
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE,
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入初始数据
INSERT INTO employees (name, department, salary, hire_date) VALUES
('张三', '技术部', 15000.00, '2020-01-15'),
('李四', '销售部', 12000.00, '2020-03-20'),
('王五', '财务部', 13000.00, '2020-06-10'),
('赵六', '技术部', 16000.00, '2020-08-25'),
('钱七', '人事部', 11000.00, '2021-01-05');
初始数据：
| id | name | department | salary | hire_date |
|---|---|---|---|---|
| 1 | 张三 | 技术部 | 15000.00 | 2020-01-15 |
| 2 | 李四 | 销售部 | 12000.00 | 2020-03-20 |
| 3 | 王五 | 财务部 | 13000.00 | 2020-06-10 |
| 4 | 赵六 | 技术部 | 16000.00 | 2020-08-25 |
| 5 | 钱七 | 人事部 | 11000.00 | 2021-01-05 |
步骤3：执行pg_dump备份
# 创建备份目录
mkdir -p /tmp/pg_pitr_backup
# 执行pg_dump备份（自定义格式）
pg_dump -U itdba -h localhost -d recovery_test \
-F c \
-f /tmp/pg_pitr_backup/base_backup_20260320.dump
# 查看备份文件
ls -la /tmp/pg_pitr_backup/
关键参数说明：
| 参数 | 说明 |
|---|---|
-F c |
自定义格式（压缩，支持并行恢复） |
-F t |
tar格式 |
-F p |
纯SQL文本格式 |
-F d |
目录格式（支持并行备份） |
-j N |
并行工作线程数（目录格式） |
备份时间点： 2026-03-20 11:42:51
步骤4：正常业务操作
-- 插入新员工
INSERT INTO employees (name, department, salary, hire_date) VALUES
('孙八', '技术部', 14000.00, '2021-03-15'),
('周九', '销售部', 12500.00, '2021-05-20');
-- 更新薪资（技术部涨薪10%）
UPDATE employees SET salary = salary * 1.1 WHERE department = '技术部';
正常操作后数据：
| id | name | department | salary | hire_date |
|---|---|---|---|---|
| 1 | 张三 | 技术部 | 16500.00 | 2020-01-15 |
| 2 | 李四 | 销售部 | 12000.00 | 2020-03-20 |
| 3 | 王五 | 财务部 | 13000.00 | 2020-06-10 |
| 4 | 赵六 | 技术部 | 17600.00 | 2020-08-25 |
| 5 | 钱七 | 人事部 | 11000.00 | 2021-01-05 |
| 6 | 孙八 | 技术部 | 15400.00 | 2021-03-15 |
| 7 | 周九 | 销售部 | 12500.00 | 2021-05-20 |
正常操作完成时间： 2026-03-20 11:42:53
步骤5：模拟误操作
-- ⚠️ 模拟误操作：忘记WHERE条件，删除全表
DELETE FROM employees;
误操作后数据：
(空表，0条记录)
误操作时间： 2026-03-20 11:42:56
步骤6：执行恢复
# 步骤1：删除当前数据库
psql -U itdba -d postgres -c "DROP DATABASE IF EXISTS recovery_test;"
# 步骤2：重新创建数据库
psql -U itdba -d postgres -c "CREATE DATABASE recovery_test;"
# 步骤3：恢复备份
pg_restore -U itdba -h localhost -d recovery_test \
/tmp/pg_pitr_backup/base_backup_20260320.dump
步骤7：验证恢复结果
-- 查看恢复后的数据
SELECT * FROM employees;
-- 统计数据
SELECT COUNT(*) AS total, SUM(salary) AS total_salary FROM employees;
恢复后数据：
| id | name | department | salary | hire_date |
|---|---|---|---|---|
| 1 | 张三 | 技术部 | 15000.00 | 2020-01-15 |
| 2 | 李四 | 销售部 | 12000.00 | 2020-03-20 |
| 3 | 王五 | 财务部 | 13000.00 | 2020-06-10 |
| 4 | 赵六 | 技术部 | 16000.00 | 2020-08-25 |
| 5 | 钱七 | 人事部 | 11000.00 | 2021-01-05 |
五、恢复结果对比
| 阶段 | 记录数 | 薪资总计 | 说明 |
|---|---|---|---|
| 初始数据 | 5 | 67000.00 | 备份前 |
| 正常操作后 | 7 | 98000.00 | 误操作前 |
| 误操作后 | 0 | 0 | DELETE全表 |
| 恢复后 | 5 | 67000.00 | ✅ 恢复到备份点 |
六、PostgreSQL PITR（基于时间点恢复）
6.1 PITR原理
┌─────────────────────────────────────────────────────────────────────────────┐
│ PostgreSQL PITR 恢复原理 │
├─────────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ pg_basebackup│ │ WAL归档 │ │ 恢复目标 │ │
│ │ (基础备份) │ │ (增量日志) │ │ (时间点) │ │
│ └──────┬──────┘ └──────┬──────┘ └──────┬──────┘ │
│ │ │ │ │
│ ▼ ▼ ▼ │
│ ┌─────────────────────────────────────────────────────────────┐ │
│ │ 恢复流程 │ │
│ │ 1. 恢复基础备份到数据目录 │ │
│ │ 2. 配置recovery_target_time/recovery_target_lsn │ │
│ │ 3. 启动PostgreSQL，自动重放WAL到目标点 │ │
│ │ 4. 数据库恢复到指定时间点状态 │ │
│ └─────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────────┘
6.2 PITR恢复步骤
# 1. 停止PostgreSQL服务
sudo systemctl stop postgresql
# 2. 清空数据目录
sudo rm -rf /var/lib/postgresql/16/main/*
# 3. 恢复基础备份
sudo tar -xzf /backup/base.tar.gz -C /var/lib/postgresql/16/main/
# 4. 创建recovery配置
cat > /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
restore_command = 'cp /var/lib/postgresql/16/archive/%f %p'
recovery_target_time = '2026-03-20 11:42:53'
recovery_target_action = 'promote'
EOF
# 5. 创建恢复信号文件
touch /var/lib/postgresql/16/main/recovery.signal
# 6. 启动PostgreSQL
sudo systemctl start postgresql
# 7. 验证恢复
psql -c "SELECT * FROM employees;"
6.3 恢复目标选项
| 参数 | 说明 |
|---|---|
recovery_target_time |
恢复到指定时间点 |
recovery_target_xid |
恢复到指定事务ID |
recovery_target_lsn |
恢复到指定WAL位置 |
recovery_target_name |
恢复到命名恢复点 |
recovery_target_immediate |
恢复到最早一致点后停止 |
七、与MySQL/Oracle对比
| 对比项 | Oracle | MySQL | PostgreSQL |
|---|---|---|---|
| 恢复类型 | 不完全恢复 | PITR | PITR |
| 物理备份 | RMAN | mysqlbackup | pg_basebackup |
| 逻辑备份 | expdp | mysqldump | pg_dump |
| 增量日志 | 归档日志 | Binlog | WAL |
| 时间点恢复 | SET UNTIL TIME |
--stop-datetime |
recovery_target_time |
| SCN/LSN恢复 | SET UNTIL SCN |
--stop-position |
recovery_target_lsn |
| 表级恢复 | 表空间传输 | binlog过滤 | pg_restore -t |
八、最佳实践建议
8.1 备份策略
| 策略 | 说明 |
|---|---|
| 基础备份频率 | 每天至少一次 |
| WAL归档 | 实时归档到远程存储 |
| 备份保留 | 至少保留7天 |
| 备份验证 | 定期进行恢复演练 |
8.2 归档配置建议
-- 推荐的归档配置
archive_mode = on
archive_command = 'rsync -a %p backup_server:/archive/%f'
archive_timeout = 300 -- 5分钟强制切换
wal_keep_size = 1GB -- 保留足够的WAL
8.3 恢复流程
1. 立即停止应用写入
2. 记录误操作时间点
3. 备份当前WAL文件
4. 恢复基础备份
5. 配置恢复目标
6. 启动数据库重放WAL
7. 验证数据完整性
8. 恢复应用服务
8.4 注意事项
| 注意项 | 说明 |
|---|---|
| wal_level | 需要设置为replica或logical |
| archive_mode | 必须开启才能PITR |
| WAL保留 | 确保WAL文件不被删除 |
| 恢复测试 | 生产操作前先测试验证 |
九、常用命令速查
9.1 备份相关
# 逻辑备份（自定义格式）
pg_dump -U postgres -F c -f backup.dump database_name
# 逻辑备份（SQL文本）
pg_dump -U postgres -F p -f backup.sql database_name
# 整个集群备份
pg_dumpall -U postgres > cluster_backup.sql
# 物理备份
pg_basebackup -h localhost -D /backup/base -Ft -z -Xs -P
9.2 恢复相关
# 恢复自定义格式备份
pg_restore -U postgres -d database_name backup.dump
# 恢复SQL文本
psql -U postgres -d database_name < backup.sql
# 并行恢复
pg_restore -U postgres -d database_name -j 4 backup.dump
# 仅恢复表结构
pg_restore -U postgres -s -d database_name backup.dump
9.3 WAL相关
-- 查看当前WAL位置
SELECT pg_current_wal_lsn();
-- 查看WAL文件列表
SELECT * FROM pg_ls_waldir();
-- 手动切换WAL
SELECT pg_switch_wal();
-- 创建恢复点
SELECT pg_create_restore_point('before_upgrade');
十、总结
PostgreSQL 提供了多种备份恢复方式：
- pg_dump/pg_restore：逻辑备份，适合小型数据库和单库恢复
- pg_basebackup + WAL归档：物理备份，支持PITR时间点恢复
- 第三方工具：pgBackRest、Barman等企业级备份方案
关键要点：
- 开启归档模式是PITR的前提
- 定期执行基础备份
- WAL文件需要妥善保存
- 恢复前备份当前状态
- 恢复后验证数据完整性
浙公网安备 33010602011771号