Re: Backend timeout

On Tue, Sep 16, 2008 at 7:21 AM, Dragan Ciric <dragan(dot)ciric(at)a-asoft(dot)com> wrote:
> Hi!
>
> We need some help.
> Sometimes we have broken connections with backend ( postgresql server ).
> When this occurs, we have "idle in transaction" indication on server side. Can you
> answer us, how long will server stay in this state and what happens with this
> broken connection ( client started transaction, but can't send commit or rollback )?

If the client socket on the other end has simply disappeared, then the
connection will be harvested approximately net.ipv4.tcp_keepalive_time
+ net.ipv4.tcp_keepalive_probes * net.ipv4.tcp_keepalive_intvl seconds
later. On default setups, this is something like 7200 + 90 * 9 for a
total of 8010 seconds. i.e. just over an hour.

On later model postgresql's you can change these settings for just the
pgsql server to something more sane, like

net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_intvl = 30

which get it down to 6.5 minutes or so before stale connections are harvested.

The advantage to using tcp_keepalive is it won't kill living but idle
connections.