On Sun, January 13, 2008 7:25 pm, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>> On Sun, Jan 13, 2008 at 08:40:34AM +0200, henry wrote:
>>> lsof doesn't tell me what's talking to PG through /tmp/.s.PGSQL.5432
>>> either. Maybe I'm not understanding exactly how /tmp/.s.PGSQL.5432 is
>>> used - what would connect to PG via a domain socket?
>
>> Connecting to unix domain socket happens if you don't specify a host.
>
> Specifically, a local connection goes through the socket file by
> default, and via TCP only if you say "-h localhost" or equivalent.
>
> Now that we know the problem connections are local ones, setting a TCP
> timeout would be 100% useless anyway. As Martijn says, the other ends
> of the socket connections *must* be on that box someplace --- I'm
> inclined to think that lsof didn't show them to you because you didn't
> run it as root and they belong to some other userid.
Yes, it was run as root, but all lsof tells you is that postgres has that
socket open - not the name/id of the other end of the connection.
A genuine thanks to Tom and Martijn for your helpful suggestions. It's
exactly what I needed to identify what was going on.
The problem was the following (to ensure googlability):
To achieve DB replication from a master to quote a few slaves, in multiple
clusters, we're using PostgreSQL, Slony and PgPool. By default, PgPool is
configured to connect to a local domain socket (backend_host_name = '').
This is fine, but it's also configured by default to never time out when
idle (connection_life_time = 0)...
Changing those two (localhost and [eg] 300) has resolved my problem of
ever-increasing /tmp/.s.PGSQL.5432 domain socket connections.
Once again, thanks. Now, back to the lounge to catch late-night Sunday TV.
Regards
Henry