Wir überwachen die PostgreSQL-Datenbank - wer ist schuld und was zu tun ist

WITH event_types(wait_event_type) AS(
  VALUES
    ('lwlock')
  , ('lock')
  , ('bufferpin')
  , ('client')
  , ('extension')
  , ('ipc')
  , ('timeout')
  , ('io')
)
, events(wait_event) AS(
  VALUES
    ('walwritelock')
  , ('wal_insert')
  , ('buffer_content')
  , ('buffer_io')
  , ('lock_manager')
  , ('relation')
  , ('extend')
  , ('page')
  , ('tuple')
  , ('transactionid')
  , ('virtualxid')
  , ('speculative token')
  , ('object')
  , ('userlock')
  , ('advisory')
  , ('clientread')
  , ('datafileextend')
  , ('datafileread')
  , ('datafilewrite')
  , ('slruread')
  , ('slruwrite')
)
, states(state) AS(
  VALUES
    ('running')
  , ('maintenance')
  , ('waiting')
  , ('transaction')
  , ('idle')
)
, stats AS(
  SELECT
    pid
  , datname
  , state
  , lower(wait_event_type) wait_event_type
  , lower(wait_event) wait_event
  , query
  FROM
    pg_stat_activity
  WHERE
    pid <> pg_backend_pid()
)
, dbs AS(
  SELECT
    datname
  FROM
    pg_database db
  WHERE
    NOT db.datistemplate
)
  SELECT
    date_part('epoch', now())::integer ts
  , coalesce(s.qty, 0) val
  , dbs.datname dbname
  , states.state
  , true total
  FROM
    dbs
  CROSS JOIN
    states
  NATURAL LEFT JOIN
    (
      SELECT
        datname
      , CASE
          WHEN query ~* E'^(\\s*(--[^\\n]*\\n|/\\*.*\\*/|\\n))*(autovacuum|VACUUM|ANALYZE|REINDEX|CLUSTER|CREATE|ALTER|TRUNCATE|DROP)' THEN
            'maintenance'
          WHEN wait_event IS NOT NULL AND
            wait_event <> 'clientread' AND
            state = 'active' THEN
            'waiting'
          WHEN state = 'active' THEN
            'running'
          WHEN state = 'idle' THEN
            'idle'
          WHEN state IN ('idle in transaction', 'idle in transaction (aborted)') THEN
            'transaction'
          WHEN state = 'fastpath function call' THEN
            'fastpath'
          ELSE
            'disabled'
        END state
      , count(*) qty
      FROM
        stats
      GROUP BY
        1, 2
    ) s
UNION
  SELECT
    date_part('epoch', now())::integer ts
  , coalesce(t.qty, 0) val
  , dbs.datname dbname
  , event_types.wait_event_type
  , false total
  FROM
    dbs
  CROSS JOIN
    event_types
  NATURAL LEFT JOIN
    (
      SELECT
        datname
      , wait_event_type
      , count(*) qty
      FROM
        stats
      WHERE
        wait_event_type IS NOT NULL
      GROUP BY
        1, 2
    ) t
UNION
  SELECT
    date_part('epoch', now())::integer ts
  , coalesce(e.qty, 0) val
  , dbs.datname dbname
  , events.wait_event
  , false total
  FROM
    dbs
  CROSS JOIN
    events
  NATURAL LEFT JOIN
    (
      SELECT
        datname
      , wait_event
      , count(*) qty
      FROM
        stats
      WHERE
        wait_event IS NOT NULL
      GROUP BY
        1, 2
    ) e;

SELECT
  extract(epoch from now())::integer ts
, datname dbname
, pg_stat_get_db_tuples_returned(oid) tup_returned
, pg_stat_get_db_tuples_fetched(oid) tup_fetched
, pg_stat_get_db_tuples_inserted(oid) tup_inserted
, pg_stat_get_db_tuples_updated(oid) tup_updated
, pg_stat_get_db_tuples_deleted(oid) tup_deleted
, pg_stat_get_db_xact_commit(oid) xact_commit
, pg_stat_get_db_xact_rollback(oid) xact_rollback
FROM
  pg_database
WHERE
  NOT datistemplate;