Is there a timeout for idle PostgreSQL connections

Question

1 S postgres  5038   876  0  80   0 - 11962 sk wai 09 57          00 00 00 postgres  postgres my app   1 45035  idle                                                                                  1 S postgres  9796   876  0  80   0 - 11964 sk wai 11 01          00 00 00 postgres  postgres my app   1 43084  idle                I see a lot of them  We are trying to fix our connection leak  But meanwhile  we want to set a timeout for these idle connections  maybe max to 5 minute

User · Answer

There is a timeout on broken connections (i.e. due to network errors), which relies on the OS' TCP keepalive feature. By default on Linux, broken TCP connections are closed after ~2 hours (see sysctl net.ipv4.tcp_keepalive_time).

There is also a timeout on abandoned transactions, idle_in_transaction_session_timeout and on locks, lock_timeout. It is recommended to set these in postgresql.conf.

But there is no timeout for a properly established client connection. If a client wants to keep the connection open, then it should be able to do so indefinitely. If a client is leaking connections (like opening more and more connections and never closing), then fix the client. Do not try to abort properly established idle connections on the server side.

User · Answer

It sounds like you have a connection leak in your application because it fails to close pooled connections  You aren t having issues just with  lt idle gt  in transaction sessions  but with too many connections overall   Killing connections is not the right answer for that  but it s an OK-ish temporary workaround   Rather than re-starting PostgreSQL to boot all other connections off a PostgreSQL database  see  How do I detach all other users from a postgres database  and How to drop a PostgreSQL database if there are active connections to it    The latter shows a better query   For setting timeouts  as  Doon suggested see How to close idle connections in PostgreSQL automatically   which advises you to use PgBouncer to proxy for PostgreSQL and manage idle connections  This is a very good idea if you have a buggy application that leaks connections anyway  I very strongly recommend configuring PgBouncer   A TCP keepalive won t do the job here  because the app is still connected and alive  it just shouldn t be   In PostgreSQL 9 2 and above  you can use the new state change timestamp column and the state field of pg stat activity to implement an idle connection reaper  Have a cron job run something like this   SELECT pg terminate backend pid      FROM pg stat activity     WHERE datname    regress        AND pid  lt  gt  pg backend pid         AND state    idle        AND state change  lt  current timestamp - INTERVAL  5  MINUTE    In older versions you need to implement complicated schemes that keep track of when the connection went idle  Do not bother  just use pgbouncer

User · Answer

A possible workaround that allows to enable database session timeout without an external scheduled task is to use the extension pg timeout that I have developped

User · Answer

In PostgreSQL 9 6  there s a new option idle in transaction session timeout which should accomplish what you describe  You can set it using the SET command  e g    SET SESSION idle in transaction session timeout    5min

User · Answer

In PostgreSQL 9 1  the idle connections with following query   It helped me to ward off the situation which warranted in restarting the database   This happens mostly with JDBC connections opened and not closed properly   SELECT    pg terminate backend procpid  FROM    pg stat activity WHERE    current query     lt IDLE gt   AND    now   - query start  gt   00 10 00

User · Answer

Another option is set this value  quot tcp keepalives idle quot   Check more in documentation https   www postgresql org docs 10 runtime-config-connection html

User · Answer

if you are using postgresql 9 6   then in your postgresql conf you can set  idle in transaction session timeout   30000  msec

[postgresql] Is there a timeout for idle PostgreSQL connections?

Examples related to postgresql

Examples related to database-connection