PostgreSQL生产环境故障排查实战指南

1. 生产环境PostgreSQL故障排查全景图在运维PostgreSQL数据库的第七个年头我整理了一份覆盖85%以上生产事故的故障排查清单。不同于官方文档的理论化描述这里记录的每个案例都曾让我们的业务线停摆过从OOM崩溃到主从切换失败从锁等待雪崩到WAL日志撑爆磁盘。下面这些方法是我们用真金白银的停机时间换来的实战经验。2. 高频故障场景与速查指南2.1 连接池耗尽从症状到根治上周刚处理完某电商大促期间的连接池耗尽事故。当应用日志开始大量报remaining connection slots are reserved for non-replication superuser connections时按这个顺序排查紧急扩容30秒生效ALTER SYSTEM SET max_connections 800; -- 默认通常是100 SELECT pg_reload_conf(); -- 无需重启生效连接泄漏分析# 按持续时间排序查看活动连接 psql -c SELECT pid, usename, application_name, now()-query_start AS duration, query FROM pg_stat_activity ORDER BY duration DESC; # 查找空闲事务常见于ORM框架配置不当 psql -c SELECT * FROM pg_stat_activity WHERE stateidle in transaction AND xact_start IS NOT NULL;长期根治方案配置连接池如PgBouncer的transaction模式在应用层添加连接回收检测Spring配置示例spring.datasource.test-on-borrowtrue spring.datasource.validation-querySELECT 1关键指标当pg_stat_activity中idle连接占比超过70%时必须介入处理2.2 WAL日志暴涨不只是磁盘空间问题某次凌晨3点收到磁盘报警发现pg_wal目录占用200GB空间。通过这个检查清单定位检查复制状态SELECT pid, application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication;识别长事务SELECT pid, now()-xact_start AS duration, query FROM pg_stat_activity WHERE state IN (idle in transaction, active) ORDER BY duration DESC;紧急释放空间# 立即创建新WAL段需超级用户权限 psql -c SELECT pg_switch_wal(); # 设置归档超时防止堆积 ALTER SYSTEM SET archive_timeout 300; -- 5分钟我们后来在监控系统添加了这些预警规则当未归档WAL超过10GB触发警告复制延迟超过1GB触发紧急告警2.3 查询雪崩从慢查询到CPU 100%金融系统曾因一个错误索引导致全库CPU满载。现在我们的排查流程是定位问题查询-- 实时查看运行中的查询 SELECT pid, query, now()-query_start AS duration FROM pg_stat_activity WHERE stateactive ORDER BY duration DESC LIMIT 10; -- 检查锁等待 SELECT blocked_locks.pid AS blocked_pid, blocking_locks.pid AS blocking_pid FROM pg_catalog.pg_locks blocked_locks 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;终止恶性查询-- 谨慎操作确认查询可以终止后再执行 SELECT pg_cancel_backend(pid); -- 优雅终止 SELECT pg_terminate_backend(pid); -- 强制终止事后分析工具# 生成执行计划可视化报告 explain.depesz.com explain.tensor.ru3. 硬件级故障处理手册3.1 内存管理OOM杀手降临之后当Linux的OOM Killer干掉Postmaster进程时我们这样处理调整内核参数# 禁止OOM Killer杀死PostgreSQL echo -17 /proc/$(head -1 $PGDATA/postmaster.pid)/oom_adj # 修改系统配置永久生效 sysctl -w vm.overcommit_memory2 sysctl -w vm.overcommit_ratio95优化PostgreSQL配置-- 关键内存参数适用于64GB内存服务器 ALTER SYSTEM SET shared_buffers 16GB; ALTER SYSTEM SET work_mem 128MB; ALTER SYSTEM SET maintenance_work_mem 2GB; ALTER SYSTEM SET effective_cache_size 48GB;3.2 磁盘IO瓶颈当iowait超过30%发现iostat -x显示%util持续90%时的处理步骤临时缓解-- 降低检查点频率 ALTER SYSTEM SET checkpoint_completion_target 0.9; ALTER SYSTEM SET checkpoint_timeout 30min; -- 扩大WAL缓冲区 ALTER SYSTEM SET wal_buffers 16MB;长期解决方案将WAL日志放在单独NVMe磁盘使用表空间分离热点表CREATE TABLESPACE fastspace LOCATION /mnt/nvme_data; ALTER TABLE orders SET TABLESPACE fastspace;4. 复制与高可用故障4.1 主从切换失败手动救火步骤当自动切换失效时我们这样手动恢复确认原主库状态# 检查原主库是否真的不可用 ssh原主库 pg_isready -h 原主IP -p 5432 # 若原主库已脑裂必须先停服 sudo systemctl stop postgresql-12提升从库为新主-- 在从库执行 SELECT pg_promote(); -- 验证新主库状态 SELECT pg_is_in_recovery();重建复制关系# 在新主库创建复制槽 psql -c SELECT * FROM pg_create_physical_replication_slot(standby1); # 在原主库配置恢复为从库 cat $PGDATA/recovery.conf EOF standby_mode on primary_conninfo host新主IP port5432 userreplicator passwordxxx primary_slot_name standby1 EOF5. 监控体系构建建议这是我们经过多次事故后形成的监控指标清单关键指标预警阈值检查频率pg_stat_activity连接数 max_connections*0.830sWAL目录使用率 80%1m复制延迟 1GB30s长事务持续时间 1h5m死锁数量 0实时检查点间隔 5min5m实现示例Prometheus配置- alert: HighReplicationLag expr: pg_replication_lag_bytes 1073741824 # 1GB for: 5m labels: severity: critical annotations: summary: DB replication lag exceeds 1GB6. 故障预防黄金法则定期执行pg_prewarm对核心表提前加载到内存-- 每天凌晨预加载订单表 SELECT pg_prewarm(orders);自动化索引维护使用pg_repack避免锁表pg_repack -d mydb --table orders --no-order --wait-timeout 3600压力测试必查项-- 模拟连接池耗尽 pgbench -c 500 -j 10 -T 600 -- 检查锁争用 SELECT locktype, mode, COUNT(*) FROM pg_locks GROUP BY locktype, mode ORDER BY COUNT(*) DESC;这套方法体系在过去一年帮助我们平均故障恢复时间(MTTR)从47分钟降低到8分钟。最关键的体会是90%的严重故障都有早期预警信号建立完善的监控比任何应急方案都重要。