Kill idle sessions in PostgreSQL
To find out the sessions which are in idle state we use the pg_stat_activity view by using the below query.
select datname,usename,application_name,state,current_timestamp-state_change as idle_since from pg_stat_activity where state=’idle’;
To find the queries which are idle since “100” seconds use the below query. If you want to get sessions in idle state more than 100 seconds then replace the 100 in below query with different time.
WITH T AS
(SELECT pid,
datname,
usename,
application_name,
state,
(EXTRACT(HOUR FROM (CURRENT_TIMESTAMP-state_change)) * 60*60) + (EXTRACT (MINUTES FROM (CURRENT_TIMESTAMP-state_change)) * 60) + (EXTRACT (SECONDS FROM (CURRENT_TIMESTAMP-state_change))) AS idle_since
FROM pg_stat_activity
WHERE state=’idle’)
SELECT datname,
usename,
application_name,
state,
idle_since
FROM T
WHERE idle_since>100;
datname | usename | application_name | state | idle_since
———-+———-+——————+——-+————
postgres | postgres | psql | idle | 245.355612
(1 row)
To kill the sessions which are in idle state for more than 100 seconds use the below query.
WITH T AS
(SELECT pid,
datname,
usename,
application_name,
state,
(EXTRACT(HOUR FROM (CURRENT_TIMESTAMP-state_change)) * 60*60) + (EXTRACT (MINUTES FROM (CURRENT_TIMESTAMP-state_change)) * 60) + (EXTRACT (SECONDS FROM (CURRENT_TIMESTAMP-state_change))) AS idle_since
FROM pg_stat_activity
WHERE state=’idle’)
SELECT pg_terminate_backend(pid)
FROM T
WHERE idle_since>100;
pg_terminate_backend
———————-
t
(1 row)
select datname,usename,application_name,state,current_timestamp-state_change as idle_since from pg_stat_activity where state=’idle’;
To find the queries which are idle since “100” seconds use the below query. If you want to get sessions in idle state more than 100 seconds then replace the 100 in below query with different time.
WITH T AS
(SELECT pid,
datname,
usename,
application_name,
state,
(EXTRACT(HOUR FROM (CURRENT_TIMESTAMP-state_change)) * 60*60) + (EXTRACT (MINUTES FROM (CURRENT_TIMESTAMP-state_change)) * 60) + (EXTRACT (SECONDS FROM (CURRENT_TIMESTAMP-state_change))) AS idle_since
FROM pg_stat_activity
WHERE state=’idle’)
SELECT datname,
usename,
application_name,
state,
idle_since
FROM T
WHERE idle_since>100;
datname | usename | application_name | state | idle_since
———-+———-+——————+——-+————
postgres | postgres | psql | idle | 245.355612
(1 row)
To kill the sessions which are in idle state for more than 100 seconds use the below query.
WITH T AS
(SELECT pid,
datname,
usename,
application_name,
state,
(EXTRACT(HOUR FROM (CURRENT_TIMESTAMP-state_change)) * 60*60) + (EXTRACT (MINUTES FROM (CURRENT_TIMESTAMP-state_change)) * 60) + (EXTRACT (SECONDS FROM (CURRENT_TIMESTAMP-state_change))) AS idle_since
FROM pg_stat_activity
WHERE state=’idle’)
SELECT pg_terminate_backend(pid)
FROM T
WHERE idle_since>100;
pg_terminate_backend
———————-
t
(1 row)
NOTE : The above query will kill all sessions which are idle for more than 100 seconds , if you want to kill all idle sessions then use the below query.
postgres=# select pg_terminate_backend(pid) from pg_stat_activity where state=’idle’;
pg_terminate_backend
———————-
t
(1 row)
Viswamitra's blog 