硬货！深度解析PostgreSQL三大核心故障

呆呆的私房菜 2025-03-04

Whoami：5年+金融、政府、医疗领域工作经验的DBA
Certificate：PGCM、OCP、YCP
Skill：Oracle、Mysql、PostgreSQL、国产数据库
Platform：CSDN、墨天轮、公众号（呆呆的私房菜）
复制

阅读本文可以了解PostgreSQL生产上的常见故障，助你掌握从现象定位到根治的完整链路。

wal日志堆积导致磁盘爆满

一、故障现象：

数据库突然宕机，日志报错如下：

PANIC: could not write to WAL
复制

pg_wal：目录占用率超过95%，文件数量激增至数千个；
主从复制延迟持续增长，备库同步状态异常。

二、故障分析：

1. 排查归档机制：

select * from pg_stat_archiver
发现last_failed_wal字段非空，显示归档失败记录；


show archive_command;
发现OSS存储桶配额已满，导致WAL无法转储。
复制

2. 复制槽异常

select * from pg_replication_slots;
发现存在active=false的废弃逻辑复制槽；该复制槽导致主库保留所有未消费的wal文件。
复制

3. 检查点配置

show checkpoint_timeout
发现检查点设置过短，频繁触发检查点，导致wal生成速率大于归档速率
复制

三、解决方法：

1. 紧急处理：

# 清理过期wal文件（保留最近24h）
pg_archivecleanup pgdata/pgwal 0000000100000032000000A1# 


删除失效复制槽
select pg_drop_replication_slot('state_slot');
复制

2. 优化配置：

# postgresql.conf  
max_wal_size = 20GB          # 原默认1GB  
min_wal_size = 5GB  
checkpoint_timeout = 30min   # 延长检查点间隔  
wal_compression = on         # 启用LZ4压缩
复制

3. 归档优化：改用pgBackRest替代原生archive_command，支持断点续传与并行上传。

优化效果：

wal磁盘占用率由95%降低到30%；
归档吞吐量提升4倍，峰值时段挤压文件清零。

索引失效引发全表扫描

一、故障现象：

关键报表查询耗时由2s突增到120s；
pg_stat_activity显示大量SeqScan操作；
监控显示IOPS飙升至磁盘极限；

二、故障分析：

1. 索引状态检查：

select * from pg_stat_all_indexes where relname = 'orders';
发现idx_orders_user_id 的 idx_scan 归零，索引未被使用。
复制

2. 统计信息异常：

select last_autoanalyze from pg_stat_all_tables;
发现统计信息两周未更新。
复制

3. 隐式类型转换：

查询语句 where user_id = '123'，其中user_id为bigint触发隐式准换
执行计划显示 Index Scan = > Seq Scan
复制

三、解决方法：

1. 重建索引与统计信息收集：

REINDEX CONCURRENTLY INDEX idx_orders_user_id;  
ANALYZE VERBOSE orders;  
复制

2. 查询重写：

-- 原问题语句  
SELECT * FROM orders WHERE user_id = '123';  


-- 优化后（显式类型转换）  
SELECT * FROM orders WHERE user_id = 123::bigint;  
复制

3. 索引覆盖优化

CREATE INDEX idx_orders_covering ON orders(user_id) INCLUDE (total_amount, status);  
复制

优化效果：

查询耗时从120s降低到0.8s；
磁盘IOPS下降82%，CPU利用率回归正常。

内存泄漏导致OOM崩溃

一、故障现象：

数据库周期性重启，日志报错如下：

Out of memory: Kill process
复制

free -h 显示可用内存持续下降至1%；
连接数激增，pg_stat_activity 存在大量idle in transaction；

二、故障分析：

1. 内存分配分析：

1. 通过 pg_top 发现 postgres 进程RSS内存每小时增长2GB；
2. 发现 work_mem=64MB 设置过高，复杂排序操作耗尽内存。
复制

2. 连接池泄露：

应用使用jdbc未启用连接池，峰值连接数达到3000+；
pg_stat_activity 中 2000+ 连接处于 idle 状态超过1h。
复制

3. 扩展模块异常：

pg_stat_statements 扩展未限制记录数，累计消耗1.2GB内存
复制

三、解决方法：

1. 内存参数调优：

shared_buffers = 25%物理内存  
work_mem = 8MB              # 按需动态调整  
max_connections = 500       # 强制应用使用连接池  
复制

2. 连接池部署：

# pgbouncer.ini  
pool_mode = transaction  
max_client_conn = 1000  
default_pool_size = 20   
复制

3. 泄漏定位工具

-- 查找内存泄漏点  
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;   
复制

优化效果：

内存泄漏问题彻底解决，连续运行几个月无OOM。；
查询平均响应时长降低40%。

系统性优化建议

一、监控体系

1. 部署Prometheus + Granfana，核心监控指标包括：

1. wal生成率/归档延迟；
2. 索引命中率（pg_stat_user_indexes.idx_scan）；
3. 连接池使用率；
复制

二、自动化运维

# 每日自动Vacuum  
pg_cron.schedule('daily_vacuum', '0 3 * * *', 'VACUUM ANALYZE');  


# 周级统计信息刷新  
pg_cron.schedule('weekly_stats', '0 4 * * 6', 'ANALYZE VERBOSE');  
复制

三、容灾设计

采用Patroni+etcd实现分钟级故障切换
跨AZ部署级联复制，WAL归档至异地OSS
复制

本文内容就到这啦，PostgreSQL常见故障及解决方法相信你也掌握了吧！恭喜你又进阶了。我们下篇再见！

索引连接池 select postgresql

文章转载自呆呆的私房菜，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

梧桐

24天前

不错！赞！

24天前

硬货！深度解析PostgreSQL三大核心故障

评论

相关阅读