接手一套 PostgreSQL 之后不管是你刚从前辈手里接过来的老库还是自己从零搭起来的新实例第一节必修课永远是同一个先把手头最常用的一批 SQL 背下来。不是那些教材里讲创建表、建索引的语法而是每天巡检、排障、应对业务方提问时真正高频使用的“查命”语句。我这些年换过几个团队每个团队的巡检脚本都不同但核心查询翻来覆去就那么二三十条。这篇文章我把压箱底的那批 PostgreSQL 运维 SQL 整理出来每条都附上我实际执行过的场景和踩过的坑。适合有一定 PostgreSQL 基础、正在做 DBA 或打算转数据库运维方向的人看完可以直接抄进自己的巡检脚本里。1. 先摸家底连接与实例状态巡检1.1 一眼看清当前所有连接在干什么DBA 每天打开电脑的第一件事大概率就是看连接。不是看总数而是看每个会话到底在做什么。pg_stat_activity这个视图就是干这个的我实际使用的查询长这样SELECT datname, usename, application_name, client_addr, state, backend_xid, backend_xmin, query_start, xact_start, query FROM pg_stat_activity WHERE pid pg_backend_pid() ORDER BY query_start DESC;这个查询会把你自己的会话过滤掉把当前实例上所有业务连接按启动时间排出来。很多人习惯直接SELECT * FROM pg_stat_activity但我建议只挑这几个字段因为query字段在不同会话里可能很长全字段输出会把终端刷得很难看。实际观察时我习惯先按state分组看一个概览SELECT state, count(*) FROM pg_stat_activity GROUP BY state;正常情况下输出应该以active和idle为主。如果出现大量idle in transaction说明有会话开启事务后没提交也没回滚这会卡住所有依赖该事务快照的清理工作还会导致表膨胀。这种会话数量突增通常意味着应用代码里有事务没关闭的 bug需要立刻顺藤摸瓜。连接数本身也是巡检重点。看当前连接数和上限比例SELECT count(*) AS total_conn, (SELECT setting::int FROM pg_settings WHERE name max_connections) AS max_conn, round(count(*) * 100.0 / (SELECT setting::int FROM pg_settings WHERE name max_connections), 2) AS usage_pct FROM pg_stat_activity;当使用率超过 80% 就要开始警惕了超过 90% 建议立刻查连接来源。排查来源的 SQLSELECT client_addr, application_name, count(*) FROM pg_stat_activity WHERE pid pg_backend_pid() GROUP BY client_addr, application_name ORDER BY count(*) DESC;1.2 角色权限与连接限制核查连接类问题里还有一类很容易忽视角色本身的连接限制。PostgreSQL 允许对单个角色设置CONNECTION LIMIT这在共享账号场景下经常被误配。有一次业务方反馈“数据库连不上”我查了半天才发现在某个实例上业务账号的rolconnlimit恰好等于当时已有的连接数。查看角色权限与连接限制SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolreplication, rolconnlimit, rolvaliduntil FROM pg_roles WHERE rolsuper OR rolreplication ORDER BY rolname;我通常还会顺手看角色的创建时间和最近密码修改时间虽然pg_roles不直接提供这些信息但要养成定期审查超级用户和复制账号的习惯。对于rolconnlimit -1的角色属于默认无限制出现具体数字时一定要和业务方确认是有意配置还是历史遗留。注意pg_roles不会列出所有角色的密码密文也不应该去查。任何试图读取密码散列的行为都应该被审计DBA 只需关注过期时间rolvaliduntil。另外如果你管理的是 PostgreSQL 12 以上的版本建议定期查pg_hba_file_rules或直接审阅pg_hba.conf。虽然这不算 SQL 层面的操作但连接问题和认证问题往往并存我见过太多团队只查pg_stat_activity忽略了pg_hba.conf里那一堆堆叠的规则。2. 空间到底去哪了存储类查询三板斧2.1 库、表、索引体积排行几乎每隔两周就会有人来问“磁盘怎么又快满了”。这种场景下你的第一反应不应该是去du看数据目录而应该直接在库里查空间占用因为业务数据文件的分布并不均匀查库才是最准的。先看整个实例下各数据库的大小SELECT datname, pg_size_pretty(pg_database_size(datname)) AS db_size FROM pg_database ORDER BY pg_database_size(datname) DESC;再看某个库里最大的那些表。注意这里要用pg_total_relation_size因为它包含表本体、所有索引、TOAST 表以及其索引的完整占用。很多新手用pg_relation_size只看到表本身的大小容易漏掉索引膨胀的问题SELECT schemaname, relname, n_live_tup, pg_size_pretty(pg_total_relation_size(relid)) AS total_size, pg_size_pretty(pg_relation_size(relid)) AS table_size, pg_size_pretty(pg_indexes_size(relid)) AS index_size FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 30;这条查询基本就是空间排查的“核武器”。执行完基本上能一眼定位是谁吃掉了磁盘。如果想把某个表分解得更细可以用pg_relation_size的变体SELECT pg_size_pretty(pg_relation_size(public.some_table)), pg_size_pretty(pg_total_relation_size(public.some_table));2.2 膨胀、死元组与 VACUUM 状态表大小排完还没完。磁盘上的空间大不代表都在被有效利用很多时候是表膨胀。PostgreSQL 的 MVCC 机制下旧版本数据会留下死元组dead tuples它们占着空间等待 VACUUM 回收。如果 VACUUM 没跟上表就会一路膨胀下去。检查膨胀情况的经典查询SELECT schemaname, relname, n_live_tup, n_dead_tup, CASE WHEN n_live_tup 0 THEN round(n_dead_tup * 100.0 / n_live_tup, 2) ELSE 0 END AS dead_pct, last_vacuum, last_autovacuum, last_analyze FROM pg_stat_user_tables WHERE n_dead_tup 100 ORDER BY n_dead_tup DESC LIMIT 20;死元组数量本身不是绝对参考要看它占活元组的比例。当dead_pct超过 20% 时就需要手动VACUUM一下超过 50% 甚至要考虑VACUUM FULL不过VACUUM FULL会锁表只能在维护窗口执行这一点我在后面“常见问题”里还会展开。另外一个重要信号是last_autovacuum为空。如果一张频繁更新的表从未被自动清理过并且last_vacuum也是空说明 autovacuum 可能被全局关闭或者这张表被设置了autovacuum_enabled false。这种表通常会在某个不眠之夜突然暴涨提前发现就能避开事故。2.3 索引体积与无效索引识别表的大小里索引体量常常被低估。我见过一张只有 100GB 的表索引却有 80GB原因就是开发同学走了索引的“多多益善”路线甚至同一组列建了好几个索引。查看索引占用的 SQLSELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC LIMIT 30;idx_scan是索引被扫描的次数。如果某个索引体积很大但idx_scan几乎为 0它很可能就是多余的。不过要注意刚建立不久或者在特定时期才使用的索引可能扫描次数暂时不高不能仅凭这个字段就动手删索引需要结合查询日志和业务特征判断。识别重复索引是另一个进阶操作。用一个自查询把相同列组合的索引找出来SELECT pg_size_pretty(sum(pg_relation_size(indexrelid))) AS total_idx_size, tablename, indexdef FROM pg_indexes GROUP BY tablename, indexdef HAVING count(*) 1 ORDER BY sum(pg_relation_size(indexrelid)) DESC;这个查询会列出完全重复的索引定义。对于不完全重复、但功能重叠的索引比如(a,b)和(a)这类识别起来更麻烦我在第 4 部分会给出更完整的排查思路。3. 卡顿与阻塞锁、长事务和慢查询定位3.1 阻塞链与锁等待排查业务方说“系统卡死了”你先不要慌第一时间查阻塞。最常用的阻塞查询是把pg_stat_activity自连接通过pg_blocking_pids函数找出阻塞源SELECT blocked.pid AS blocked_pid, blocked.datname, blocked.usename, blocked.application_name, blocked.query AS blocked_query, blocker.pid AS blocker_pid, blocker.query AS blocker_query, now() - blocked.xact_start AS blocked_xact_age, now() - blocker.xact_start AS blocker_xact_age FROM pg_stat_activity blocked LEFT JOIN pg_stat_activity blocker ON blocker.pid ANY(pg_blocking_pids(blocked.pid)) WHERE blocked.state idle AND blocked.wait_event_type IN (Lock, LWLock);这个查询的结果如果blocker_pid不为空就说明阻塞链路存在。不仅要看最直接的阻塞者还要逐层往上追因为可能存在 A 阻塞 B、B 又阻塞 C 的多层级链路。PostgreSQL 14 之后pg_blocking_pids返回所有直接阻塞该 pid 的会话所以 JOIN 出来 Union 后要把链路串起来看。再配合pg_locks查看锁模式更直观SELECT locktype, relation::regclass AS relation, mode, granted, pid, virtualtransaction FROM pg_locks WHERE NOT granted ORDER BY pid;granted false的锁说明会话正在等待此锁。通常结合pg_stat_activity里的wait_event_type一起看如果wait_event显示为transactionid说明在等事务锁如果显示relation则大概率在等表锁。3.2 长事务与空闲会话处理长事务和空闲会话是 PostgreSQL 两种很容易被忽视的“隐形杀手”。长事务会阻止VACUUM清理死元组进而导致表膨胀严重时会撑爆pg_xact事务提交日志整库进入只读保护模式。遇到这种情况就危险了。先找长事务SELECT pid, datname, usename, application_name, state, now() - xact_start AS xact_age, now() - query_start AS query_age, query FROM pg_stat_activity WHERE state active AND xact_start IS NOT NULL AND now() - xact_start interval 10 minutes ORDER BY xact_start;我这里的10 minutes只是示例值实际阈值要根据业务设定。OLTP 系统里事务超过 10 分钟基本就该报警了分析型系统可以放宽到 30 分钟以上。空闲会话同样值得关注。特别是idle in transaction状态它比普通的idle更危险因为事务没结束持有的锁和快照不会释放。定位这类会话SELECT pid, datname, usename, application_name, client_addr, now() - xact_start AS idle_in_xact_age FROM pg_stat_activity WHERE state idle in transaction ORDER BY xact_start;处理方式通常有两种一是联系业务方让应用自己提交或回滚二是通过pg_terminate_backend(pid)强制终止。我有一次凌晨处理过一个空闲事务应用端连接池里的连接被人为开启事务但一直没关闭积累了几百个idle in transaction会话最终不得不写了个循环去逐个 terminate。所以事后一定要让研发排查框架配置治标不治本是 DBA 的大忌。3.3 慢SQL定位与 pg_stat_statements慢查询谁都会看但“看得到”和“定位得准”之间差了pg_stat_statements这个扩展。没有这个扩展你只能从日志里翻效率极低。安装扩展CREATE EXTENSION IF NOT EXISTS pg_stat_statements;注意pg_stat_statements必须通过shared_preload_libraries预加载才能生效只CREATE EXTENSION是不够的。修改postgresql.conf之后需要重启实例shared_preload_libraries pg_stat_statements重启之后用以下 SQL 找最耗时的查询SELECT queryid, calls, total_exec_time, mean_exec_time, rows AS total_rows, round(total_exec_time / calls, 2) AS avg_ms_per_call FROM pg_stat_statements WHERE calls 100 ORDER BY total_exec_time DESC LIMIT 20;在 PostgreSQL 12 及以下版本中时间字段叫total_time不是total_exec_time升级到 13 以上才统一成后者。不同版本的兼容问题我遇到过不止一次写巡检脚本时最好判断一下版本。mean_exec_time和total_exec_time要结合看。有的查询调用次数少但单次极慢有的查询调用次数极多但单次还好。重点是找到“总耗时”最高的也就是对系统整体影响最大的。如果想进一步获取查询的完整文本可以用queryid关联SELECT queryid, query FROM pg_stat_statements WHERE queryid 1923765532;query字段默认会截断到一定长度想要完整语句可以调pg_stat_statements.max参数但该参数同样需要重启才生效。4. 复制与高可用主从健康和 WAL 检查4.1 流复制状态与延迟计算现在企业里很少跑单机 PostgreSQL 了主从复制是标配。流复制状态检查的工具就一个视图pg_stat_replication。在主库上执行SELECT usename, application_name, client_addr, state, sync_state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication;我需要提醒大家pg_stat_replication只有主库上有内容从库上查不到。state字段正常应该是streaming如果出现catchup或backup说明从库正在追赶或做基础备份需要关注网络和磁盘 IO。延迟计算有两种方式。在 PG10 版本中可以直接看replay_lag字段这是 PostgreSQL 自动计算的主备延迟单位是时间间隔。如果字段为空或者你想自己算 LSN 距离可以这样SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_lag_bytes FROM pg_stat_replication;pg_current_wal_lsn()表示主库当前 WAL 写入位置replay_lsn表示备库已回放的位置两者差值越大说明备库落后越多。这个差值用字节表示能帮你判断备库的恢复能力是否跟得上主库的写入速率。4.2 WAL 目录与归档检查WAL 文件增长失控是另一个容易翻车的地方。如果你的实例启用了archive_mode但归档命令执行得很慢或者失败WAL 文件会在pg_wal目录里疯狂堆积最终把磁盘写满。看 WAL 目录大小SELECT pg_size_pretty(sum(size)) AS wal_total_size FROM pg_ls_waldir();pg_ls_waldir()在 PostgreSQL 11 以上可用返回pg_wal目录里所有 WAL 文件和段信息。再配合查询当前 WAL 位置和最近一次检查点SELECT pg_current_wal_lsn(), pg_current_wal_insert_lsn(), pg_last_commit_ts();不过pg_last_commit_ts依赖track_commit_timestamp on默认是关的这个查询大概率报错我在实际中一般不看它。更实用的是了解当前 WAL 写入速率和分段文件数量结合监控系统做趋势分析。如果需要代码层面知道归档是否正常可以检查pg_stat_archiverSELECT archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time FROM pg_stat_archiver;failed_count如果持续增长归档链路就是有问题的。常见原因有归档目录磁盘满、归档命令写错路径、备份软件卡死等。有一次我发现一个实例的last_failed_time一直不变但failed_count很大最后排查发现是备份软件只记录错误但不清理队列磁盘上积压了大量 WAL 文件。这类问题不是 SQL 能直接解决的但通过 SQL 尽早发现总比磁盘写满后再处理强得多。5. 自动化巡检把常用 SQL 装进每日值班报告5.1 巡检脚本的基本架构上面这些 SQL 单条跑都没问题但真正让它们发挥价值的方式是汇总成自动化巡检脚本。我一般每天早上跑到值班群里的报告长这样连接数、DB 大小、最大表、死元组 Top10、慢查询 Top10、主备延迟、WAL 目录大小、最近失败日志数量。一个最简的巡检脚本可以用 bash 配合 psql 实现不需要额外依赖#!/bin/bash # pg_daily_check.sh PGHOST127.0.0.1 PGPORT5432 PGUSERpostgres export PGPASSWORDyour_password PSQLpsql -h ${PGHOST} -p ${PGPORT} -U ${PGUSER} -d postgres -X -q -A -t echo 连接数 ${PSQL} -c SELECT state, count(*) FROM pg_stat_activity GROUP BY state; echo 数据库大小 ${PSQL} -c SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC; echo 最大的10张表 ${PSQL} -c SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10; echo 死元组Top10 ${PSQL} -c SELECT schemaname, relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE n_dead_tup 100 ORDER BY n_dead_tup DESC LIMIT 10; echo 慢查询Top10 ${PSQL} -c SELECT queryid, calls, round(total_exec_time::numeric, 2) AS total_ms, round(mean_exec_time::numeric, 2) AS avg_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; echo WAL目录大小 ${PSQL} -c SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir(); echo 复制状态 ${PSQL} -c SELECT client_addr, state, replay_lag FROM pg_stat_replication;几个关键细节我强调一下-X表示不读取.psqlrc防止用户配置文件影响输出格式-q安静模式-A关闭对齐-t只输出元组。这些参数组合起来输出结果才是干净、易于解析的纯文本。如果你把pg_stat_statements放进巡检脚本还要注意扩展是否已创建没创建的话脚本会直接报错。5.2 从脚本到值班日志的落地经验有了脚本还需要配 crontab。我的习惯是这样0 7 * * * /usr/local/scripts/pg_daily_check.sh /var/log/pg_daily_check.log 21每天早上 7 点跑一次。如果团队规模更小也可以把脚本放到一台堡垒机上对所有实例循环执行。千万不要把巡检脚本放在数据库服务器上作为唯一副本因为数据库节点本身可能宕机那时巡检脚本也跑不了也就失去了告警的意义。巡检脚本输出的内容不要止步于当天查看最好把关键指标追加到历史 CSV 文件里这样你需要回溯问题时可以查趋势${PSQL} -c COPY (SELECT now(), count(*) FROM pg_stat_activity WHERE stateactive) TO STDOUT WITH CSV /var/log/pg_conn_history.csv用COPY ... TO STDOUT把结果直接输出成 CSV 追加写入文件是轻量且可靠的做法。等积累一个月再用 Python 或 Excel 画个趋势图连接数暴涨的时间点和应用发版时间一对比真相往往立竿见影。告警方面不要贪多。先做以下几类告警就够用连接数使用率超过 80%任一数据库大小在 24 小时内增长超过 10%死元组占比超过 30% 的表数量大于 5主备延迟超过 5 分钟pg_stat_archiver的failed_count较昨天增加告警阈值可以在脚本末尾用 awk 判断然后调用钉钉或者企业微信机器人接口推送。注意发送到即时通讯工具时要脱敏避免把带客户信息的查询语句直接发到群里。写在最后的个人体会PostgreSQL DBA 的日常工作远不是那几条 SQL 能全覆盖的但把这批高频 SQL 内化成“肌肉记忆”之后你的工作效率会提升一大截。尤其当线上出问题时你能在几十秒内定位到连接、锁、空间、复制中的任意一环和翻文档现查现试是完全不同的体验。我自己的习惯是每遇到一次故障就在文档里追加一条对应的排查 SQL 和判断标准。时间一长这份文档比任何官方手册都顺手。如果你刚开始组建自己的 SQL 清单从这篇文章里的二十多条入手就够了后续再根据自己的业务场景慢慢扩充。尤其是复制监控和 WAL 检查这两个方面很多人一开始觉得用不上等真出了事故才发现临时补课代价太大。趁实例还健康先把这些查询跑一遍把正常状态下的输出样式存个档以后值班心里就有底了。