410-pg_activity.yml

##
# SYNOPSIS
#       pg_activity_*
#
# DESCRIPTION
#       PostgreSQL backend activity group by database and state
#
# OPTIONS
#       Tags       [cluster]
#       TTL        10
#       Priority   0
#       Timeout    100ms
#       Fatal      false
#       Version    90400 ~ higher
#       Source     410-pg_activity.yml
#
# METRICS
#       datname (LABEL)
#           Name of the database this backend is connected to
#       state (LABEL)
#           Current overall state of this backend.
#       count (GAUGE)
#           Count of connection among (datname,state)
#       max_duration (GAUGE)
#           Max duration since last state change among (datname, state)
#       max_tx_duration (GAUGE)
#           Max transaction duration since state change among (datname, state)
#       max_conn_duration (GAUGE)
#           Max backend session duration since state change among (datname, state)
#

pg_activity:
  name: pg_activity
  desc: PostgreSQL backend activity group by database and state

  query: |
    SELECT datname, state, coalesce(count, 0) AS count, coalesce(max_duration, 0) AS max_duration, coalesce(max_tx_duration, 0) AS max_tx_duration, coalesce(max_conn_duration, 0) AS max_conn_duration FROM
        (SELECT d.datname, a.state FROM pg_database d, unnest(ARRAY ['active','idle','idle in transaction','idle in transaction (aborted)','fastpath function call','disabled']) a(state) WHERE d.datallowconn AND NOT d.datistemplate) base
          LEFT JOIN (SELECT datname, state, count(*) AS count, max(extract(epoch from now() - state_change)) AS max_duration, max(extract(epoch from now() - xact_start))
          AS max_tx_duration, max(extract(epoch from now() - backend_start)) AS max_conn_duration FROM pg_stat_activity WHERE pid <> pg_backend_pid() GROUP BY 1,2) data USING (datname,state);
  ttl: 10
  min_version: 090400
  tags:
    - cluster

  metrics:
    - datname:
        usage: LABEL
        description: Name of the database this backend is connected to
    - state:
        usage: LABEL
        description: Current overall state of this backend.
    - count:
        usage: GAUGE
        description: Count of connection among (datname,state)
    - max_duration:
        usage: GAUGE
        description: Max duration since last state change among (datname, state)
    - max_tx_duration:
        usage: GAUGE
        description: Max transaction duration since state change among (datname, state)
    - max_conn_duration:
        usage: GAUGE
        description: Max backend session duration since state change among (datname, state)