Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 43D939FBA39 for ; Wed, 21 Mar 2007 23:46:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 25080-05 for ; Wed, 21 Mar 2007 23:46:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from lists.commandprompt.com (host-254.commandprompt.net [207.173.203.254]) by postgresql.org (Postfix) with ESMTP id 409759FB6C3 for ; Wed, 21 Mar 2007 23:46:22 -0300 (ADT) Received: from [192.168.1.50] (or-67-76-146-141.sta.embarqhsd.net [67.76.146.141]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id l2M2kLTI015547 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Wed, 21 Mar 2007 19:46:24 -0700 Message-ID: <4601EE39.6000503@commandprompt.com> Date: Wed, 21 Mar 2007 19:47:21 -0700 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Thunderbird 1.5.0.9 (X11/20070103) MIME-Version: 1.0 To: "Davidson, Robert" CC: pgsql-admin@postgresql.org Subject: Re: Vacuum taking forever References: <8333C841129E074E9F83FC80676BA76E06191083@exchg-sea3-03.ant.amazon.com> In-Reply-To: <8333C841129E074E9F83FC80676BA76E06191083@exchg-sea3-03.ant.amazon.com> X-Enigmail-Version: 0.94.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV 0.88.5/2865/Sun Mar 18 08:27:48 2007 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]); Wed, 21 Mar 2007 19:46:24 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/157 X-Sequence-Number: 24981 Davidson, Robert wrote: > I am doing a vacuum full on a table that takes up 78 GB of a 79 GB HD. When I couldn't add any more data because of out of disk space errors, I freed up space by dropping other tables in the database. I was able to delete about half the records in the table, but the space consumption did not change. I began a vacuum full on the individual table on 3/15 and the hard drive light hasn't turned off since then. > > > > df -h reports 806 MB free and 79 GB used. Is this enough free space to perform a vacuum? Should I export the table, drop it, and re-import it? Yes a pg_dumpall|psql -U is probably in order here. SO this: pg_dumpall, drop database, create database, restore pg_dumpall. This of course assumes that your 78GB is actually bloat and that the backup file is much smaller. Joshua D. Drake > > > > I have implemented data retention policies to keep the amount of data in the table to a more reasonable level in the future and would like to retain what > is currently in there, but will not be heartbroken to lose it. > > > > Many thanks for any tips or advice provided. > > > > Robert > > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/