7

According to PostgresSQL docs there is parameter keepalives and further parameters keepalives_idle, keepalives_interval and keepalives_count which are relevant only if keepalives=1.

But it is not clear to me what does it mean if the parameter keepalives is set to zero?

What exactly happens if the keepalives parameter is set to zero (keepalives=0)?

Does it mean that a PostgreSQL session is never terminated? What happens if client application is closed? What happens if network connection is broken? Does it mean that the PostgreSQL server will never ever terminate idle session?

cpinamtz
  • 773
  • 8
  • 13
zlatko
  • 596
  • 1
  • 6
  • 23
  • https://blog.stephencleary.com/2009/05/detection-of-half-open-dropped.html – Craig Ringer Oct 23 '17 at 00:29
  • yes, it does afaik. https://stackoverflow.com/a/13245020/5315974 you can terminate sessions which now()-state_change > '1 hour' or so in a job, or configure pgbouncer or try other ways, but per se postgres server does not teminate idle sessions – Vao Tsun Oct 23 '17 at 07:32

1 Answers1

9

keepalives is a client-side setting.

If you set it to 0, the TCP socket on the client machine will have the SO_KEEPALIVE socket option set to 0 (the default setting (on Linux) is 1, meaning that keepalive is enabled).

Then it won't send keepalive messages on idle connections to a database server to check if the other side is still alive.

The effect is that the client will not detect if the database server terminates unexpectedly, that is, without closing the TCP connection.

Usually it is not necessary to enable keepalive on the client side: The client will notice when the server has dies anyway when it next tries to talk to it.

The other use for keepalive messages would be to keep a firewall or proxy from closing an idle connection. But since the PostgreSQL server enables keepalive on the server side anyway, that should be taken care of.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 2
    > _PostgreSQL server enables keepalive on the server side anyway, that should be taken care of._ What does this mean? Suppose there is a TCP proxy between the client and the Postgres server. How does the Postgres server notify the proxy to keep the connections alive? What mechanism allows the server to do this? – Satyajeet Kanetkar May 19 '20 at 17:46
  • @SatyajeetKanetkar The kernel's TCP stack. It will start sending keepalive packets after two hours of inactivity by default. – Laurenz Albe May 19 '20 at 19:40
  • @LaurenzAlbe I think if the proxy works on an application layer you'll have to enable tcp-keepalive on the server *and* on the client side. Otherwise only the connection proxy <> server will be kept alive while the connection client <> proxy might still timeout. – gucki Nov 18 '20 at 09:28
  • @gucki Your question seems unrelated to this one. Of course, if you are using pgBouncer, you'll have to define keepalive there separately. – Laurenz Albe Nov 18 '20 at 09:50
  • @LaurenzAlbe You are right, it was more a comment to the other comments. In my case it's istio/ envoy which sits between the app and the db and causes problems for long-idle connections. – gucki Nov 18 '20 at 10:30
  • "The default setting (on Linux) is off." - The docs say "The default value is 1, meaning on,". Did that change at some point? – jberryman Mar 18 '21 at 20:58
  • @jberryman No, I must have been confused. Fixed. – Laurenz Albe Mar 19 '21 at 05:17