PostgreSQL 恢复步骤

恢复前准备

1. 环境检查

bash

# 检查目标服务器硬件资源
free -h  # 检查内存
df -h    # 检查磁盘空间

# 检查 PostgreSQL 安装情况
dpkg -l | grep postgresql  # Debian/Ubuntu
yum list installed | grep postgresql  # CentOS/RHEL

# 检查 PostgreSQL 版本
psql --version

2. 备份确认

bash

# 确认备份文件存在和完整性
ls -la /backup/postgresql_full_*.sql
md5sum /backup/postgresql_full_*.sql  # 验证备份文件完整性

# 确认 WAL 文件存在（如果需要 PITR）
ls -la /archive/pg_wal/  # 检查 WAL 归档目录

3. 恢复计划确认

明确恢复目标：全量恢复、时间点恢复（PITR）或特定表恢复
确认恢复点目标（RPO）和恢复时间目标（RTO）
确认恢复顺序和优先级
准备恢复所需的脚本和工具

不同场景的恢复步骤

1. 从 SQL 备份恢复（pg_dump/pg_dumpall）

1.1 全量数据库恢复

bash

# 停止 PostgreSQL 服务
systemctl stop postgresql@15-main

# 清理数据目录（注意：这将删除所有现有数据）
rm -rf /var/lib/postgresql/15/main/*

# 重新初始化数据库集群
pg_createcluster 15 main

# 启动 PostgreSQL 服务
systemctl start postgresql@15-main

# 恢复全量备份
psql -h localhost -U postgres -d postgres -f /backup/postgresql_full_20230101.sql

# 验证恢复结果
psql -h localhost -U postgres -d postgres -c "SELECT datname FROM pg_database;"

1.2 单个数据库恢复

bash

# 创建目标数据库
psql -h localhost -U postgres -c "CREATE DATABASE target_db;"

# 恢复单个数据库
pg_restore -h localhost -U postgres -d target_db /backup/target_db_backup_20230101.dump

# 验证恢复结果
psql -h localhost -U postgres -d target_db -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public';"

2. 从基础备份恢复（pg_basebackup）

2.1 准备恢复环境

bash

# 停止 PostgreSQL 服务
systemctl stop postgresql@15-main

# 清理数据目录
rm -rf /var/lib/postgresql/15/main/*

# 从基础备份恢复
rsync -av /backup/pg_basebackup_20230101/ /var/lib/postgresql/15/main/

# 修复权限
chown -R postgres:postgres /var/lib/postgresql/15/main/

2.2 配置恢复参数

bash

# 创建 recovery.signal 文件
touch /var/lib/postgresql/15/main/recovery.signal

# 配置恢复参数
cat > /var/lib/postgresql/15/main/postgresql.auto.conf << EOF
restore_command = 'cp /archive/pg_wal/%f "%p"'
# 如果需要时间点恢复，添加以下参数
# recovery_target_time = '2023-01-01 12:00:00'
# recovery_target_inclusive = false
EOF

2.3 启动恢复

bash

# 启动 PostgreSQL 服务
systemctl start postgresql@15-main

# 检查恢复状态
tail -f /var/log/postgresql/postgresql-15-main.log

3. 时间点恢复（PITR）

3.1 准备基础备份

bash

# 从基础备份恢复
rsync -av /backup/pg_basebackup_20230101/ /var/lib/postgresql/15/main/

3.2 配置时间点恢复

bash

# 创建 recovery.signal 文件
touch /var/lib/postgresql/15/main/recovery.signal

# 配置时间点恢复参数
cat > /var/lib/postgresql/15/main/postgresql.auto.conf << EOF
restore_command = 'cp /archive/pg_wal/%f "%p"'
recovery_target_time = '2023-01-01 12:00:00'
recovery_target_inclusive = false
recovery_target_action = 'promote'
EOF

3.3 启动恢复并验证

bash

# 启动 PostgreSQL 服务
systemctl start postgresql@15-main

# 检查恢复日志
tail -f /var/log/postgresql/postgresql-15-main.log

# 验证恢复到指定时间点
psql -h localhost -U postgres -c "SELECT now();"

4. 特定表恢复

4.1 使用 pg_restore 恢复特定表

bash

# 查看备份文件中的表列表
pg_restore -l /backup/target_db_backup_20230101.dump | grep -i "TABLE DATA public"

# 恢复特定表
pg_restore -h localhost -U postgres -d target_db -t table_name /backup/target_db_backup_20230101.dump

4.2 使用 COPY 命令恢复特定表

bash

# 从备份中提取特定表的数据
pg_restore -h localhost -U postgres -d postgres -t table_name -a /backup/target_db_backup_20230101.dump > /tmp/table_data.sql

# 恢复到目标表
psql -h localhost -U postgres -d target_db -f /tmp/table_data.sql

5. 从 WAL 归档恢复

bash

# 确保 WAL 归档目录存在
mkdir -p /archive/pg_wal

# 配置 WAL 恢复
cat > /var/lib/postgresql/15/main/postgresql.auto.conf << EOF
restore_command = 'cp /archive/pg_wal/%f "%p"'
recovery_end_command = 'echo "Recovery completed at $(date)" >> /var/log/postgresql/recovery.log'
EOF

恢复验证

1. 数据完整性验证

sql

-- 检查数据库对象数量
SELECT count(*) FROM pg_tables WHERE schemaname = 'public';
SELECT count(*) FROM pg_indexes WHERE schemaname = 'public';

-- 检查关键表的数据量
SELECT count(*) FROM users;
SELECT count(*) FROM orders;

-- 运行数据一致性检查
ANALYZE VERBOSE;

2. 业务功能验证

sql

-- 运行业务验证查询
SELECT * FROM users WHERE user_id = 1;
SELECT * FROM orders WHERE order_date > '2023-01-01';

-- 测试业务流程
INSERT INTO test_table (col1, col2) VALUES ('test1', 'test2');
UPDATE test_table SET col1 = 'updated' WHERE col2 = 'test2';
DELETE FROM test_table WHERE col1 = 'updated';

3. 性能验证

bash

# 检查系统性能
top -p $(pgrep -f postgres)

# 检查 PostgreSQL 性能指标
psql -h localhost -U postgres -c "SELECT * FROM pg_stat_bgwriter;"
psql -h localhost -U postgres -c "SELECT * FROM pg_stat_database;"

恢复后操作

1. 配置调整

sql

-- 调整连接数
ALTER SYSTEM SET max_connections = 200;

-- 调整内存参数
ALTER SYSTEM SET shared_buffers = '4GB';
ALTER SYSTEM SET work_mem = '64MB';

-- 调整 WAL 参数
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_size = '4GB';

2. 备份策略恢复

bash

# 重新配置定时备份
crontab -e
# 添加以下行
0 2 * * * pg_dumpall -h localhost -U postgres -f /backup/postgresql_full_$(date +\%Y\%m\%d).sql

# 验证备份配置
crontab -l

3. 监控和告警恢复

bash

# 重启监控服务
systemctl restart prometheus
systemctl restart grafana-server

# 验证监控数据
curl -s http://localhost:9187/metrics | grep -i postgres

4. 文档更新

记录恢复操作的详细过程
更新恢复计划和流程文档
记录恢复过程中遇到的问题和解决方案
评估恢复效果，包括 RTO 和 RPO 达成情况

常见问题处理

1. 恢复过程中磁盘空间不足

bash

# 扩展磁盘空间
lvextend -L +100G /dev/mapper/vg-root
resize2fs /dev/mapper/vg-root  # ext4 文件系统
xfs_growfs /dev/mapper/vg-root  # XFS 文件系统

# 清理不必要的文件
rm -rf /var/lib/postgresql/15/main/pg_wal/archive_status/*

2. 恢复过程中 WAL 文件缺失

bash

# 检查 WAL 归档目录
ls -la /archive/pg_wal/

# 如果 WAL 文件缺失，考虑使用 pg_resetwal 工具（注意：这可能导致数据丢失）
pgsql -D /var/lib/postgresql/15/main stop
pg_resetwal -f /var/lib/postgresql/15/main
pgsql -D /var/lib/postgresql/15/main start

3. 恢复后数据库无法启动

bash

# 检查日志文件
tail -n 100 /var/log/postgresql/postgresql-15-main.log

# 检查数据目录权限
ls -la /var/lib/postgresql/15/main/
chown -R postgres:postgres /var/lib/postgresql/15/main/

# 检查配置文件语法
pg_ctl -D /var/lib/postgresql/15/main checkconf

常见问题（FAQ）

Q1：如何选择合适的恢复方法？

A1：根据恢复场景选择合适的恢复方法：

全量恢复：使用 pg_dumpall 备份，适用于整个数据库集群恢复
单个数据库恢复：使用 pg_dump 备份，适用于特定数据库恢复
时间点恢复：使用基础备份 + WAL 归档，适用于需要恢复到特定时间点的场景
特定表恢复：使用 pg_restore 或 COPY 命令，适用于单个或少数表的恢复

Q2：如何提高恢复速度？

A2：

使用并行恢复：在 postgresql.auto.conf 中设置 max_parallel_workers_per_gather
使用更快的存储设备：将备份和恢复目标放在 SSD 上
调整恢复参数：设置 wal_buffers 和 checkpoint_segments 等参数
压缩备份文件：减少备份文件大小，加快恢复速度

Q3：如何验证恢复后的数据完整性？

A3：

比较恢复前后的表行数和索引数量
运行业务验证查询，确保关键业务功能正常
使用 pg_dump 导出恢复后的数据，与原备份文件比较大小
运行 ANALYZE 和 VACUUM 命令，检查数据一致性

Q4：如何处理恢复过程中的错误？

A4：

仔细阅读错误日志，确定错误原因
检查备份文件完整性和权限
检查恢复命令语法和参数
确认目标服务器环境满足恢复要求
必要时联系 PostgreSQL 社区或厂商支持

Q5：恢复后如何优化数据库性能？

A5：

运行 ANALYZE 命令更新统计信息
重建索引，优化查询性能
调整内存和连接参数，适应生产环境需求
配置适当的 WAL 参数，平衡性能和安全性
启用查询日志，监控恢复后的性能问题

Q6：如何实现自动化恢复？

A6：

编写恢复脚本，自动化恢复流程
使用配置管理工具（如 Ansible、Puppet）管理恢复过程
结合监控系统，实现自动故障检测和恢复
使用云服务的自动恢复功能（如 AWS RDS 自动恢复）

Q7：如何测试恢复计划的有效性？

A7：

定期进行恢复演练，验证恢复计划的可行性
模拟不同的灾难场景，测试恢复能力
记录演练过程和结果，持续改进恢复计划
邀请业务部门参与恢复验证，确保满足业务需求

PostgreSQL 恢复步骤 ​

恢复前准备 ​

1. 环境检查 ​

2. 备份确认 ​

3. 恢复计划确认 ​

不同场景的恢复步骤 ​

1. 从 SQL 备份恢复（pg_dump/pg_dumpall） ​

1.1 全量数据库恢复 ​

1.2 单个数据库恢复 ​

2. 从基础备份恢复（pg_basebackup） ​

2.1 准备恢复环境 ​

2.2 配置恢复参数 ​

2.3 启动恢复 ​

3. 时间点恢复（PITR） ​

3.1 准备基础备份 ​

3.2 配置时间点恢复 ​

3.3 启动恢复并验证 ​

4. 特定表恢复 ​

4.1 使用 pg_restore 恢复特定表 ​

4.2 使用 COPY 命令恢复特定表 ​

5. 从 WAL 归档恢复 ​

恢复验证 ​

1. 数据完整性验证 ​

2. 业务功能验证 ​

3. 性能验证 ​

恢复后操作 ​

1. 配置调整 ​

2. 备份策略恢复 ​

3. 监控和告警恢复 ​

4. 文档更新 ​

常见问题处理 ​

1. 恢复过程中磁盘空间不足 ​

2. 恢复过程中 WAL 文件缺失 ​

3. 恢复后数据库无法启动 ​

常见问题（FAQ） ​

Q1：如何选择合适的恢复方法？ ​

Q2：如何提高恢复速度？ ​

Q3：如何验证恢复后的数据完整性？ ​

Q4：如何处理恢复过程中的错误？ ​

Q5：恢复后如何优化数据库性能？ ​

Q6：如何实现自动化恢复？ ​

Q7：如何测试恢复计划的有效性？ ​

PostgreSQL 恢复步骤

恢复前准备

1. 环境检查

2. 备份确认

3. 恢复计划确认

不同场景的恢复步骤

1. 从 SQL 备份恢复（pg_dump/pg_dumpall）

1.1 全量数据库恢复

1.2 单个数据库恢复

2. 从基础备份恢复（pg_basebackup）

2.1 准备恢复环境

2.2 配置恢复参数

2.3 启动恢复

3. 时间点恢复（PITR）

3.1 准备基础备份

3.2 配置时间点恢复

3.3 启动恢复并验证

4. 特定表恢复

4.1 使用 pg_restore 恢复特定表

4.2 使用 COPY 命令恢复特定表

5. 从 WAL 归档恢复

恢复验证

1. 数据完整性验证

2. 业务功能验证

3. 性能验证

恢复后操作

1. 配置调整

2. 备份策略恢复

3. 监控和告警恢复

4. 文档更新

常见问题处理

1. 恢复过程中磁盘空间不足

2. 恢复过程中 WAL 文件缺失

3. 恢复后数据库无法启动

常见问题（FAQ）

Q1：如何选择合适的恢复方法？

Q2：如何提高恢复速度？

Q3：如何验证恢复后的数据完整性？

Q4：如何处理恢复过程中的错误？

Q5：恢复后如何优化数据库性能？

Q6：如何实现自动化恢复？

Q7：如何测试恢复计划的有效性？