Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 9ECB62E00B5 for ; Fri, 25 Jan 2008 15:23:51 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 13875-04 for ; Fri, 25 Jan 2008 15:23:45 -0400 (AST) Received: from easy2.in-chemnitz.de (easy2.in-chemnitz.de [88.198.17.232]) by postgresql.org (Postfix) with ESMTP id 063952E0085 for ; Fri, 25 Jan 2008 15:23:45 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) by easy2.in-chemnitz.de (Postfix) with ESMTP id B8A27B2726 for ; Fri, 25 Jan 2008 20:23:42 +0100 (CET) Received: from easy2.in-chemnitz.de ([127.0.0.1]) by localhost (easy.in-chemnitz.de [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id bXMyE5e89C4X for ; Fri, 25 Jan 2008 20:23:42 +0100 (CET) Received: by easy2.in-chemnitz.de (Postfix, from userid 1013) id A1365B2532; Fri, 25 Jan 2008 20:23:42 +0100 (CET) Date: Fri, 25 Jan 2008 20:23:42 +0100 From: Tino Schwarze To: pgsql-admin@postgresql.org Subject: Re: Recovering a database in danger of transaction wrap-around Message-ID: <20080125192342.GA15475@easy2.in-chemnitz.de> References: <3757.1201288206@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <3757.1201288206@sss.pgh.pa.us> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/248 X-Sequence-Number: 28022 On Fri, Jan 25, 2008 at 02:10:06PM -0500, Tom Lane wrote: > > I used lsof to monitor which files the backend was actually working on. It > > took two of the four days for it to vacuum a single table with 43 > > one-gigabyte extents. I have one table with over 300 extents. I'm looking > > at a vacuum process which can ultimately take weeks (if not months) to > > complete. > > Yipes. You are just using plain VACUUM, right, not VACUUM FULL? > Have you checked that vacuum_cost_delay isn't enabled? pg_dump/pg_restore may be a lot faster here - we're in an emergency situation anyway and after that, the whole DB will be clean again, all indices rebuilt nicely, no bloat in the tables. Tino. -- www.craniosacralzentrum.de www.spiritualdesign-chemnitz.de Tino Schwarze * Lortzingstraße 21 * 09119 Chemnitz