数据库教程FGMT69：PostgreSQL运维诊断与监控分析

数据库教程FGMT69：PostgreSQL运维诊断与监控分析

前言

随着业务系统数据体量持续增长，关系型数据库作为业务底层核心存储组件，数据库的稳定运行、性能故障定位、日常状态巡检已经成为运维体系中不可缺少的一环。PostgreSQL具备强大的扩展能力、事务可靠性与丰富的内置统计信息模块，但是它的内部结构相对复杂，很多运维人员在面对慢查询、连接爆满、IO阻塞、锁等待、缓存命中率异常等问题时，缺少一套系统化的诊断思路，只会依靠简单查询语句查看表面指标，很难定位根因。风哥教程本文围绕PostgreSQL运维诊断与监控分析完整体系进行讲解，从底层原理、监控架构、核心视图、巡检规范，到实战故障排查、性能调优操作，完整覆盖生产环境数据库运维工作场景。本套风哥教程面向数据库运维工程师、DBA、后端开发人员，学习完成之后，可以独立完成数据库日常监控部署、常态化巡检、SQL性能诊断、锁问题排查、资源瓶颈定位，处理生产中绝大多数PostgreSQL数据库故障场景。

风哥教程本文内容大纲

第一部分：PostgreSQL监控体系基础理论。包含数据库统计信息原理、后台进程架构、共享内存结构、监控指标分类、性能瓶颈定位方法论，讲解各类指标的含义与评判标准，区分系统层指标与数据库内核层指标，理解数据库收集统计数据的机制，明白视图数据的来源与数据更新规则。
第二部分：系统资源层面监控与诊断。包含服务器CPU、内存、磁盘IO、网络的监控方法，操作系统工具的使用，64G内存8CPU主机参数规划，操作系统内核参数配置，资源瓶颈识别方法，主机名fgedu-net-cn1、fgedu-net-cn2双节点环境资源分析。
第三部分：PostgreSQL内置统计视图详解。pg_stat系列视图、pg_stat_activity、pg_stat_statements、pg_locks、pg_wait_events等核心视图字段解析，讲解如何通过视图获取会话信息、等待事件、SQL执行统计、锁信息，区分活跃会话、空闲会话、长事务会话。
第四部分：数据库连接、事务、锁与等待事件诊断。讲解事务隔离级别、长事务危害、锁类型、锁等待原理，识别死锁、表锁、行锁冲突，定位会话阻塞链，终止异常会话，规避锁冲突引发业务卡顿。
第五部分：SQL性能诊断与慢查询分析。开启慢查询日志，配置日志参数，分析慢查询日志，使用pg_stat_statements抓取TOP消耗SQL，查看执行计划，识别全表扫描、低效索引、不合理JOIN，完成SQL优化。
第六部分：缓存与内存诊断分析。共享缓冲区、work_mem、maintenance_work_mem等内存结构原理，缓存命中率计算，识别内存不足导致大量磁盘读写，基于64G内存8CPU主机规格进行内存参数调优。
第七部分：WAL日志诊断与IO性能分析。WAL写入原理，WAL相关参数，WAL日志膨胀、归档异常诊断，检查点机制，IO高负载场景定位，WAL预写日志对业务性能的影响。
第八部分：数据库日常巡检实战。构建常态化巡检清单，自动化巡检脚本编写，巡检报告输出，区分告警指标与预警指标，定期数据库健康评估。
第九部分：典型故障实战诊断案例。包含连接数打满、锁阻塞、慢SQL引发CPU飙升、内存交换、磁盘IO瓶颈、长事务导致VACUUM无法清理垃圾元组，一步步复现、定位、解决故障。
第十部分：监控方案搭建实战。搭建基础监控体系，指标采集，告警阈值设置，区分操作系统监控与数据库内核监控，双节点fgedu-net-cn1、fgedu-net-cn2监控部署。

风哥 itpux-com

一、PostgreSQL监控体系基础理论

数据库监控本质是持续采集系统与数据库内核运行指标，通过指标变化趋势，提前识别潜在风险，在业务故障发生前预警；当故障发生时，依靠监控数据快速定位问题根源。PostgreSQL监控分为两大层级：操作系统层监控、数据库内核层监控，两层指标需要结合起来分析，不能单独只看数据库内部指标。

PostgreSQL是多进程架构，每个客户端连接对应一个backend后台进程，所有进程共享一块共享内存区域。共享内存主要包含共享缓冲区、WAL缓冲区、锁管理区域、统计信息共享段。所有后台进程会持续采集运行统计数据，写入共享内存的统计段，pg_stat系列视图就是读取这块共享内存的数据，部分统计数据会在数据库正常关闭时持久化，异常重启会重置部分统计计数器。

按照指标类型划分，监控指标分为资源指标、会话指标、SQL执行指标、锁与等待指标、存储指标、WAL日志指标、对象统计指标。资源指标反映主机硬件资源负载，包含CPU使用率、内存使用率、swap交换、磁盘读写吞吐量、IO等待、网络带宽；会话指标反映数据库连接状态，当前活跃连接、空闲连接、长事务会话；SQL指标记录每条SQL调用次数、总耗时、扫描行数，用于定位消耗资源最高的SQL；锁与等待指标记录会话等待事件，识别阻塞；存储指标包含表大小、索引大小、表膨胀、元组垃圾量；WAL指标记录WAL生成速率、归档延迟、检查点触发频率。

风哥教程113257174

性能诊断通用方法论，风哥总结为：先看整体资源，再看数据库会话，接着查看等待事件，然后定位问题SQL，最后分析对象与参数。第一步确认操作系统资源是否存在瓶颈，CPU、内存、磁盘IO、网络；第二步查看数据库当前所有会话，找出长时间运行会话；第三步查看等待事件，判断会话在等待什么资源；第四步抓取TOP消耗SQL，分析执行计划；第五步检查表、索引膨胀，垃圾元组；第六步核对数据库配置参数是否合理。很多运维人员诊断故障顺序颠倒，直接查询SQL，忽略底层操作系统资源瓶颈，导致诊断方向错误。

PostgreSQL统计信息分为两类，实时内存统计与持久化统计。实时统计存储在共享内存，实时更新，数据库重启后重置，代表视图为pg_stat_activity、pg_locks；持久化统计存储在系统目录，数据库关闭不会丢失，例如pg_stat_user_tables，记录表扫描、插入、更新、删除总行数。统计信息采集功能由track_*系列参数控制，默认部分采集项关闭，生产环境需要合理开启，开启过多统计项会带来少量性能开销，需要平衡监控需求与数据库性能损耗。

生产环境指标分为告警指标与预警指标。告警指标代表已经发生故障，需要立刻处理，例如数据库连接数达到上限、实例无法连接、磁盘使用率超过95%、WAL归档失败；预警指标代表风险正在累积，需要安排窗口处理，例如表膨胀持续上涨、缓存命中率缓慢下降、WAL生成速率持续增加、存在持续长事务。

二、系统资源层面监控与诊断

本套风哥教程采用两台主机，fgedu-net-cn1为主库节点，fgedu-net-cn2为备库节点，主机规格统一为64G内存，8CPU。操作系统层面监控是所有数据库诊断的基础，数据库所有操作都依赖操作系统CPU、内存、磁盘、网络资源，很多数据库卡顿根源不在数据库本身，而是操作系统资源耗尽。

2.1 CPU监控原理

CPU指标重点关注用户态us、内核态sy、空闲id、IO等待wa。us代表业务进程消耗CPU，一般是大量SQL计算、排序、聚合；sy代表内核开销，频繁系统调用、磁盘读写、内存页交换；wa代表CPU等待磁盘IO，CPU本身空闲，但是磁盘读写慢，进程阻塞等待IO。当wa持续超过20%，代表磁盘IO瓶颈。8CPU主机，总CPU核心为8，单核心100%，CPU整体持续高于80%就需要排查。

常用操作系统工具：top、htop、vmstat、mpstat。top可以查看进程CPU、内存占用；vmstat周期性输出系统状态；mpstat可以查看每个CPU核心负载，判断是否存在单核心跑满而其他核心空闲的不均衡场景。

2.2 内存监控原理

主机总内存64G，内存分为物理内存与swap交换分区。swap代表磁盘上交换空间，当物理内存不足，操作系统会把内存页面置换到磁盘swap，swap读写速度远低于物理内存，一旦swap频繁读写，数据库性能会急剧下降，生产环境要尽量避免swap使用。

内存指标重点关注：空闲内存、buffer/cache内存、swap si/so。si表示从swap读入内存，so表示内存写入swap，只要si/so持续不为0，就属于内存压力。PostgreSQL共享缓冲区、work_mem、WAL buffer都使用物理内存，内存参数配置总和不能超过主机可用内存，否则触发swap。

2.3 磁盘IO监控原理

磁盘IO指标：读吞吐量、写吞吐量、读IOPS、写IOPS、%util、平均等待avawait。%util接近100%代表磁盘设备饱和，IO请求排队；await代表IO请求平均等待时间，机械盘await超过10ms，SSD盘await超过2ms就属于IO压力。iostat是核心IO监控工具，区分设备读写，定位哪块磁盘压力高。

2.4 网络监控原理

数据库主备复制依赖网络，网络指标关注带宽、丢包、延迟。主库fgedu-net-cn1向备库fgedu-net-cn2同步WAL日志，网络延迟过高会导致复制延迟不断上涨，备库数据落后主库。使用sar、ping、tc查看网络状态。

2.5 操作系统内核参数配置实战

登录主机fgedu-net-cn1，编辑sysctl配置文件：

vi /etc/sysctl.conf

针对64G内存8CPU主机，内核参数设置：

# 共享内存最大值，单位page，一页4k，设置足够大支撑PostgreSQL共享内存
kernel.shmmax = 34359738368
kernel.shmall = 8388608
# 内存overcommit策略，PostgreSQL推荐设置为2
vm.overcommit_memory = 2
vm.overcommit_ratio = 90
# 减少swap使用倾向，值越低越尽量不使用swap
vm.swappiness = 10
# 脏页参数，控制刷盘行为
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
# 文件句柄
fs.file-max = 1048576
# 网络参数
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_reuse = 1
net.core.somaxconn = 4096

保存文件，生效内核参数：

sysctl -p

修改进程最大打开文件数，编辑limits.conf：

vi /etc/security/limits.conf

增加fgedudb用户配置：

fgedudb soft nofile 65535
fgedudb hard nofile 65535
fgedudb soft nproc 65535
fgedudb hard nproc 65535

退出用户重新登录，验证参数：

su - fgedudb
ulimit -n
ulimit -u

2.6 操作系统监控命令实战

1. 实时查看整机资源top

top

2. 每2秒输出一次系统状态，持续采集vmstat

vmstat 2

3. 查看每个CPU核心负载mpstat

mpstat -P ALL 2

4. 磁盘IO监控iostat，每2秒输出

iostat -x 2

5. 查看swap状态

free -h

6. 查看网络状态sar

sar -n DEV 2

风哥数据库教程 itpux-com

三、PostgreSQL内置统计视图详解

PostgreSQL内置大量pg_stat统计视图，不需要额外安装组件，开启对应参数即可采集数据，是数据库运维诊断最核心工具集。登录fgedu-net-cn1主机，切换fgedudb用户，连接fgedudb数据库：

su - fgedudb
psql -d fgedudb -U fgedu -p 5432

3.1 pg_stat_activity视图

pg_stat_activity视图记录当前所有数据库后台会话，每一行代表一个backend进程，字段含义：
pid：后台进程ID，对应操作系统进程号，可以使用kill终止会话；
datname：数据库名称；
usename：连接用户名；
state：会话状态，active活跃执行SQL，idle空闲，idle in transaction事务中空闲；
wait_event_type、wait_event：等待事件类型与等待事件名称；
query：当前执行SQL语句；
backend_start：会话创建时间；
state_change：状态变更时间；
xact_start：事务开始时间，判断长事务核心字段。

查询所有会话基础信息：

SELECT pid,datname,usename,state,wait_event_type,wait_event,backend_start,xact_start,query
FROM pg_stat_activity;

查询活跃会话：

SELECT pid,now()-xact_start AS tx_duration,query
FROM pg_stat_activity
WHERE state='active';

查询长事务，事务运行超过30秒会话：

SELECT pid,now()-xact_start AS tx_duration,query
FROM pg_stat_activity
WHERE state IN ('active','idle in transaction') AND now()-xact_start > '30 seconds'::interval;

3.2 pg_locks视图

pg_locks视图记录数据库当前所有锁信息，包含表锁、行锁、元组锁、事务锁。锁分为共享锁、排他锁，排他锁会阻塞其他会话读写。pg_locks与pg_stat_activity关联查询，可以查看持有锁会话与等待锁会话。

查询当前所有锁信息：

SELECT locktype,relation::regclass,pid,mode,granted
FROM pg_locks;

关联会话，查询锁等待会话：

SELECT l.pid, a.usename,a.state,l.mode,relation::regclass,l.granted,a.query
FROM pg_locks l LEFT JOIN pg_stat_activity a ON l.pid=a.pid
WHERE l.granted='false';

3.3 pg_stat_user_tables 表统计视图

记录用户表扫描、插入、更新、删除、元组信息，n_live_tup存活元组，n_dead_tup死亡垃圾元组，n_seq_scan全表扫描次数，n_idx_scan索引扫描次数，用于检查表膨胀，识别大量全表扫描。

查询表垃圾元组与扫描统计：

SELECT relname,n_live_tup,n_dead_tup,n_seq_scan,n_idx_scan
FROM pg_stat_user_tables;

网上搜索风哥教程可以学习全套数据库教程

3.4 pg_stat_statements扩展

pg_stat_statements是官方扩展，用来统计所有SQL执行开销，统计每条SQL总执行时间、调用次数、读取块数量，定位TOP慢SQL。这个扩展需要在postgresql.conf配置shared_preload_libraries加载，修改配置文件，路径/fgedudb/data/postgresql.conf。

编辑postgresql.conf：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

重启数据库实例生效，登录数据库创建扩展：

CREATE EXTENSION pg_stat_statements;

查询消耗总时间最高TOP10 SQL：

SELECT queryid,query,total_time,calls,mean_time,rows
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

查询IO开销最高SQL，查看块读取：

SELECT query,shared_blks_read,shared_blks_hit
FROM pg_stat_statements
ORDER BY shared_blks_read DESC LIMIT 10;

重置pg_stat_statements统计计数器：

SELECT pg_stat_statements_reset();

注意：重置只会清空内存内统计，不会影响业务。生产环境不要频繁重置，会丢失历史SQL性能数据。

3.5 等待事件wait_event

等待事件是性能诊断关键，代表会话当前阻塞在等待什么资源。等待事件分为IO等待、锁等待、LwLock轻量锁等待、客户端等待。
IO类等待：DataFileRead，读取数据文件块；
锁等待：Lock，等待表锁或者行锁；
LwLock：BufferContent，缓冲区轻量锁，大量并发访问同一个内存块会出现；
ClientRead：等待客户端应用发送数据，一般是应用侧慢，不是数据库问题。

四、数据库连接、事务、锁与等待事件诊断

4.1 数据库连接管理

连接上限参数max_connections，64G内存8CPU主机，max_connections建议设置200~300。每一个连接会消耗后台进程内存，连接数量过大，会导致操作系统进程资源耗尽，上下文切换飙升，CPU压力上涨。

查看当前连接总数：

SELECT count(*) FROM pg_stat_activity;

查看不同状态连接数量统计：

SELECT state,count(*) FROM pg_stat_activity GROUP BY state;

终止异常会话，pid从pg_stat_activity获取：

SELECT pg_terminate_backend(12345);

pg_terminate_backend会直接断开会话，回滚未提交事务；pg_cancel_backend只会取消当前正在执行SQL，会话保持连接状态，适合只终止SQL，保留连接场景。

SELECT pg_cancel_backend(12345);

实战注意：终止会话前，优先确认会话业务属性，禁止随意终止核心业务会话，避免业务异常。优先终止长时间空闲事务会话。

4.2 长事务原理与危害

长事务指长时间没有提交或者回滚的事务。PostgreSQL MVCC机制，旧版本元组垃圾不能被VACUUM清理，只要存在运行中事务，垃圾元组会持续保留，长期积累会造成表严重膨胀，占用大量磁盘空间，查询扫描大量无效元组，SQL性能持续下降。长事务同时会阻塞WAL文件回收，WAL目录持续膨胀，磁盘打满。

查询长事务SQL：

SELECT pid,now()-xact_start AS tx_duration,usename,datname,query
FROM pg_stat_activity
WHERE now()-xact_start > '1 minute'::interval
ORDER BY tx_duration DESC;

定位长事务之后，优先通知业务应用确认，确认事务无用，执行pg_terminate_backend终止会话。

4.3 锁机制与锁阻塞链分析

PostgreSQL锁粒度分为表级锁与行级锁。表锁ACCESS EXCLUSIVE排他锁，一旦持有，阻塞所有读写操作，ALTER TABLE、DROP TABLE、TRUNCATE会申请这个锁，是生产事故高发锁类型。

锁阻塞链，即会话A持有锁，会话B等待A的锁，会话C等待B的锁，形成链式阻塞。单条锁查询只能看到直接等待会话，需要手动构建阻塞链。

查询锁阻塞关系：

SELECT
  pid,
  usename,
  now()-xact_start AS tx_time,
  wait_event_type,
  wait_event,
  pg_blocking_pids(pid) AS blocking_pids,
  query
FROM pg_stat_activity
WHERE pg_blocking_pids(pid) <> '{}';

pg_blocking_pids返回阻塞当前会话的pid列表，找到源头持有锁会话，终止源头会话，所有等待锁会话就会解除阻塞。

4.4 死锁诊断

死锁是两个会话互相持有对方需要的锁，数据库检测到死锁，自动终止其中一个事务，抛出死锁错误。数据库日志会记录死锁详情，开启日志参数后，死锁信息写入日志文件，路径/fgedudb/log/。查看日志定位死锁SQL。

五、SQL性能诊断与慢查询分析

5.1 慢查询日志参数配置

修改postgresql.conf，配置慢查询日志，64G 8CPU主机参数：

log_min_duration_statement = 1000
log_destination = 'csvlog'
logging_collector = on
log_directory = '/fgedudb/log'
log_filename = 'postgresql-%Y%m%d_%H%M%S.log'
log_rotation_size = 100MB
log_rotation_age = 1d

log_min_duration_statement单位毫秒，设置1000，代表执行时间超过1秒SQL会记录日志。配置完成重载配置，不需要重启数据库：

SELECT pg_reload_conf();

上51CTO搜索风哥可以学习全套数据库教程

5.2 执行计划explain与explain analyze

explain只生成预估执行计划，不执行SQL；explain analyze实际执行SQL，返回真实执行统计，会真实跑SQL，生产业务高峰期谨慎执行，避免加重负载。

基础查看执行计划：

EXPLAIN SELECT * FROM t_fgedu WHERE id=100;

真实执行，查看实际耗时、扫描行数：

EXPLAIN ANALYZE SELECT * FROM t_fgedu WHERE id=100;

执行计划关键字识别：Seq Scan全表扫描，Index Scan索引扫描，Index Only Scan仅索引扫描。看到Seq Scan就要评估是否缺少索引，或者索引失效。

5.3 索引优化实战

创建B树索引：

CREATE INDEX idx_t_fgedu_id ON t_fgedu(id);

查看索引大小，索引使用情况：

SELECT relname,pg_size_pretty(pg_relation_size(relid)) FROM pg_stat_user_indexes;

未使用索引，确认过滤条件、统计信息是否准确，执行ANALYZE更新表统计信息：

ANALYZE t_fgedu;

5.4 TOP慢SQL排查完整流程

1. 通过pg_stat_statements抓取总耗时TOP SQL；
2. 复制SQL，执行explain analyze查看执行计划；
3. 判断是全表扫描、索引失效、JOIN笛卡尔积、排序内存不足；
4. 增加合适索引，改写SQL；
5. 优化完成，观察pg_stat_statements指标，确认SQL耗时下降。

六、缓存与内存诊断分析

PostgreSQL内存分为共享内存与进程私有内存。共享内存最核心是shared_buffers共享缓冲区，用来缓存表、索引数据块；私有内存work_mem用于排序、哈希连接、分组操作；maintenance_work_mem用于索引创建、VACUUM操作。主机64G内存，推荐参数配置：

shared_buffers = 16GB
work_mem = 32MB
maintenance_work_mem = 2GB
effective_cache_size = 48GB

shared_buffers一般设置主机物理内存25%，effective_cache_size告诉优化器操作系统page cache可用内存，设置为主机内存75%。work_mem是每个操作单独分配，不是全局总内存，并发大量排序场景，work_mem设置过大，多个并发会话会瞬间耗尽内存，触发swap。

计算共享缓冲区命中率，是缓存诊断核心指标：

SELECT
  sum(shared_blks_hit) AS hit,
  sum(shared_blks_read) AS read,
  round(100.0 * sum(shared_blks_hit)/(sum(shared_blks_hit)+sum(shared_blks_read)),2) AS hit_ratio
FROM pg_stat_user_tables;

缓存命中率低于99%，代表大量数据块从磁盘读取，存在IO压力，需要评估热数据是否超出内存容量，或者缺少索引导致大量扫描。

查看当前work_mem使用场景，排查大排序SQL：

SELECT pid,query,now()-backend_start AS dur
FROM pg_stat_activity
WHERE state='active' AND query ILIKE '%sort%';

七、WAL日志诊断与IO性能分析

WAL预写日志机制，PostgreSQL修改数据不会直接写数据文件，先写入WAL日志，再后台刷数据文件，保障崩溃一致性。WAL参数配置（64G/8CPU）：

wal_buffers = 16MB
max_wal_size = 4GB
min_wal_size = 1GB
wal_level = replica
archive_mode = on
archive_command = 'cp %p /fgedudb/wal_archive/%f'

max_wal_size是WAL文件循环复用上限，到达阈值触发检查点，检查点会大量刷脏页到磁盘，瞬间拉高IO压力。

查看WAL生成速率，统计WAL文件产生数量：

SELECT count(*) FROM pg_ls_waldir();

查看WAL归档状态，检查归档失败记录，查看日志/fgedudb/log。

查看检查点信息：

SELECT * FROM pg_stat_checkpointer;

checkpoint_write_time代表检查点刷写耗时，如果持续很高，说明检查点刷盘压力大，可以调大max_wal_size，拉长检查点间隔，分散IO。

主备复制延迟诊断，fgedu-net-cn1主库，fgedu-net-cn2备库：

SELECT pid,usename,application_name,state,write_lag,flush_lag,replay_lag
FROM pg_stat_replication;

replay_lag代表备库回放延迟，延迟过高，排查网络带宽、备库IO压力、备库资源。

八、数据库日常巡检实战

常态化巡检目的是定期收集数据库健康指标，提前发现隐患，风哥整理巡检项目清单，包含操作系统巡检、数据库基础状态、连接、长事务、锁、表膨胀、缓存命中率、WAL归档、复制状态、日志错误。

编写巡检脚本，部署在fgedu-net-cn1，输出巡检文本报告，脚本基础框架：

#!/bin/bash
# pg巡检脚本，用户fgedudb，数据库fgedudb
LOG_FILE=/fgedudb/script/pg_check.log
echo "======主机资源信息======" >> $LOG_FILE
free -h >> $LOG_FILE
iostat -x 1 1 >> $LOG_FILE
echo "======数据库基础信息======" >> $LOG_FILE
su - fgedudb -c "psql -d fgedudb -U fgedu -c 'SELECT version();'" >> $LOG_FILE
su - fgedudb -c "psql -d fgedudb -U fgedu -c 'SELECT count(*) FROM pg_stat_activity;'" >> $LOG_FILE
su - fgedudb -c "psql -d fgedudb -U fgedu -c 'SELECT pid,now()-xact_start AS tx_dur,query FROM pg_stat_activity WHERE now()-xact_start > ''30s''::interval;'" >> $LOG_FILE

定时任务crontab配置，每天凌晨2点自动执行巡检：

crontab -e
0 2 * * * /fgedudb/script/pg_check.sh

巡检结果重点关注：磁盘使用率、swap使用、长事务、表膨胀率、WAL归档状态、缓存命中率，任何指标异常记录到运维台账，评估处理窗口。

表膨胀计算实战，查询表大小、死亡元组估算膨胀比例：

SELECT
  relname,
  pg_size_pretty(pg_relation_size(relid)) AS table_size,
  n_live_tup,
  n_dead_tup,
  round(100.0*n_dead_tup/(n_live_tup+n_dead_tup),2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_live_tup+n_dead_tup>0;

死亡元组比例超过20%，需要执行VACUUM清理垃圾元组：

VACUUM ANALYZE t_fgedu;

VACUUM ANALYZE清理垃圾元组，同时更新表统计信息，VACUUM FULL会重建表，锁表，业务高峰期禁止执行VACUUM FULL。

九、典型故障实战诊断案例

案例一：数据库连接数爆满，业务无法接入

现象：应用报错无法连接数据库，登录psql连接超时。
排查步骤：

1. 主机fgedu-net-cn1执行ps aux | grep postgres，查看postgres进程数量；
2. 登录psql（预留超级用户连接）执行SELECT count(*),state FROM pg_stat_activity GROUP BY state;，发现大量idle in transaction会话；
3. 查询长事务，发现应用连接未及时关闭事务，大量长事务会话占用连接；
4. 业务侧修复代码，增加事务提交逻辑；批量终止无用长事务会话，连接释放；
5. 增加监控告警，连接数达到max_connections80%触发告警。

案例二：锁阻塞引发业务大面积卡顿

现象：业务提交请求长时间卡住，少量查询可以执行。
排查步骤：

1. 操作系统查看CPU不高，IO正常；
2. 查询pg_stat_activity，大量会话等待Lock；
3. 使用pg_blocking_pids定位源头会话，一条ALTER TABLE语句持有ACCESS EXCLUSIVE锁；
4. 确认业务，该DDL为运维人员手动执行，业务高峰期执行；
5. 终止DDL会话，所有等待锁会话恢复；
6. 规范运维流程，DDL操作必须业务低峰执行，提前评估锁影响。

案例三：慢SQL引发CPU持续高负载

现象：主机CPU持续90%以上，数据库响应缓慢。
排查步骤：

1. top查看postgres进程占用大量CPU；
2. pg_stat_statements查询TOP SQL，一条无索引全表查询频繁调用；
3. explain验证执行计划Seq Scan全表扫描；
4. 创建合适索引；
5. 观察CPU逐步下降，SQL执行耗时大幅降低。

案例四：表膨胀，磁盘持续上涨，查询性能下降

现象：磁盘空间每天持续上涨，业务查询越来越慢，没有大量新增数据。
排查步骤：

1. 查询pg_stat_user_tables，n_dead_tup垃圾元组巨大；
2. 排查发现长期存在长事务，VACUUM无法清理旧垃圾；
3. 终止长事务；执行VACUUM ANALYZE；
4. 垃圾元组清理完成，磁盘不再持续增长。

十、监控方案搭建实战

PostgreSQL监控体系分为采集层、存储层、告警层。采集层负责抓取操作系统指标与数据库内核指标；存储层保存时序指标；告警层设置阈值，指标异常推送告警。

基础监控指标清单：
操作系统指标：CPU使用率、内存使用率、swap、磁盘使用率、磁盘IOPS、磁盘读写吞吐量；
数据库指标：活跃连接数、总连接数、长事务数量、锁等待会话数、缓存命中率、WAL生成量、复制延迟、慢SQL数量、表膨胀率。

在fgedu-net-cn1部署采集客户端，定时调用SQL查询pg_stat视图，将指标推送到时序存储。配置告警阈值示例：

• 主机磁盘使用率>90%，告警；
• 数据库连接数>max_connections*0.8，告警；
• 复制延迟>5s，告警；
• 存在超过5分钟长事务，告警；
• 缓存命中率<98%，预警。

监控搭建完成之后，持续观察指标趋势，不能只看瞬时值。瞬时指标波动可能是正常业务峰值，持续趋势异常才代表故障风险。监控告警需要做降噪，避免大量误告警，区分业务高峰期正常波动与真实故障。

风哥针对本文总结

PostgreSQL运维诊断与监控分析不是零散命令堆砌，而是一套完整的分析思路，从操作系统底层资源，到数据库进程、会话、等待事件、SQL执行、内存WAL、锁机制，逐层定位瓶颈。64G内存8CPU主机的参数配置需要结合业务读写模型调整，不存在一套WL参数。生产环境优先常态化巡检，依靠监控提前发现隐患，而不是故障出现之后紧急救火。本套风哥教程重点强化实战操作，所有命令、SQL都可以在fgedu-net-cn1、fgedu-net-cn2双节点环境直接落地测试。运维人员要熟练掌握pg_stat系列视图、等待事件分析、慢查询日志、锁阻塞排查，理解MVCC、WAL、VACUUM底层原理，原理和实战结合，才能快速定位各类数据库性能故障。日常运维必须建立规范，限制业务高峰期DDL操作，杜绝长事务，及时优化低效SQL，定期清理表垃圾元组，保障PostgreSQL数据库长期稳定运行。