|
Chris, In a shell script, you could do a “ps
auxwww|grep ‘idle in transaction’” which would give you a
list of all processes with a status of “idle in transaction.” Then
run through each line of the output, use “awk” to find the process
id, and issue a “kill <pid>” to each process. Obviously this kills all idle in
transaction processes, of which some may be genuinely waiting. The best idea would be to see where your
idle transactions are coming from, and work out why they’re being left
idle and not disconnecting (ie. In your app/s.) Better to find the cause of
the problem than try and patch it. A “ps auxwww|grep postgres”
will show you the host of the connection, the DB it’s connect to and the
user (as long as this is enabled in your PG config.)
From:
pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Chris Hoover Is there a way inside of Postgresql to automatically terminate idle
connections? I would like to terminate any connection to my database that
has not has any activity for a specified period of time. I would like to
be able to do this despite the state of the connection (the majority of my
truly idle connections show "idle in transaction" in the process
table). I would love for the db engine to do this, but if it can't, is
there an easy way to do this outside the database with some sort of script? |
