Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C74769FBF62 for ; Tue, 15 May 2007 19:53:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 44283-10 for ; Tue, 15 May 2007 19:53:18 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 101979FBBD1 for ; Tue, 15 May 2007 19:53:21 -0300 (ADT) Received: from perhan.alvh.no-ip.org (201-221-217-243.bk12-dsl.surnet.cl [201.221.217.243]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l4FMrJLC026530; Tue, 15 May 2007 15:53:20 -0700 Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id DD1DF32A13; Tue, 15 May 2007 18:53:13 -0400 (CLT) Date: Tue, 15 May 2007 18:53:13 -0400 From: Alvaro Herrera To: Nick Urbanik Cc: pgsql-admin@postgresql.org Subject: Re: How overcome wait for vacuum full? Message-ID: <20070515225313.GV12731@alvh.no-ip.org> References: <20070515221356.GG5264@nicku.org> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20070515221356.GG5264@nicku.org> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: ClamAV version 0.88.5, clamav-milter version 0.88.5 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Tue, 15 May 2007 15:53:20 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/120 X-Sequence-Number: 25440 Nick Urbanik wrote: > Dear Folks, > > I am running a full vacuum on a database. It's taking longer than I > hoped. In particular, the vacuum still hasn't reached the table that > will benefit most from the vacuum. > > Can I move the existing table to a backup, make a copy of the table > back to its original name, restart the application, and run the vacuum > on the backup? Or can I reclaim the disk space by dropping the > original after making a copy? > > If I can, without losing data, what is the best way to do that? > I am reaching the end of the period when this database application can > be disabled. > > I have never run vacuum full on such a large database (24G) and am > desperate to get the application back online. Instead of waiting a month for the time when you can take the application offline (thus accumulating a month's worth of dead tuples), run a non-full vacuum more often (say, once a day or more). It doesn't lock the table so the app can continue to be online while it runs. If you have too many dead tuples and are desperate to get the table in a reasonable non-bloated state, try CLUSTER instead of VACUUM FULL. It might finish faster. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support