From: Joshua D. Drake <jd@commandprompt.com>
To: Davidson, Robert <robdavid@amazon.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Vacuum taking forever
Date: Wed, 21 Mar 2007 19:47:21 -0700
Message-ID: <4601EE39.6000503@commandprompt.com> (raw)
In-Reply-To: <8333C841129E074E9F83FC80676BA76E06191083@exchg-sea3-03.ant.amazon.com>
References: <8333C841129E074E9F83FC80676BA76E06191083@exchg-sea3-03.ant.amazon.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jd@commandprompt.com, robdavid@amazon.com
Subject: Re: Vacuum taking forever
In-Reply-To: <4601EE39.6000503@commandprompt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox