cookbooks/postgresql/templates/default/postgres_queries.yml.erb

   1 pg_process_idle:
   2   query: |
   3     WITH
   4       metrics AS (
   5         SELECT
   6           state,
   7           application_name,
   8           SUM(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change))::bigint)::float AS process_idle_seconds_sum,
   9           COUNT(*) AS process_idle_seconds_count
  10         FROM pg_stat_activity
  11         WHERE state ~ '^idle'
  12         GROUP BY state, application_name
  13       ),
  14       buckets AS (
  15         SELECT
  16           state,
  17           application_name,
  18           le,
  19           SUM(
  20             CASE WHEN EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change)) <= le
  21               THEN 1
  22               ELSE 0
  23             END
  24           )::bigint AS bucket
  25         FROM
  26           pg_stat_activity,
  27           UNNEST(ARRAY[1, 2, 5, 15, 30, 60, 90, 120, 300]) AS le
  28         GROUP BY state, application_name, le
  29         ORDER BY state, application_name, le
  30       )
  31     SELECT
  32       state,
  33       application_name,
  34       process_idle_seconds_sum as seconds_sum,
  35       process_idle_seconds_count as seconds_count,
  36       ARRAY_AGG(le) AS seconds,
  37       ARRAY_AGG(bucket) AS seconds_bucket
  38     FROM metrics JOIN buckets USING (state, application_name)
  39     GROUP BY 1, 2, 3, 4
  40   master: true
  41   metrics:
  42     - state:
  43         usage: "LABEL"
  44         description: "State"
  45     - application_name:
  46         usage: "LABEL"
  47         description: "Application Name"
  48     - seconds:
  49         usage: "HISTOGRAM"
  50         description: "Idle time of server processes"
  51
  52 pg_wal:
  53   query: "SELECT count(*) AS segment_count FROM pg_ls_waldir() WHERE name ~ '^[0-9A-Z]{24}$'"
  54   master: true
  55   metrics:
  56     - segment_count:
  57         usage: "GAUGE"
  58         description: "Number of WAL segments"