如何在Postgresql中使用tcp_keepalives设置? [英] How to use tcp_keepalives settings in Postgresql?

问题描述

Postgresql具有3个keepalive设置，用于管理断开的连接(在postgresql.conf中):

Postgresql has 3 keepalive settings for managing dropped connections (in postgresql.conf):

tcp_keepalives_count
tcp_keepalives_idle
tcp_keepalives_interval

tcp_keepalives_count
tcp_keepalives_idle
tcp_keepalives_interval

默认情况下，这些值为0.

By default these are 0.

如果客户端丢失网络连接或进入睡眠状态，我希望Postgresql在一段时间后断开客户端连接.

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

tcp_keepalives_count = 1
tcp_keepalives_idle = 60
tcp_keepalives_interval = 60

我在Mac OS X上运行PostgreSQL 8.4，但似乎没有任何效果.我的测试是，我锁定了表中的一行(使用SELECT FOR UPDATE)，并断开了工作站与网络的连接.但是在Postgresql中，我仍然看到该工作站持有该锁.

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.

我希望在时间过去之后(在这种情况下为60秒)，连接将终止并且锁将被释放.

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.

有什么建议吗?

推荐答案

我认为您需要配置操作系统.目前尚不支持通过程序更改keepalive参数.这应该对您有帮助:
使用TCP keepalive检测网络错误

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

您的参数选择也不正确.如果tcp_keepalives_count=1工作正常，那么即使丢失一个keepalive数据包也将断开您的连接.而且单个数据包经常丢失.我将在MacOSX/FreeBSD的/etc/sysctl.conf中使用以下命令:
net.inet.tcp.keepidle = 60000
net.inet.tcp.keepintvl = 10000
然后，操作系统会在失去连接后最多140秒(60秒的空闲时间+ 10个间隔的时间间隔发送8个保持活动的数据包)断开连接.

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.

这篇关于如何在Postgresql中使用tcp_keepalives设置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！