How to find long running queries in PostgreSQL

As administrator, From time to time we need to investigate if there is any query running indefinitely on PostgreSQL. These long running queries may impact database performance and probably they are stuck on some background process.

Long running queries in PostgreSQL

1. Following queries will return currently running top 10 queries and longest running queries in the front.

select current_timestamp-query_start as runtime,
datname,usename, query FROM pg_stat_activity
where state='active'
order by 1 desc
limit 10;

2. To get list of queries that have been running more than 1 minute in PostgreSQL..

select current_timestamp-query_start as runtime,
datname,usename, query FROM pg_stat_activity
where state='active' and current_timestamp-query_start> '1 min'
order by 1 desc
limit 10;

3. To get list of queries that have been running more than 5 minutes in PostgreSQL.

select current_timestamp-query_start as runtime,
datname,usename, query FROM pg_stat_activity
where state='active' and current_timestamp-query_start> '5 min'
order by 1 desc
limit 10;

Pre 9.5 verions:

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';

How to kill long running processes:

1. First try to cancel the query.

If you have proper approvals to cancel process, then execute following command.

SELECT pg_cancel_backend(__pid__);

or

SELECT pg_cancel_backend(pid);

2. After query cancelled, still query is present on the PostgreSQL server. If you have proper approvals to kill the process, then execute following command.

SELECT pg_terminate_backend(__pid__);

or

SELECT pg_terminate_backend(pid);