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 9392A11F68E1 for ; Thu, 30 Mar 2006 12:41:32 -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 13174-01 for ; Thu, 30 Mar 2006 12:41:30 -0400 (AST) X-Greylist: delayed 00:40:45.677035 by SQLgrey- Received: from mail.worldcycling.com (mail.worldcycling.com [209.240.82.37]) by postgresql.org (Postfix) with SMTP id 0903C11F68E2 for ; Thu, 30 Mar 2006 12:41:29 -0400 (AST) Received: from intranet.worldcycling.com ([209.240.82.46]) by mail.worldcycling.com (JAMES SMTP Server 2.2.0) with SMTP ID 243 for ; Thu, 30 Mar 2006 10:41:28 -0600 (CST) Received: from steve.worldcycling.com (localhost.localdomain [127.0.0.1]) by steve.worldcycling.com (8.12.11.20060308/8.12.11) with ESMTP id k2UGfSSZ030618 for ; Thu, 30 Mar 2006 10:41:28 -0600 Received: (from linabery@localhost) by steve.worldcycling.com (8.12.11.20060308/8.12.11/Submit) id k2UGfSHV030617 for pgsql-admin@postgresql.org; Thu, 30 Mar 2006 10:41:28 -0600 X-Authentication-Warning: steve.worldcycling.com: linabery set sender to slinabery@worldcycling.com using -f Date: Thu, 30 Mar 2006 10:41:28 -0600 From: Steve Linabery To: pgsql-admin@postgresql.org Subject: Re: can't get connection during pg_dump Message-ID: <682391.1143736889382.JavaMail.root@mail.worldcycling.com> References: <26980667.1143734442919.JavaMail.root@mail.worldcycling.com> <523929.1143735343754.JavaMail.root@mail.worldcycling.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <523929.1143735343754.JavaMail.root@mail.worldcycling.com> User-Agent: Mutt/1.5.6i Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.08 required=5 tests=[AWL=0.080] X-Spam-Score: 0.08 X-Spam-Level: X-Archive-Number: 200603/378 X-Sequence-Number: 21135 On Thu, Mar 30, 2006 at 11:14:37AM -0500, Tom Lane wrote: > Steve Linabery writes: > > Last night during backup, I noticed that I could not get a connection, either remotely via jdbc or locally via psql. Well perhaps that is inaccurate: 'ps -ef' showed several processes with a status message of "waiting to startup" (or something similar; sorry, I was late-night coding and didn't think to write it down). > > Hm, I don't recall any such status message in the code. Can you double > check what it said exactly? I just replicated the problem, see below for explanation, but here is ps output: postgres 3201 1 0 Mar13 ? 01:58:14 /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data postgres 3270 3201 0 Mar13 ? 00:04:11 postgres: writer process postgres 3271 3201 0 Mar13 ? 00:11:28 postgres: stats buffer process postgres 3272 3271 0 Mar13 ? 00:07:11 postgres: stats collector process postgres 3360 3201 0 Mar29 ? 00:00:00 postgres: dbusername other_db_name obfus.ip.address.36(53524) idle postgres 3363 3201 0 Mar29 ? 00:00:00 postgres: dbusername other_db_name obfus.ip.address.36(53536) idle postgres 3364 3201 0 Mar29 ? 00:00:00 postgres: dbusername other_db_name obfus.ip.address.36(53537) idle root 16467 16447 0 09:59 pts/0 00:00:00 su postgres postgres 16468 16467 0 09:59 pts/0 00:00:00 bash postgres 25817 3201 55 10:20 ? 00:02:54 postgres: postgres db_being_dumped [local] COPY postgres 27956 3201 46 10:24 ? 00:00:36 postgres: postgres db_being_dumped [local] COPY postgres 28124 3201 0 10:25 ? 00:00:00 postgres: dbusername template1 obfus.ip.address.36(49528) DROP DATABASE waiting postgres 28180 3201 0 10:25 ? 00:00:00 postgres: dbusername db_being_dumped obfus.ip.address.37(48997) startup waiting postgres 28183 3201 0 10:25 ? 00:00:00 postgres: dbusername db_being_dumped obfus.ip.address.36(49532) startup waiting > > What do you mean by "could not get a connection" ... did it fail (if so, > with what client-side error message) or just hang up waiting? > All connection attempts just hung. No error message on the client side. Here's what I did to replicate the problem: 0) first tried running pg_dump and there were no problems with connecting (from various web applications, mail server, etc) 1) repeated what I did last night: am developing a java webapp with OJB, which uses torque & xdoclet to generate SQL for the various classes in the project. Part of the torque xdoclet task DROPS the database and recreates it (only while you're in development mode...still have to figure out how to turn that off!). 2) postgresql hung once I ran the ant task and torque was trying to drop the database, as you can see from the ps output above. So, what I'm gathering is that dropping a database counts as one of the operations that will hang, along with 'VACUUM FULL', if pg_dump is running. Correct? I ran across a reference to 'VACUUM FULL' in the docs w.r.t. pg_dump in the context of troubleshooting this problem last night, but can't find it now. Thanks! -- Steve Linabery, sysadmin/developer B94B C3C7 8A27 FF09 3C9D E992 5A20 2492 D5F5 EE51 This electronic message transmission contains information from the sender's organization that may be proprietary, confidential and/or privileged. The information is intended only for the use of the individual(s) or entity named above. If you are not the intended recipient, be aware that any disclosure, copying or distribution or use of the contents of this information is prohibited. If you have received this electronic transmission in error, please notify the sender immediately by replying to the address listed in the "From:"