Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 903CD9FBF81 for ; Tue, 15 May 2007 20:11:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 91074-04 for ; Tue, 15 May 2007 20:11:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.polimetrix.com (mail.polimetrix.com [209.128.109.230]) by postgresql.org (Postfix) with ESMTP id DFBE59FBF7E for ; Tue, 15 May 2007 20:11:34 -0300 (ADT) Received: from [10.1.1.201] ([10.1.1.201]) by mail.polimetrix.com over TLS secured channel with Microsoft SMTPSVC(6.0.3790.1830); Tue, 15 May 2007 16:11:33 -0700 Message-ID: <464A3E24.6060204@polimetrix.com> Date: Tue, 15 May 2007 16:11:32 -0700 From: Geoff Tolley User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.0.10) Gecko/20070226 CentOS/1.0.8-0.2.el4.centos SeaMonkey/1.0.8 MIME-Version: 1.0 To: Nick Urbanik CC: pgsql-admin@postgresql.org Subject: Re: How overcome wait for vacuum full? References: <20070515221356.GG5264@nicku.org> In-Reply-To: <20070515221356.GG5264@nicku.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 15 May 2007 23:11:33.0435 (UTC) FILETIME=[6101A0B0:01C79746] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/122 X-Sequence-Number: 25442 Nick Urbanik wrote: > 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. If you're waiting on vacuum to get around to the one table, you can run VACUUM FULL instead. Personally I've found that dropping indices (including the PK), then vacuum full, then recreating indices can be an awful lot faster than just leaving vacuum full to its own devices. Yes you ought to be able to reclaim disk space by the copy/drop original/rename procedure, but that gets hard to manage if you have triggers or foreign keys on the table in question. You might want to use CREATE TABLE table_copy (LIKE orig_table) to preserve column defaults if you go down that path. HTH, Geoff