Sunday, January 19, 2014

I need to run VACUUM FULL with no available disk space

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

No comments:

Post a Comment