postgres diagnostics.sql

-- These queries have been tested on a Postgres 9.5 Cluster running on RDS

-- Show queries running longer than 2 minutes
SELECT now() - query_start as "runtime", usename, datname, waiting, state, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '2 minutes'::interval
 ORDER BY runtime DESC;
 
--
SET application_name='%elliot%';
SELECT blocked_locks.pid                    AS blocked_pid,
         blocked_activity.usename           AS blocked_user,
         blocking_locks.pid                 AS blocking_pid,
         blocking_activity.usename          AS blocking_user,
         blocked_activity.query             AS blocked_statement,
         blocking_activity.query            AS current_statement_in_blocking_process,
         blocked_activity.application_name  AS blocked_application,
         blocking_activity.application_name AS blocking_application
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
 
    JOIN pg_catalog.pg_stat_activity blocking_activity 
      ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;


SELECT a.datname,
       c.relname,
       l.transactionid,
       l.mode,
       l.GRANTED,
       a.usename,
       a.query, 
       a.query_start,
       age(now(), a.query_start) AS "age", 
       a.pid 
  FROM  pg_stat_activity a
   JOIN pg_locks         l ON l.pid = a.pid
   JOIN pg_class         c ON c.oid = l.relation
  ORDER BY a.query_start;


SELECT query,state,waiting,pid 
FROM pg_stat_activity
WHERE NOT (state='idle' OR pid=pg_backend_pid());


SELECT 
  blockeda.pid    AS blocked_pid, 
  blockeda.query  AS blocked_query,
  blockinga.pid   AS blocking_pid, 
  blockinga.query AS blocking_query
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda 
  ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl 
  ON (blockingl.transactionid=blockedl.transactionid AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga 
  ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted ;