Postgres - Surgery

Collection of Postgres commands for diagnosing issues. Display Vertically

\x

Count Long Running Active Queries

SELECT 
    COUNT(*)
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND pg_stat_activity.state = 'active';

Show Long Running Active Queries

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND pg_stat_activity.state = 'active'
ORDER BY (now() - pg_stat_activity.query_start) DESC;

Reference

• Finding and killing long running queries on PostgreSQL
• Useful PostgreSQL Queries For Monitoring Purpose