PostgreSQLで各クライアントからの接続数とその内訳を確認するSQL

SELECT
  client_addr,
  sum(1) numconns,
  sum(CASE WHEN state = 'active' THEN 1 ELSE 0 END) active,
  sum(CASE WHEN state = 'idle' THEN 1 ELSE 0 END) idle,
  sum(CASE WHEN state = 'idle in transaction' THEN 1 ELSE 0 END) idle_in_transaction,
  sum(CASE WHEN state = 'idle in transaction (aborted)' THEN 1 ELSE 0 END) aborted,
  sum(CASE WHEN state = 'fastpath function call' THEN 1 ELSE 0 END) fastpath,
  sum(CASE WHEN state = 'disabled' THEN 1 ELSE 0 END) disabled,
  sum(CASE WHEN state IS NULL THEN 1 ELSE 0 END) undefined
FROM
  pg_stat_activity
GROUP BY
  client_addr
ORDER BY
  client_addr;