How to close idle connections in PostgreSQL automatically?

postgres kill connections
postgres idle in transaction
postgres idle_in_transaction_session_timeout
kill idle queries postgres
postgres session vs connection
postgres idle processes
aws rds postgres kill connections
postgresql connection manager

Some clients connect to our postgresql database but leave the connections opened. Is it possible to tell Postgresql to close those connection after a certain amount of inactivity ?

TL;DR

IF you're using a Postgresql version >= 9.2 THEN use the solution I came up with

IF you don't want to write any code THEN use arqnid's solution

Is it possible to configure PostgreSQL to automatically close idle , Once those idle connections are found, a simple call to pg_terminate_backend will close them. See an in depth description of this approach in the anwser of the​  The chosen solution comes down like this: First, we upgrade to Postgresql 9.2. Then, we schedule a thread to run every second. When the thread runs, it looks for any old inactive connections. A connection is considered inactiveif its stateis either idle, idle in transaction, idle in transaction (aborted)or disabled.

If you are using PostgreSQL >= 9.6 there is an even easier solution. Let's suppose you want to delete all idle connections every 5 minutes, just run the following:

alter system set idle_in_transaction_session_timeout='5min';

In case you don't have access as superuser (example on Azure cloud), try:

SET SESSION idle_in_transaction_session_timeout = '5min';

But this latter will work only for the current session, that most likely is not what you want.

To disable the feature,

alter system set idle_in_transaction_session_timeout=0;

or

SET SESSION idle_in_transaction_session_timeout = 0;

(by the way, 0 is the default value).

If you use alter system, you must reload configuration to start the change and the change is persistent, you won't have to re-run the query anymore if, for example, you will restart the server.

To check the feature status:

show idle_in_transaction_session_timeout;

Re: Terminating Idle Connections, Is there a way inside of Postgresql to automatically terminate idle connections? I would like to terminate any connection to my database that Finding and Closing Idle Connections in PostgreSQL. To enumerate all database connections that have been idle for at least 10 minutes: SELECT DATE_TRUNC('second',NOW()-query_start) AS age, client_port, current_query FROM pg_stat_activity WHERE current_query = '<IDLE>' AND NOW() - query_start > '00:10:00' ORDER BY age DESC;

Connect through a proxy like PgBouncer which will close connections after server_idle_timeout seconds.

Finding and Closing Idle Connections in PostgreSQL, To enumerate all database connections that have been idle for at least 10 minutes: SELECT DATE_TRUNC('second',NOW()-query_start) AS age,  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

How to auto terminate IDLE connection, It will not terminate any IDLE connection on the master. 00:00:00 postgres: port 42702, gpadmin flightdata 172.28.8.250(33959) con24 seg1  @Priya:- You can also use the PgBouncer which will close connections after server_idle_timeout seconds. – Rahul Tripathi Jun 19 '15 at 6:57 I dont want to use seperate script to kill the connection.I need to include the query on the servlet.Its a product,it cant be done for every user.

Tracking and managing your Postgres connections, Managing connections in Postgres is a topic that seems to come up several idle – This is where you have opened a connection to the DB (most A statement timeout will automatically kill queries that run longer than the  In this post, I am sharing one of the important script to kill all running idle connections and sessions of the PostgreSQL Database. I have prepared this script such a way that you can also filter idle connections base on a particular time interval.

Connection handling best practice with PostgreSQL, Managing connections in Microsoft Azure Database for PostgreSQL is a The connections in Postgres aren't free each connection, whether idle or active, A statement timeout will automatically end queries that run longer  postgresql.conf can help to find the culprit. On Tuesday 09 May 2006 16:29, Chris Hoover wrote: > 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

Comments
  • You could use a cron job to look at when the connection was last active (see pg_stat_activity) and use pg_terminate_backend to kill old ones. Easily expressed in a simple query. I'm not sure if pg_terminate_backend was available in the pretty-ancient 8.3, though.
  • I have a similar problem with my service using C3P0 pooling -- all the connections were closed (finally block) but after i did a load test, the number of idle connections didn't drop after the load test finished. In pg_stat_activity table i have lots of idle connections that's running SELECT 1 or SHOW TRANSACTION ISOLATION LEVEL. Then when I run a second load test, the number kept growing, does this mean that the idle connections were not re-used during second test? Can i use the same solution here to fix it?
  • @Zip Can you please turn your comment into a new question and put a link to this new question here? I'll be easier to anwser you.
  • @Stephan the question is here: stackoverflow.com/questions/51682584/… . Thanks!
  • Finally, we moved to Postgresql 9.2 to get advantage of pg_terminate_backend. We used a cron like job to periodically invoke pg_terminate_backend.
  • Can you post a script which you use?
  • Interesting feature. However, it doesn't allow fine connections selection (keeping one connection alive, whitelisting some applications connections ...)
  • Maybe it has the possibility to write a whitelist, but i am not sure about. The command is new also for me.
  • This will only close connections that opened a transaction and failed to close (commit or rollback) it within the given timeout (as the name "idle_in_transaction_session_timeout" suggests). It won't close connections that are just "idle".
  • "idle_in_transaction_session_timeout" can also be set in postgresql.conf
  • can pg pool II do the same?
  • @Stephan Thank you! I would miss the answer if you had not tagged me in the comment.