How to use tcp_keepalives settings in Postgresql?
keep-alive, locking, postgresql
Solution
I think you need to configure your operating system instead. Changing keepalive parameters by programs is not widely supported yet. This should help you: Using TCP keepalive to Detect Network Errors
Also your parameters are chosen badly. If `tcp_keepalives_count=1` worked then even one lost keepalive packet will drop your connection. And single packets get lost often. I'd use the following in `/etc/sysctl.conf` on MacOSX/FreeBSD: net.inet.tcp.keepidle = 60000 net.inet.tcp.keepintvl = 10000 OS will then drop connections at most 140 seconds (60 seconds of idle + 8 keepalive packets in 10 seconds intervals) after loosing connectivity.
Problem
Postgresql has 3 keepalive settings for managing dropped connections (in postgresql.conf): tcp_keepalives_count tcp_keepalives_idle tcp_keepalives_interval By default these are 0. The behavior I would like is for Postgresql to drop client connections after a period of time, should the client lose its network connection or go to sleep. I am currently using these values: tcp_keepalives_count = 1 tcp_keepalives_idle = 60 tcp_keepalives_interval = 60 I am running PostgreSQL 8.4 on Mac OS X, but it doesn't seem to have any effect. My test is that I lock a row in a table (using SELECT FOR UPDATE) and disconnect the workstation from the network. But in Postgresql I still see that workstation holding the lock. I would expect that after the time has passed (60 seconds in this case) the connection would be terminated and the lock would be released. Either I am doing something wrong or I am completely misunderstanding how this is supposed to work. Any advice?