pg_stat_activity_pg stat activity-优快云博客

本文链接：https://blog.youkuaiyun.com/hezuijiudexiaobai/article/details/128982275

pg_stat_activity是PostgreSQL数据库中的一个视图，用于显示每个服务器进程的当前活动信息，包括进程ID、启动时间、状态、查询等详细数据。通过查询这个视图，管理员可以了解数据库的运行状态，诊断性能问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

目录标题

视图字段说明
常用命令

视图字段说明

The pg_stat_activity view will have one row per server process, showing information related to the current activity of that process.

\d pg_stat_activity;
           View "pg_catalog.pg_stat_activity"
      Column      |           Type           | Modifiers 
------------------+--------------------------+-----------
 datid            | oid                      | 
 datname          | name                     | 
 pid              | bigint                   | 
 sessionid        | bigint                   | 
 usesysid         | oid                      | 
 usename          | name                     | 
 application_name | text                     | 
 client_addr      | inet                     | 
 client_hostname  | text                     | 
 client_port      | integer                  | 
 backend_start    | timestamp with time zone | 
 xact_start       | timestamp with time zone | 
 query_start      | timestamp with time zone | 
 state_change     | timestamp with time zone | 
 waiting          | boolean                  | 
 enqueue          | text                     | 
 state            | text                     | 
 resource_pool    | name                     | 
 query_id         | bigint                   | 
 query            | text                     | 
 connection_info  | text                     | 
 unique_sql_id    | bigint                   | 
 trace_id         | text                     |

select * from pg_stat_activity;

select datname, backend_start, connection_info from pg_stat_activity;

pg9.6
pg14
理解 pg_stat_activity

在 PostgreSQL 中，pg_stat_activity 是一个系统视图，它提供了当前数据库服务器上所有活动会话的实时快照。这个视图包含了关于每个会话的各种信息，包括它们的状态、正在执行的查询、客户端地址、连接时间等。

以下是 pg_stat_activity 视图中一些常用列的解释：

pid: 会话的进程ID，这是操作系统级别的进程标识符。
usename: 启动会话的数据库用户名称。
datname: 会话当前连接的数据库名称。
state: 会话的状态，如 active（正在执行查询）、idle（空闲）、idle in transaction（在事务中空闲）等。
query: 当前会话正在执行的 SQL 查询。如果会话处于空闲状态，则此列为空。
query_start: 查询开始的时间戳。
xact_start: 事务开始的时间戳。如果会话不在事务中，则此列为空。
backend_start: 后端进程启动的时间戳。
client_addr: 连接到数据库服务器的客户端的IP地址。
client_port: 客户端使用的端口号。
virtual transaction id: 虚拟事务ID，用于标识事务，即使在崩溃后也能保持一致。
transaction id: 当前事务的ID。

pg_stat_activity 视图对于监控数据库性能、诊断问题和理解数据库负载非常有用。例如，您可以使用它来识别长时间运行的查询、空闲会话或锁定资源的会话。

常用命令

针对您提供的查询语句，以下是优化建议：

查询每个数据库的连接数：

SELECT 
  datname AS "Database",
  COUNT(*) AS "Number of connections"
FROM 
  pg_stat_activity
WHERE 
  datid IS NOT NULL
GROUP BY 
  datname
ORDER BY 
  "Number of connections" DESC;

优化建议：

避免使用 datid IS NOT NULL：pg_stat_activity 视图中的 datid 列始终不为 NULL，因此此条件多余。
简化查询：直接查询 datname 和连接数即可。
优化后的查询：

SELECT 
  datname AS "Database",
  COUNT(*) AS "Number of connections"
FROM 
  pg_stat_activity
GROUP BY 
  datname
ORDER BY 
  "Number of connections" DESC;

查询客户端地址：

SELECT datname, usename, client_addr 
FROM pg_stat_activity 
WHERE datname != 'postgres' AND usename != 'postgres';

优化建议：

避免硬编码数据库和用户名：硬编码 'postgres' 可能导致遗漏其他系统用户。
使用 NOT IN 替代多个 !=：NOT IN 更适合用于排除多个值。
优化后的查询：

SELECT datname, usename, client_addr 
FROM pg_stat_activity 
WHERE (datname, usename) NOT IN (('postgres', 'postgres'));

查询当前非空闲状态的活动：

SELECT pid, wait_event_type, wait_event 
FROM pg_stat_activity 
WHERE datname != 'postgres' AND usename != 'postgres' AND state != 'idle';

优化建议：

避免硬编码数据库和用户名：同上，避免遗漏其他系统用户。
使用 NOT IN 替代多个 !=：同上，NOT IN 更适合用于排除多个值。
优化后的查询：

SELECT pid, wait_event_type, wait_event 
FROM pg_stat_activity 
WHERE (datname, usename) NOT IN (('postgres', 'postgres')) 
  AND state != 'idle';

查询每个数据库和用户名的连接数：

SELECT datname, usename, COUNT(*) 
FROM pg_stat_activity 
WHERE datname != 'postgres' AND usename != 'postgres' 
GROUP BY datname, usename;

优化建议：

避免硬编码数据库和用户名：同上，避免遗漏其他系统用户。
使用 NOT IN 替代多个 !=：同上，NOT IN 更适合用于排除多个值。
优化后的查询：

SELECT datname, usename, COUNT(*) 
FROM pg_stat_activity 
WHERE (datname, usename) NOT IN (('postgres', 'postgres')) 
GROUP BY datname, usename;

其他优化建议：

使用 pg_stat_statements 插件：该插件可以提供更详细的查询统计信息，帮助识别性能瓶颈。
定期清理连接：长时间未使用的连接可能导致资源浪费，定期清理空闲连接有助于提升性能。
监控系统资源：使用系统监控工具（如 top、htop）监控 CPU、内存和磁盘 I/O 使用情况，确保系统资源充足。

通过以上优化，您可以更高效地监控和管理 PostgreSQL 数据库的连接和活动。