How to use tcp_keepalives settings in Postgresql?
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?
Answer
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.