select pid as process_id,
usename as username,
datname as database_name,
client_addr as client_address,
application_name,
backend_start,
state,
state_change
from pg_stat_activity;
select datname,count(*) from pg_stat_activity group by datname;
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';
You can change the interval time.
SELECT query FROM pg_stat_activity WHERE pid=xxxx;
$ # show the top 10 cpu using threads, sorted numerically
$ ps -eo %cpu,%mem,pid,user,args | sort -k1 -r -n | head -10
In order to cancel long running queries you should execute:
SELECT pg_cancel_backend(pid);
The pid parameter is the value returned in the previous step.
pg_cancel_backend
can take a few seconds to stop the query. If it doesn't work, you can use:
SELECT pg_terminate_backend(pid);
Be careful: pg_terminate_backend
is the kill -9 in PostgreSQL. It will terminate the entire
process which can lead to a full database restart in order to recover consistency.
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;