PostgreSQL performance troubleshooting

pgsql_performance.sql

psql -h <host> -U <user> -W -d postgres

SELECT * from pg_stat_activity;
SELECT * from pg_stat_database;

# Queries running during >5 minutes
SELECT
  pid,
  datname,
  query,
  now() - pg_stat_activity.query_start AS duration,
  time(pg_stat_activity.query_start) as started_at
FROM pg_stat_activity
WHERE pg_stat_activity.query <> ''::text
AND now() - pg_stat_activity.query_start > interval '5 minutes';

SELECT
  pid,
  datname,
  query,
  now() - pg_stat_activity.query_start AS duration
FROM pg_stat_activity
WHERE pg_stat_activity.query <> ''::text
AND now() - pg_stat_activity.query_start > interval '5 minutes';

# Kill pg sub-process:
SELECT pg_cancel_backend(<pid>);

# Really terminate it!
SELECT pg_terminate_backend(<pid>) from pg_stat_activity where datname='<datname>';