X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0A6C0D9A2D for ; Mon, 3 Oct 2005 13:47:22 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 65154-06 for ; Mon, 3 Oct 2005 16:47:19 +0000 (GMT) Received: from solanne.parasun.net (solanne.parasun.net [204.174.16.15]) by svr1.postgresql.org (Postfix) with ESMTP id BCDE6D9A20 for ; Mon, 3 Oct 2005 13:47:18 -0300 (ADT) Received: from dynamic-204-95-16-127.sanbrunocable.com ([204.95.16.127] helo=oss4u.com) by solanne.parasun.net with smtp (Exim 4.43) id 1EMTTA-0003h8-VE for pgsql-admin@postgresql.org; Mon, 03 Oct 2005 09:47:17 -0700 Received: (qmail 5720 invoked from network); 3 Oct 2005 16:40:16 -0000 Received: from tiburon.oss4u.com (192.168.1.4) by gw.oss4u.com (192.168.1.1) with ESMTP; 03 Oct 2005 16:40:16 -0000 From: "Uwe C. Schroeder" To: pgsql-admin@postgresql.org Subject: Re: Vacuum Full Analyze Stalled Date: Mon, 3 Oct 2005 09:47:14 -0700 User-Agent: KMail/1.7.1 Cc: "Jeff Kirby" References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200510030947.14676.uwe@oss4u.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.222 required=5 tests=[AWL=0.172, FORGED_RCVD_HELO=0.05] X-Spam-Level: X-Archive-Number: 200510/31 X-Sequence-Number: 19103 On the linux box check if any application left a "idle in transaction" backend around. just ps -ef | grep transaction if that shows any postgres process then you most likely found the culprit. An application that's still running can have a bug that starts a transaction which never finishes for some reason (commit or rollback it never called). I had a couple of those errors in my app - always leading to a stuck vacuum full. If that's the case just kill the backend in question and the vauum will continue. Don't kill -9 it!! After that try to figure out what caused the application not to commit or rollback a transaction. It's usually an error handler of some sort. just an idea. UC On Sunday 02 October 2005 09:42, Jeff Kirby wrote: > I'm going to attempt to do my best in describing the situation... so please > forgive any ignorance on my part since I am very new to the Postgres > community. > > We have 2 identical Postgres databases, one on a Windows 2003 platform, and > the other on a SUSe 9.3 Linux platform (both platforms have identical > hardware). They both have the same schema, the same configuration settings > (as far as I can tell), and relatively the same amount of data (approx > 32GB). OK... here is the scoop, I started a "vacuum full analyze verbose" > on both last night... the windows box completed in about 1 1/2 hours... the > Linux box however is still chugging away this morning... and appears to be > stuck on vacuuming "pg_constraint_contypid_index". How do I know... well I > don't really... I'm inferring based on the order of the log output on the > Windows box. > > So the question is... has anyone experienced this problem in the past? If > so, what do you recommend to rectify the problem? Is it normal to be > running for more than 12 hours on a table that only has 35 rows? Do you > need me to attach the log output thus far from both the Windows and Linux > box? Any help here would be greatly appreciated. Any commands you can > suggest to run on the Linux machine... no problem. > > Jeffrey Kirby > CCAP Web Team > jeff.kirby@wicourts.gov > 608-264-6253 > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax: +1 650 872 2417