PostgreSQL 常见故障快速恢复指南

故障分类与优先级

故障分类

紧急故障
- 数据库服务完全不可用
- 主从复制完全中断
- 数据损坏
- 磁盘空间耗尽
严重故障
- 部分服务不可用
- 复制延迟超过阈值
- 大量慢查询
- 高CPU/内存使用率
一般故障
- 单个连接失败
- 小范围性能下降
- 非关键日志报错

恢复优先级

优先级	故障类型	恢复时间目标
P0	紧急故障	立即恢复
P1	严重故障	30分钟内恢复
P2	一般故障	4小时内恢复

连接问题快速恢复

症状识别

应用无法连接到数据库
连接超时错误
"connection refused" 错误
连接数达到上限

恢复步骤

检查PostgreSQL服务状态

bash

# 检查服务状态
systemctl status postgresql

# 如果服务停止，启动服务
systemctl start postgresql

检查监听配置

bash

# 检查是否监听正确的地址和端口
netstat -tlnp | grep postgres

# 或者使用ss命令
ss -tlnp | grep postgres

检查连接数

sql

-- 查看当前连接数
SELECT count(*) FROM pg_stat_activity;

-- 查看最大连接数配置
SHOW max_connections;

-- 查看等待连接的进程
SELECT * FROM pg_stat_activity WHERE state = 'waiting';

释放空闲连接

sql

-- 终止空闲时间超过30分钟的连接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle' 
AND current_timestamp - state_change > interval '30 minutes';

临时调整连接数

sql

-- 临时增加最大连接数
ALTER SYSTEM SET max_connections = 500;

-- 重新加载配置
SELECT pg_reload_conf();

预防措施

配置合理的连接超时参数
使用连接池管理连接
设置空闲连接自动清理机制
监控连接数变化趋势

复制失败快速恢复

症状识别

复制延迟持续增加
从库状态显示"down"
WAL日志堆积
复制槽状态异常

恢复步骤

检查复制状态

sql

-- 主库查看复制状态
SELECT * FROM pg_stat_replication;

-- 从库查看复制状态
SELECT * FROM pg_stat_wal_receiver;

-- 查看复制槽状态
SELECT * FROM pg_replication_slots;

重启WAL接收器

bash

# 从库重启PostgreSQL服务
systemctl restart postgresql

重新初始化从库

bash

# 在从库上执行
# 停止PostgreSQL服务
systemctl stop postgresql

# 清空数据目录
rm -rf /var/lib/postgresql/14/main/*

# 使用pg_basebackup重新同步
pg_basebackup -h 主库IP -p 5432 -U replication -D /var/lib/postgresql/14/main -Fp -Xs -P -R

# 启动从库服务
systemctl start postgresql

修复复制槽问题

sql

-- 删除无效复制槽
SELECT pg_drop_replication_slot('slot_name');

-- 重新创建复制槽
SELECT pg_create_physical_replication_slot('slot_name');

预防措施

监控复制延迟
配置适当的WAL保留策略
使用复制槽确保数据不丢失
定期验证复制完整性

死锁问题快速恢复

症状识别

应用出现"deadlock detected"错误
查询长时间处于等待状态
锁等待图出现循环

恢复步骤

识别死锁

sql

-- 查看当前锁情况
SELECT * FROM pg_locks WHERE NOT granted;

-- 查看锁等待关系
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_query,
       blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

终止阻塞进程

sql

-- 终止阻塞进程
SELECT pg_terminate_backend(blocking_pid);

清理死锁事务

sql

-- 查看长时间运行的事务
SELECT pid, usename, datname, query, state, 
       current_timestamp - xact_start AS xact_duration
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY xact_duration DESC;

-- 终止长时间运行的事务
SELECT pg_terminate_backend(pid);

预防措施

保持事务简短
统一锁定顺序
使用适当的隔离级别
设置合理的锁超时参数
监控锁等待情况

磁盘空间不足快速恢复

症状识别

"no space left on device" 错误
磁盘使用率接近100%
写入操作失败
日志无法滚动

恢复步骤

紧急释放空间

bash

# 删除旧日志文件
find /var/log/postgresql -name "*.log.*" -type f -mtime +7 -delete

# 清理pg_wal目录中的旧文件（仅当确认安全时）
# 注意：此操作可能导致数据丢失，需谨慎
rm -f /var/lib/postgresql/14/main/pg_wal/000000010000000000000001.00000028.backup

检查磁盘使用情况

bash

# 查看磁盘使用率
df -h

# 查看目录大小
du -sh /var/lib/postgresql/*

# 查找大文件
find /var/lib/postgresql -type f -size +100M | sort -nk 5 -r

清理WAL归档

bash

# 清理7天前的WAL归档文件
find /path/to/wal/archive -name "*.backup" -o -name "*.gz" -o -name "*" -type f -mtime +7 -delete

真空清理大表

sql

-- 查看表大小
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) 
FROM pg_stat_user_tables 
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- 执行真空清理
VACUUM FULL VERBOSE ANALYZE large_table;

扩展磁盘空间

bash

# 扩展LVM逻辑卷（如果使用LVM）
lvextend -L +50G /dev/vg_postgres/lv_data

# 扩展文件系统
resize2fs /dev/vg_postgres/lv_data

预防措施

配置自动清理策略
监控磁盘使用率趋势
设置磁盘空间告警阈值
定期执行真空清理
合理规划存储容量

高CPU/内存使用率快速恢复

症状识别

CPU使用率持续超过90%
内存使用率接近100%
系统响应缓慢
大量swap使用

恢复步骤

识别占用资源的进程

bash

# 查看CPU占用最高的进程
top -c

# 查看PostgreSQL进程
ps aux | grep postgres | sort -nk 3 -r

识别消耗资源的查询

sql

-- 查看当前运行的查询及其资源使用情况
SELECT pid, usename, datname, query, state, 
       pg_total_relation_size(relid) AS rel_size,
       current_timestamp - query_start AS query_duration
FROM pg_stat_activity
WHERE query NOT LIKE '%pg_stat_activity%'
ORDER BY query_duration DESC LIMIT 10;

-- 终止消耗资源的查询
SELECT pg_terminate_backend(pid);

调整内存配置

sql

-- 临时调整shared_buffers
ALTER SYSTEM SET shared_buffers = '2GB';

-- 临时调整work_mem
ALTER SYSTEM SET work_mem = '64MB';

-- 重新加载配置
SELECT pg_reload_conf();

优化查询

sql

-- 查看慢查询日志
SELECT * FROM pg_stat_statements 
ORDER BY total_exec_time DESC LIMIT 10;

-- 分析查询计划
EXPLAIN ANALYZE SELECT * FROM large_table WHERE column = 'value';

预防措施

监控CPU/内存使用率
优化查询性能
配置合理的内存参数
定期分析慢查询
考虑垂直或水平扩展

慢查询风暴快速恢复

症状识别

大量慢查询同时运行
数据库响应时间急剧下降
连接队列堆积
锁竞争加剧

恢复步骤

启用慢查询日志

sql

-- 临时启用慢查询日志
ALTER SYSTEM SET log_min_duration_statement = 500;
SELECT pg_reload_conf();

批量终止慢查询

sql

-- 终止所有运行时间超过1分钟的查询
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
AND current_timestamp - query_start > interval '1 minute';

调整查询处理参数

sql

-- 临时降低max_parallel_workers
ALTER SYSTEM SET max_parallel_workers = 2;

-- 调整random_page_cost
ALTER SYSTEM SET random_page_cost = 4;

-- 重新加载配置
SELECT pg_reload_conf();

限流措施

bash

# 使用pgpool-II或其他中间件进行查询限流
# 调整连接池配置

预防措施

实施查询限流
配置合理的查询超时
优化索引设计
定期分析查询性能
实施查询审核机制

数据损坏快速恢复

症状识别

"invalid page header" 错误
表扫描失败
CRC校验错误
数据库启动失败

恢复步骤

确认损坏范围

sql

-- 检查数据库完整性
SELECT pg_relation_check('table_name');

-- 或使用pg_checksums
pg_checksums -c -D /var/lib/postgresql/14/main

从备份恢复

bash

# 停止PostgreSQL服务
systemctl stop postgresql

# 恢复基础备份
tar -xzf /path/to/backup/base.tar.gz -C /var/lib/postgresql/14/main

# 恢复WAL日志
pg_waldump /path/to/wal/archive/000000010000000000000001 | pg_walrestore -D /var/lib/postgresql/14/main

# 启动PostgreSQL服务
systemctl start postgresql

修复单个表

sql

-- 尝试修复损坏的表
REINDEX TABLE table_name;

-- 或使用COPY重建表
CREATE TABLE table_name_new AS SELECT * FROM table_name WHERE 1=0;
COPY table_name_new FROM PROGRAM 'pg_dump -t table_name -a -d dbname';

使用pg_resetwal（仅作为最后手段）

bash

# 注意：此操作可能导致数据丢失
pg_resetwal -f /var/lib/postgresql/14/main

预防措施

启用数据校验和
定期执行完整性检查
实施多副本策略
定期测试恢复流程
使用可靠的存储设备

恢复验证与后续处理

恢复验证步骤

服务可用性验证

bash

# 验证服务是否正常运行
pg_isready -h localhost -p 5432

# 连接测试
psql -U postgres -c "SELECT 1;"

数据完整性验证

sql

-- 检查表计数
SELECT count(*) FROM critical_table;

-- 验证关键数据
SELECT * FROM critical_table WHERE id = 1;

性能验证

sql

-- 检查查询性能
EXPLAIN ANALYZE SELECT * FROM large_table LIMIT 100;

-- 检查系统负载
SELECT * FROM pg_stat_bgwriter;

复制状态验证

sql

-- 验证复制是否正常
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_stat_wal_receiver;

后续处理

故障根因分析
- 记录故障时间、症状和恢复过程
- 分析日志找出根本原因
- 制定预防措施
文档更新
- 更新故障恢复手册
- 记录新的故障类型和恢复方法
- 更新监控告警规则
测试与演练
- 定期进行故障恢复演练
- 测试新的恢复方法
- 验证备份完整性
改进措施
- 优化系统配置
- 增强监控告警
- 改进架构设计
- 提升团队技能

常见问题（FAQ）

Q1: 如何快速识别PostgreSQL故障类型？

A1: 可以通过以下方法快速识别故障类型：

查看PostgreSQL日志文件
使用pg_stat_activity视图查看数据库活动
检查系统资源使用情况
监控告警信息
测试基本连接和查询

Q2: 恢复过程中需要注意哪些事项？

A2: 恢复过程中需要注意：

不要随意终止PostgreSQL进程
避免在恢复过程中进行无关操作
记录恢复步骤和结果
确保备份文件的完整性
恢复后进行验证
及时通知相关人员

Q3: 如何避免类似故障再次发生？

A3: 避免故障再次发生的措施：

实施完善的监控系统
定期进行性能优化
保持数据库更新到最新版本
实施合理的备份策略
定期进行故障演练
培训团队成员

Q4: 什么时候需要寻求外部帮助？

A4: 以下情况建议寻求外部帮助：

无法识别故障原因
恢复过程中遇到技术难题
数据损坏严重
恢复时间超过预期
涉及关键业务系统

Q5: 如何制定有效的故障恢复计划？

A5: 制定故障恢复计划的步骤：

识别关键业务系统和数据
分析可能的故障场景
制定详细的恢复流程
明确责任人和时间要求
定期测试和更新计划
确保所有团队成员熟悉计划

Q6: 如何平衡恢复速度和数据安全性？

A6: 平衡恢复速度和数据安全性的方法：

实施分层备份策略
使用增量备份和差异备份
考虑使用PITR（时间点恢复）
测试不同恢复方法的速度和安全性
根据业务需求调整恢复策略

Q7: 如何监控恢复过程？

A7: 监控恢复过程的方法：

查看PostgreSQL日志
使用系统监控工具监控资源使用
定期检查恢复进度
记录恢复时间和步骤
监控关键指标的变化

Q8: 恢复后如何处理性能问题？

A8: 恢复后处理性能问题的方法：

执行真空清理和重新索引
分析查询计划
调整系统配置参数
优化慢查询
监控性能变化趋势
考虑扩容或优化架构

PostgreSQL 常见故障快速恢复指南 ​

故障分类与优先级 ​

故障分类 ​

恢复优先级 ​

连接问题快速恢复 ​

症状识别 ​

恢复步骤 ​

预防措施 ​

复制失败快速恢复 ​

症状识别 ​

恢复步骤 ​

预防措施 ​

死锁问题快速恢复 ​

症状识别 ​

恢复步骤 ​

预防措施 ​

磁盘空间不足快速恢复 ​

症状识别 ​

恢复步骤 ​

预防措施 ​

高CPU/内存使用率快速恢复 ​

症状识别 ​

恢复步骤 ​

预防措施 ​

慢查询风暴快速恢复 ​

症状识别 ​

恢复步骤 ​

预防措施 ​

数据损坏快速恢复 ​

症状识别 ​

恢复步骤 ​

预防措施 ​

恢复验证与后续处理 ​

恢复验证步骤 ​

后续处理 ​

常见问题（FAQ） ​

Q1: 如何快速识别PostgreSQL故障类型？ ​

Q2: 恢复过程中需要注意哪些事项？ ​

Q3: 如何避免类似故障再次发生？ ​

Q4: 什么时候需要寻求外部帮助？ ​

Q5: 如何制定有效的故障恢复计划？ ​

Q6: 如何平衡恢复速度和数据安全性？ ​

Q7: 如何监控恢复过程？ ​

Q8: 恢复后如何处理性能问题？ ​

PostgreSQL 常见故障快速恢复指南

故障分类与优先级

故障分类

恢复优先级

连接问题快速恢复

症状识别

恢复步骤

预防措施

复制失败快速恢复

症状识别

恢复步骤

预防措施

死锁问题快速恢复

症状识别

恢复步骤

预防措施

磁盘空间不足快速恢复

症状识别

恢复步骤

预防措施

高CPU/内存使用率快速恢复

症状识别

恢复步骤

预防措施

慢查询风暴快速恢复

症状识别

恢复步骤

预防措施

数据损坏快速恢复

症状识别

恢复步骤

预防措施

恢复验证与后续处理

恢复验证步骤

后续处理

常见问题（FAQ）

Q1: 如何快速识别PostgreSQL故障类型？

Q2: 恢复过程中需要注意哪些事项？

Q3: 如何避免类似故障再次发生？

Q4: 什么时候需要寻求外部帮助？

Q5: 如何制定有效的故障恢复计划？

Q6: 如何平衡恢复速度和数据安全性？

Q7: 如何监控恢复过程？

Q8: 恢复后如何处理性能问题？