Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have one table that is taking up close to 90% of hd space on our server. I have decided to drop a few columns to free up space. But I need to return the space to the OS. The problem, though, is that I'm not sure what will happen if I run VACUUM FULL and there is not enough free space to make a copy of the table.

I understand that VACUUM FULL should not be used but I figured it was the best option in this scenario.

Any ideas would be appreciated.

Oh, and I'm using PostgreSQL 9.0.6

share|improve this question
    
You will need as much space as is required to copy the contents of the table, plus additional overhead of a certain percentage. If you don't have this space, the VACUUM transaction should roll back. So short answer: don't do it unless you have the space. –  user1240 Apr 27 '12 at 4:37

1 Answer 1

up vote 4 down vote accepted

Since you don't have enough space to run a vacumm or rebuild, you can always rebuild your postgresql databases by restoring them. Restoring the databases, tables, indexes will free up space and defragment. Afterwards, you can setup automated maintenance to vacumm your databases on a regular basis.

1 Backup all of the databases on your postgresql server

You will want to backup all of your databases to a partition that has enough space. If you were on Linux, you can use gzip to further compress the backup to save space

su - postgres
pg_dumpall | gzip -9 > /some/partition/all.dbs.out.gz

2 Backup your configuration files

cp /path/to/postgresql/data_directory/*.conf /some/partition/

3 Stop Postgresql

pg_ctl -D /path/to/postgresql/data_directory stop

4 erase the contents of the data directory

rm -Rf /path/to/postgresql/data_directory/*

5 Run initdb to reinitalize your data directory

initdb -D /path/to/postgresql/data_directory

6 Restore configuration files

cp /some/partition/*.conf /path/to/postgresql/data_directory/*.conf 

7 Start Postgresql

pg_ctl -D /path/to/postgresql/data_directory start

8 Restore the dump of all the databases you made

gunzip /some/partition/all.dbs.out.gz
psql -f /some/partition/all.dbs.out
share|improve this answer
1  
Thanks, this is what I ended up doing, with a couple of differences. I just dropped the database after backing it up. Then created a new one and restored it. –  Justin Apr 30 '12 at 15:51
    
Your welcome. I figured that removing the contents of the data directory and doing initdb would have been sufficent though. –  Craig Efrein Apr 30 '12 at 15:56

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.