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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

I'm using PostgreSQL 9.0.6

share|improve this question
up vote 8 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
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
You're welcome. I figured that removing the contents of the data directory and doing initdb would have been sufficient though. – Craig Efrein Apr 30 '12 at 15:56

NOTE: I have tested this on 9.1. I have no 9.0 server lying around here. I am preeeeeeeeettty sure though it will work on 9.0 though.

You can do this with pretty much no down-time by using a temporary tablespace. The down-time will be in the form of exclusive locks. But only on the table you are vacuuming. So all that will happen is that client queries will simply wait for the lock to be acquired if they access the table in question. You don't need to close existing connections.

One thing to be aware of though, is that moving the table and the vacuum full will themselves need to wait for an exclusive lock first!

First, you obviously need some additional storage. If you are lucky and can dynamically add a disk to the machine, do that. In the worst case you can just attach an USB disk (risky and slow though)!

Next, mount the new device and make it available as tablespace:

CREATE TABLESPACE tempspace LOCATION '/path/to/new/folder';

You can list the tablespaces easily using:


Double-check the current tablespace of your table (you need to know where to move it back to):

SELECT tablespace FROM pg_tables WHERE tablename = 'mytable';

If it's NULL, it will be in the default tablespace:

SHOW default_tablespace;

If that is NULL as well, it will likely be pg_default (check the official docs in case it's changed).

Now move the table over:

COMMIT;  -- if autocommit is off

Vacuum it:

VACUUM FULL mytable;

Move it back:

-- assuming you are using the defaults, the tablespace will be "pg_default".
-- Otherwise use the value from the SELECT we did earlier.
ALTER TABLE mytable SET TABLESPACE pg_default;
COMMIT;  -- if autocommit is off

Remove the temporary space:

share|improve this answer
NB: the move appears to use more disk space in the original data directory... – Chris Withers Nov 24 '15 at 11:13
Just tested it on 9.3 and it works like charm. – Bartek Jablonski 2 days ago
Successfully used in production on 9.1. After changing tablespace the original used space is released. Note that high CPU load due to I/O operations may be expected. – erny 2 hours ago

Your Answer


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.