如何在 Postgresql 中使用 tcp_keepalives 设置？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2166872/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use tcp_keepalives settings in Postgresql?
提问by Paul Lefebvre
Postgresql has 3 keepalive settings for managing dropped connections (in postgresql.conf):
Postgresql 有 3 个 keepalive 设置用于管理断开的连接（在 postgresql.conf 中）：
tcp_keepalives_count
tcp_keepalives_idle
tcp_keepalives_interval
tcp_keepalives_count
tcp_keepalives_idle
tcp_keepalives_interval
By default these are 0.
默认情况下，这些值为 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.
我想要的行为是 Postgresql 在一段时间后断开客户端连接，如果客户端失去网络连接或进入睡眠状态。
I am currently using these values:
我目前正在使用这些值：
tcp_keepalives_count = 1
tcp_keepalives_idle = 60
tcp_keepalives_interval = 60
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.
我在 Mac OS X 上运行 PostgreSQL 8.4，但它似乎没有任何效果。我的测试是锁定表中的一行（使用 SELECT FOR UPDATE）并断开工作站与网络的连接。但是在 Postgresql 中，我仍然看到那个工作站持有锁。
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.
我希望在时间过去后（在这种情况下为 60 秒），连接将终止并释放锁。
Either I am doing something wrong or I am completely misunderstanding how this is supposed to work.
要么我做错了什么，要么我完全误解了这应该如何工作。
Any advice?
有什么建议吗？
回答by Tometzky
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
我认为你需要配置你的操作系统。还没有广泛支持通过程序更改 keepalive 参数。这应该对您有所帮助：
使用 TCP keepalive 检测网络错误
Also your parameters are chosen badly. If tcp_keepalives_count=1worked then even one lost keepalive packet will drop your connection. And single packets get lost often. I'd use the following in /etc/sysctl.confon 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.
此外，您的参数选择不当。如果tcp_keepalives_count=1有效，那么即使丢失了一个 keepalive 数据包也会断开您的连接。并且单个数据包经常丢失。我会/etc/sysctl.conf在 MacOSX/FreeBSD 上使用以下内容：
net.inet.tcp.keepidle = 60000
net.inet.tcp.keepintvl = 10000
然后操作系统最多会在 140 秒内断开连接（60 秒空闲 + 8 个 keepalive 数据包在 10秒间隔）后失去连接。
