X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 87F779DCA13 for ; Sat, 28 Jan 2006 12:00:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99980-01 for ; Sat, 28 Jan 2006 12:00:47 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 5281C9DC834 for ; Sat, 28 Jan 2006 12:00:45 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k0SG0f5k011869; Sat, 28 Jan 2006 11:00:41 -0500 (EST) To: Juan Miguel cc: pgsql-admin@postgresql.org Subject: Re: Postgresql, number of connections and life time In-reply-to: <43DB2942.3040009@ono.com> References: <43DB2942.3040009@ono.com> Comments: In-reply-to Juan Miguel message dated "Sat, 28 Jan 2006 09:20:18 +0100" Date: Sat, 28 Jan 2006 11:00:41 -0500 Message-ID: <11868.1138464041@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.1 required=5 tests=[AWL=0.100] X-Spam-Score: 0.1 X-Spam-Level: X-Archive-Number: 200601/311 X-Sequence-Number: 20399 Juan Miguel writes: > I have a Linux box with postgres 7.4 branche, where web php applications > queries the DB. When Apache launches a php file that connects to the DB, > if I do "ps aux | grep postgrest", I can see a process "postgres user > ...." running, that represents the session openned to the DB. But when > the php program finishes, and closes the connection, this process does > not end. Therefore the number of processes are growing while people > visit the web. ¿ Why ? Evidently the connection isn't really getting closed. I don't know enough about PHP to help you with why not, though. > I gain access this DB remotely by an encrypted connection with stunnel. > Well, when I launch a connection to a DB, and passes a long time without > traffic, I realise the connection is closed. The program that queries > the DB does not realise the connection is broken and when it tries to > query, it fails. I think that the origin of the problem is not stunnel, > because the same problem presents to me when trying with a testing DB > without stunnel. ¿ Why ? Has postgres a timer that breaks the > connections when there is no traffic ? Has Linux this timmer ? No, and no, but a lot of router and firewall hardware has idle-timeouts for connections. Look at the settings for whatever boxes are between your two machines --- if you're lucky, you can increase the idle timeout. More recent versions of PG (I think 8.1 and up) allow you to fool with the TCP keepalive settings --- reducing those can be used to generate dummy traffic that will keep the firewall from thinking the connection is idle. regards, tom lane