pg_stat_activity 是一个 统计视图，显示每个服务器进程的活动信息。
pg_stat_activity 在 PostgreSQL 7.2 中添加。
按 PostgreSQL 版本定义
pg_stat_activity (PostgreSQL 19)
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
leader_pid | integer | | |
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 | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query_id | bigint | | |
query | text | | |
backend_type | text | | |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 18)
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
leader_pid | integer | | |
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 | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query_id | bigint | | |
query | text | | |
backend_type | text | | |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 17)
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
leader_pid | integer | | |
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 | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query_id | bigint | | |
query | text | | |
backend_type | text | | |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 16)
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
leader_pid | integer | | |
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 | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query_id | bigint | | |
query | text | | |
backend_type | text | | |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 15)
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
leader_pid | integer | | |
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 | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query_id | bigint | | |
query | text | | |
backend_type | text | | |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 14)
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
leader_pid | integer | | |
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 | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query_id | bigint | | |
query | text | | |
backend_type | text | | |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 13)
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
leader_pid | integer | | |
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 | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query | text | | |
backend_type | text | | |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 12)
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
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 | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query | text | | |
backend_type | text | | |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 11)
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
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 | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query | text | | |
backend_type | text | | |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 10)
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+---------
datid | oid | | |
datname | name | | |
pid | integer | | |
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 | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query | text | | |
backend_type | text | | |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 9.6)
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
pid | integer |
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 |
wait_event_type | text |
wait_event | text |
state | text |
backend_xid | xid |
backend_xmin | xid |
query | text |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 9.5)
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
pid | integer |
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 |
state | text |
backend_xid | xid |
backend_xmin | xid |
query | text |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 9.4)
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
pid | integer |
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 |
state | text |
backend_xid | xid |
backend_xmin | xid |
query | text |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 9.3)
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
pid | integer |
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 |
state | text |
query | text |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 9.2)
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
pid | integer |
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 |
state | text |
query | text |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 9.1)
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
procpid | integer |
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 |
waiting | boolean |
current_query | text |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 9.0)
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
procpid | integer |
usesysid | oid |
usename | name |
application_name | text |
client_addr | inet |
client_port | integer |
backend_start | timestamp with time zone |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
waiting | boolean |
current_query | text |
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 8.4)
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
---------------+--------------------------+-----------
datid | oid |
datname | name |
procpid | integer |
usesysid | oid |
usename | name |
current_query | text |
waiting | boolean |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
backend_start | timestamp with time zone |
client_addr | inet |
client_port | integer |
View definition:
SELECT s.datid, d.datname, s.procpid, s.usesysid, u.rolname AS usename, s.current_query, s.waiting, s.xact_start, s.query_start, s.backend_start, s.client_addr, s.client_port
FROM pg_database d, pg_stat_get_activity(NULL::integer) s(datid, procpid, usesysid, current_query, waiting, xact_start, query_start, backend_start, client_addr, client_port), pg_authid u
WHERE s.datid = d.oid AND s.usesysid = u.oid;
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 8.3)
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
---------------+--------------------------+-----------
datid | oid |
datname | name |
procpid | integer |
usesysid | oid |
usename | name |
current_query | text |
waiting | boolean |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
backend_start | timestamp with time zone |
client_addr | inet |
client_port | integer |
View definition:
SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_waiting(s.backendid) AS waiting, pg_stat_get_backend_xact_start(s.backendid) AS xact_start, pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port
FROM pg_database d, ( SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u
WHERE pg_stat_get_backend_dbid(s.backendid) = d.oid AND pg_stat_get_backend_userid(s.backendid) = u.oid;
文档： pg_stat_activity
pg_stat_activity (PostgreSQL 8.2)
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
---------------+--------------------------+-----------
datid | oid |
datname | name |
procpid | integer |
usesysid | oid |
usename | name |
current_query | text |
waiting | boolean |
query_start | timestamp with time zone |
backend_start | timestamp with time zone |
client_addr | inet |
client_port | integer |
View definition:
SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_waiting(s.backendid) AS waiting, pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port
FROM pg_database d, ( SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u
WHERE pg_stat_get_backend_dbid(s.backendid) = d.oid AND pg_stat_get_backend_userid(s.backendid) = u.oid;
文档： pg_stat_activity
变更历史
- PostgreSQL 14
- PostgreSQL 13
- 添加了
leader_pid列 (提交 b025f32e)
- 添加了
- PostgreSQL 11
- PostgreSQL 10
- PostgreSQL 9.6
- PostgreSQL 9.4
- PostgreSQL 9.2
- PostgreSQL 9.1
- 添加了
client_hostname列 (提交 4a25bc14)
- 添加了
- PostgreSQL 9.0
- 添加了
application_name列 (提交 8217cfbd)
- 添加了
- PostgreSQL 8.3
- PostgreSQL 8.2
- 添加了
waiting列 (提交 9bf760f7)
- 添加了
- PostgreSQL 8.1
- PostgreSQL 7.4
- 添加了
query_start列 (提交 a1833100)
- 添加了
- PostgreSQL 7.2
- 添加 (提交 8d80b0d9)
示例
在 PostgreSQL 12 中实现
proddb=# SELECT * FROM pg_stat_activity LIMIT 1\x\g\x -[ RECORD 1 ]----+---------------------------------------------------------------------------------------- datid | 16389 datname | proddb pid | 28082 usesysid | 16384 usename | appuser application_name | client_addr | 127.0.0.1 client_hostname | client_port | 58160 backend_start | 2020-05-23 06:33:47.14178+02 xact_start | query_start | 2020-05-23 06:35:01.897185+02 state_change | 2020-05-23 06:35:01.897817+02 wait_event_type | Client wait_event | ClientRead state | idle backend_xid | backend_xmin | query | /* getCommentsForObject_r */ + | SELECT c.*, + | EXTRACT('epoch' FROM c.entry_created) AS entry_created_unix + | FROM entry c + | WHERE entry_reference_id = '14' + | AND entry_status IN (1, 0) + | ORDER BY entry_created + | backend_type | client backend
参考资料
- PostgreSQL 文档: pg_stat_activity
有用链接
- 理解 pg_stat_activity - depesz 的 2022 年 7 月博客文章