CODEWITHSUNDEEP
postgresqlconnectionkill
Matias
Matias

Reputation: 589

Kill idle connections sitting for more than 4 hours

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

Upvotes: 1

Views: 3752

Answers (2)

klin
klin

Reputation: 121834

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.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247830

From PostgreSQL v14 on, you can use the idle_session_timeout parameter that does exactly that.

Upvotes: 1

Related Questions