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';
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.
From PostgreSQL v14 on, you can use the idle_session_timeout parameter that does exactly that.