Get back the Disk space from Postgresql

Question

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

Solution 1

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.

Solution 2

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.

Solution 3

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.

Comments

[Mike T]

I hope you are aware that support for version 8.1 ended November 2010

[Pavunkumar]

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 ..?

[Erwin Brandstetter]

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.

[Admin]

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.

[redgeoff]

Another option is to create another table, copy over the contents, delete the original table and then rename the new table to the old name.