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.

Gapin

how are you connecting to the DB? socketTimeout might be what you are looking for. – Katt

We have this legacy Pylons web app, and we used SQLAlchemy but apparently we didn't use it properly. I don't remember. We are trying to fix the leak. socketTimeout from the doc it looks like this close the connection to the DB, entirely. I am trying to close each idle, and the counter starts as soon as the connection is established. – Gapin

see #12391674 – Katt

@Gapin When you say "close each idle" - do you mean terminate <IDLE> in transaction sessions, leaving the session running but in <IDLE> state? In other words, terminate the transaction but not the session? (Downvoted: unclear question) – Camacho

@CraigRinger after a while, we reach max client connection. To solve that, we must restart the webapp, which forces to restart postgresql as well. That wipe out every connection. When we see these idle forever, we are asking if we could set a timeout on each connection/session (I honestly don't know the correct terminology, sorry). If a transaction takes 5 minutes for a normal web app something must be wrong.... – Gapin

what about something like auto-reconnect? – Determinism

Answer 1

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 <idle> 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 <> pg_backend_pid()
      AND state = 'idle'
      AND state_change < 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.

Camacho

Good, but it will kill other PgAdmin backends. Use additional condition application_name='' – Mandimandible

Can I run pg_terminate_backend if I'm using pgbouncer? – Reorganize

@HenleyChiu I don't see why not, though I haven't specifically checked. – Camacho

Running this seems to have killed my WAL sender process – Maul

@CraigRinger even a psql connection is considered as idle connection. And why does one has to close the idle connection at first place. I have a long running code that establish connection with pg perform some dml operation and the then wait for the message over queue and then perform some more dml operation.Now during that period i.e while it is waiting over queue(for message) as mention above even then the connection with postges is idle. why should i close it. – Ledger

consider adding this at your 'state' matching: state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') – Poolroom

One question, how does pgbouncer help here? We saw connection leaks when GCP did kill our preemptible machines, somehow postgres didn't clean up the connection. How does pgbouncer clean those stale connections up? – Demur

Answer 2

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

Ultramicrometer

It sucks having to ask something so simple but I'm brand new to databases in general - Could you please give a very basic example of how to use this function? – Poltroon

Anything like this in previous versions of PostgreSQL?? – Aer

No, something akin to the other answers is required for previous versions. – Ultramicrometer

Do you need to set this parameter at every restart of database? Or after you did once you can forget about? Thanks – Verenaverene

SET SESSION is just for the current session (it will go back to the default once you open a new connection). You can also set config parameters on a database level using e.g. ALTER DATABASE SET idle_in_transaction_session_timeout = '5min', or using configuration files (see postgresql.org/docs/current/static/config-setting.html). – Ultramicrometer

or you can set in postgresql.conf or via alter system. Very nice. – Scuttle

You can also set this on a DB level: ALTER DATABASE <DB name> SET idle_in_transaction_session_timeout TO '5min'; – Arndt

Note that this would not kill state = 'idle'. It would only kill session you see as state = 'idle in transaction'. And it is a good thing. Because when you have connection pooling you will have a lot of idle connections and those would probably not be a problem. – Herrah

Answer 3

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 = '<IDLE>'
AND
   now() - query_start > '00:10:00';

Begone

pg_terminate_backend is in since 8.4 – Farina

Answer 4

if you are using postgresql 9.6+, then in your postgresql.conf you can set

idle_in_transaction_session_timeout = 30000 (msec)

Pachalic

Answer 5

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.

Kramer

Answer 6

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.

Arrest

Answer 7

Another option is set this value "tcp_keepalives_idle". Check more in documentation https://www.postgresql.org/docs/10/runtime-config-connection.html.

Etan