Search code examples
databasepostgresqlconnection

How to kill Postgresql Sessions at "Idle In Transaction (aborted)" state automatically?

I have a PostgreSQL 15 server that runs in a Linux machine, with lots of clients connecting and doing some long transactions. Sometimes my max_connection numbers exceed, thus i had to kill them manually via running a query from table "pg_stat_activity".

This is kinda tiring process though. Are there any Postgresql parameters or tricks that can save me from this repeating issue?

I find pids via the query below:

select * from pg_stat_activity where datname='<mydatabase>'
and pid<>pg_backend_pid() and state='idle in transaction (aborted)'
and state_change < current_timestamp - INTERVAL '120' MINUTE;
Solution

  • There is no way to kill such sessions automatically. You have to call pg_terminate_backend() for each process ID.

    You should fix the application so that it ends its database transactions properly.