Thread: Vacuum taking forever

Vacuum taking forever

From
"Davidson, Robert"
Date:

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?

 

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

Re: Vacuum taking forever

From
"Joshua D. Drake"
Date:
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
outof disk space errors, I freed up space by dropping other tables in the database. I was able to delete about half the
recordsin the table, but the space consumption did not change. I began a vacuum full on the individual table on 3/15
andthe 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,
dropit, 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
futureand 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/


Re: Vacuum taking forever

From
Phillip Smith
Date:
What are the specs of your machine? What is the memory settings in your postgresql.conf file?

Cheers
~p

On Wed, 2007-03-21 at 19:42 -0700, Davidson, Robert wrote:
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?
Attachment