Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have one database in linux server which has huge number of data's. Due to the updation and deletion operation it has consumed most of the from my hard disk. The main reason is that the version which we are using ( 8.1 ) does not have periodical autovaccum process. To over come this issue i have put the vaccum full to specific table, the vaccum command was running more than 15 hours and keeps on running. So I just stopped the vaccum execution since other process was not able to access that table for 15 hours.

Is there any way to overcome this issue and get back the memory without disturbing other process.

Thanks

share|improve this question

migrated from stackoverflow.com Nov 16 '11 at 10:08

This question came from our site for professional and enthusiast programmers.

1  
I hope you are aware that support for version 8.1 ended November 2010 –  Mike T Sep 21 '11 at 7:35

3 Answers 3

up vote 2 down vote accepted

Version 8.1 is End Of Life as of November 2010. You need an upgrade to a newer version, 8.3 or later. Version 8.2 will be EOL within a few months as well.

VACUUM FULL needs a table lock, did it get the lock or was it just waiting for 15 hours to get it? VACUUM FULL is the only way to reclaim diskspace.

Version 8.1 does have auto_vacuum, it was introduced in this version:

Beginning in PostgreSQL 8.1, there is a separate optional server process called the autovacuum daemon, whose purpose is to automate the execution of VACUUM and ANALYZE commands.

Turn it on, or do a manual VACUUM (without FULL) on a time interval.

share|improve this answer
    
yes, I want to upgrade the database to new version. before that how can i get rid of this issue...any other work around is there ..? –  Pavunkumar Sep 21 '11 at 7:50
    
Stop all processes on that table on a quiet moment and do a VACUUM FULL. Afterwards, you need a REINDEX for this table as well, VACUUM FULL might inflate your indexes. VACUUM FULL will always disturb other processes, that's why you need auto_vacuum to prevent the need for a VACUUM FULL. –  Frank Heikens Sep 21 '11 at 7:55
    
In that case you might consider CLUSTER instead, followed by ANALYZE. Optimizes best and all indexes as well. You could chose an index for that which sorts data according to typical queries - another benefit on the side. –  Erwin Brandstetter Sep 21 '11 at 18:30

You can give CLUSTER (doc here) a try - it will rewrite and compact a complete table. While VACUUM FULL does this in place, CLUSTER does its job by writing into a second file and switches the files after the jobs.

This has some advantages and some disadvantages:

  • It is much faster than VACUUM FULL,
  • it needs more storage while running (second table file),
  • it requires an EXCLUSIVE lock on the table.

You might start with several smaller tables to get a feeling for it.

And please lookup the PostgreSQL mailinglists for more details.

share|improve this answer

If the VACUUM debt is really that huge, restoring from a pg_dump backup would also be an option. And that comes tentatively close to upgrading to 8.4 or higher.

share|improve this answer
    
If you're upgrading anyway, then dump restore is the way to go. Note that you can pg_dump -t tablename just the one table, truncate it an restore it, or do something like select * into holding_table from bloated_table;truncate bloated_table;insert into bloated_table select * from holding_table; and things like that. –  Scott Marlowe Sep 22 '11 at 22:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.