pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Vacuum taking forever
3+ messages / 3 participants
[nested] [flat]

* Vacuum taking forever
@ 2007-03-22 02:42 Davidson, Robert <robdavid@amazon.com>
  2007-03-22 02:47 ` Re: Vacuum taking forever Joshua D. Drake <jd@commandprompt.com>
  2007-03-22 02:48 ` Re: Vacuum taking forever Phillip Smith <phillip.smith@weatherbeeta.com.au>
  0 siblings, 2 replies; 3+ messages in thread

From: Davidson, Robert @ 2007-03-22 02:42 UTC (permalink / raw)
  To: pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Vacuum taking forever
  2007-03-22 02:42 Vacuum taking forever Davidson, Robert <robdavid@amazon.com>
@ 2007-03-22 02:47 ` Joshua D. Drake <jd@commandprompt.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Joshua D. Drake @ 2007-03-22 02:47 UTC (permalink / raw)
  To: Davidson, Robert <robdavid@amazon.com>; +Cc: pgsql-admin

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/




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Vacuum taking forever
  2007-03-22 02:42 Vacuum taking forever Davidson, Robert <robdavid@amazon.com>
@ 2007-03-22 02:48 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
  1 sibling, 0 replies; 3+ messages in thread

From: Phillip Smith @ 2007-03-22 02:48 UTC (permalink / raw)
  To: Davidson, Robert <robdavid@amazon.com>; +Cc: pgsql-admin

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?

Attachments:

  smime.p7s (application/x-pkcs7-signature, 2.1K) download

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2007-03-22 02:48 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-03-22 02:42 Vacuum taking forever Davidson, Robert <robdavid@amazon.com>
2007-03-22 02:47 ` Joshua D. Drake <jd@commandprompt.com>
2007-03-22 02:48 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>

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