本篇对PostgreSQL 中的pg_stat_activity系统视图进行一个非常详细和深入的讲解并介绍其核心应用场景。一、pg_stat_activity 是什么pg_stat_activity是 PostgreSQL 的一个系统视图System View它提供了对当前数据库服务器上所有正在运行的服务器进程的一瞥。每个连接到 PostgreSQL 服务器的客户端包括后台进程都会在其中对应一条记录。你可以把它看作是数据库的“任务管理器”或“活动监视器”是进行数据库监控、性能分析和故障排查的最重要工具之一。二、视图字段详解核心列说明查询SELECT * FROM pg_stat_activity;会返回很多列。以下是其中最常用和关键的列字段名数据类型描述datidOid进程所连接的数据库的 OID。datnamename进程所连接的数据库名。这是最常用的过滤字段。pidinteger进程 ID。这是操作系统级别的进程 ID是操作如取消查询的关键。usesysidOid登录用户的 OID。usenamename登录到此后端的用户名。application_nametext应用程序名称。由客户端连接字符串中的application_name参数设置。常用于识别不同来源的连接如“psql” “pgAdmin” “my_app_server_1”。client_addrinet客户端的 IP 地址。如果通过 Unix domain socket 连接则为 NULL。用于排查网络来源的问题。client_hostnametext客户端的主机名如果通过 IP 连接且启用了log_hostname。client_portinteger客户端用于通信的 TCP 端口号。backend_starttimestamptz进程启动的时间即客户端连接建立的时间。xact_starttimestamptz当前事务开始的时间。如果未在事务中则为 NULL。query_starttimestamptz当前正在执行的查询开始的时间。state_changetimestamptz上次state改变的时间。wait_event_typetext进程正在等待的事件类型如Lock,LWLock,BufferPin。这是分析瓶颈的关键。如果进程正在运行则为 NULL。wait_eventtext等待事件的名称如等待的锁类型。与wait_event_type配合使用。statetext当前后端的状态。这是极其重要的列-active 后端正在执行一个查询。-idle 后端正在等待一个新的客户端命令。-idle in transaction 后端在一个事务中但当前没有执行查询。-idle in transaction (aborted) 后端在一个事务中但事务中的一个语句出错了。-fastpath function call 后端正在执行一个 fast-path 函数。-disabled 如果 track_activities 被在这个后端禁用。backend_xidxid后端的顶级事务 ID如果存在。backend_xminxid后端的xmin水平线用于判断哪些行版本对此后端可见。querytext该进程最近执行的查询文本。如果state是active这就是当前正在运行的查询。如果track_activities被禁用此值为 NULL。注意超级用户可以看到所有查询普通用户只能看到自己的查询。query_idbigint用于计算查询频率的哈希码需要compute_query_id on。三、核心应用场景和查询示例1. 查看所有活动连接最基本用法SELECT*FROMpg_stat_activity;2. 查看非空闲连接聚焦正在工作的进程这是最常用的查询过滤掉那些只是连着但没事干的连接。SELECTdatname,usename,client_addr,application_name,state,query,query_start,now()-query_startASdurationFROMpg_stat_activityWHEREstate!idleANDpid!pg_backend_pid()-- 排除自己当前这个查询连接ORDERBYdurationDESC;3. 查找长时间运行的查询/事务用于排查性能问题-- 查找运行超过 5 分钟的查询SELECTpid,usename,datname,now()-query_startASquery_duration,queryFROMpg_stat_activityWHEREstateactiveANDnow()-query_startinterval5 minutesORDERBYquery_durationDESC;-- 查找开启时间过长的事务即使它现在没在执行查询SELECTpid,usename,datname,now()-xact_startASxact_duration,state,queryFROMpg_stat_activityWHERExact_startISNOTNULLANDnow()-xact_startinterval10 minutesORDERBYxact_durationDESC;4. 查找等待锁的进程用于解决锁冲突SELECTpid,usename,datname,query,wait_event_type,wait_event,now()-query_startASwait_durationFROMpg_stat_activityWHEREwait_event_typeISNOTNULLANDwait_event_typeLock-- 聚焦在锁等待上ORDERBYwait_durationDESC;5. 按应用或用户统计连接数-- 按应用统计SELECTapplication_name,count(*)FROMpg_stat_activityGROUPBYapplication_name;-- 按用户统计SELECTusename,count(*)FROMpg_stat_activityGROUPBYusename;-- 按数据库统计SELECTdatname,count(*)FROMpg_stat_activityGROUPBYdatname;6. 终止问题查询或连接pg_terminate_backend当你发现一个异常查询如长时间运行、死锁时可以用获取到的pid来终止它。警告这是强制杀死操作可能会中断业务请谨慎使用。-- 取消一个查询类似于 CtrlC允许它自行回滚SELECTpg_cancel_backend(pid);-- 强制终止一个后端连接类似于 kill -9连接会立即断开事务会回滚SELECTpg_terminate_backend(pid);-- 示例终止所有连接到 my_database 的连接常用于维护前踢出所有用户SELECTpg_terminate_backend(pid)FROMpg_stat_activityWHEREdatnamemy_database;7. 查找“僵尸”事务Idle in Transaction这种状态的事务通常由应用程序bug引起如开启了事务但未提交或回滚它会持有锁、阻止VACUUM是数据库的“大敌”。SELECTpid,usename,datname,now()-xact_startASxact_duration,queryFROMpg_stat_activityWHEREstateidle in transactionORDERBYxact_durationDESC;四、重要注意事项和最佳实践权限 普通用户只能看到自己会话的信息。超级用户可以看到所有会话的信息和查询。query字段的性能pg_stat_activity的query字段是text类型可能很长。在生产环境频繁查询所有字段尤其是SELECT *可能会对性能有轻微影响。建议只选择你需要的列。pg_backend_pid() 在编写管理脚本时使用WHERE pid pg_backend_pid()可以排除掉你当前用于查询的管理连接自身避免误杀自己。监控工具的基础 几乎所有 PostgreSQL 监控工具如 pgAdmin 的仪表盘、Zabbix、Prometheus grafana 看板其底层数据都来源于pg_stat_activity和pg_stat_statements等系统视图。结合其他视图 为了更全面的分析通常将pg_stat_activity与pg_locks查看锁详情、pg_stat_statements查看历史查询统计等视图结合使用。总之pg_stat_activity是 PostgreSQL DBA 和开发者必须掌握的核心工具熟练使用它能让你快速诊断数据库的实时状态、定位性能瓶颈和解决各种连接与锁相关问题。
