pg_stat_activity

A statistics view showing activity information about each server process

pg_stat_activity is a statistics view showing activity information about each server process.

pg_stat_activity was added in PostgreSQL 7.2.

Definition by PostgreSQL version

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                     |           |          |
    

Documentation: 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                     |           |          |
    

Documentation: 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                     |           |          |
    

Documentation: 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                     |           |          |
    

Documentation: 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                     |           |          |
    

Documentation: 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                     |           |          |
    

Documentation: 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                     |
    

Documentation: 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                     |
    

Documentation: 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                     |
    

Documentation: 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                     |
    

Documentation: 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                     |
    

Documentation: 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                     |
    

Documentation: 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                     |
    

Documentation: 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;
    

Documentation: 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;
    

Documentation: 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;
    

Documentation: pg_stat_activity

Change history

Examples

Executed in 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

Categories

Monitoring, Statements, Statistics / monitoring view

See also

track_activity_query_size