Kill idle connections sitting for more than 4 hours

Question

We would like to kill idle connections on Postgres which have been sitting there for more than 4 hours, ideally, we would like to create a cron to do this.

What's the field we should be looking at in the pg_stat_activity? We have these:

backend_start
query_start
state_change

We assume is backend_start...

We then could do something like this:

select pg_terminate_backend(pid) 
from pg_catalog.pg_stat_activity
where backend_start<CURRENT_TIME-4Hours and state='idle';

Answer 1

It depends on the connection rules. If a connection is open only to execute a single transaction (or a group of transactions provided by a batch job), you can virtually kill all idle sessions. When connections are allowed to remain open and wait for new online queries, state-change should be considered as this timestamp indicates how long the connection has been idle.