27

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

19

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
  • 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
  • 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
  • Worked great, I just recommend skipping the gzip part to save time. – Rafael Barbosa Nov 4 '16 at 13:50
17

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


CAUTION (As noted in the comments by @erny):

Note that high CPU load due to I/O operations may be expected.

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. As Stéphane mentions in the comments, this needs to be at least twice as big as the table in question as VACUUM FULL does a full copy. 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:

\db

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:

ALTER TABLE mytable SET TABLESPACE tempspace;
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:

DROP TABLESPACE tempspace;
  • 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 Jul 27 '16 at 6:10
  • 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 Jul 29 '16 at 19:06
  • 2
    Amazing tips, thanks for this detailed explaination. Note that on the temporary tablespace you'll need at least size of table x 2, since VACUUM FULL is making a full copy of the table. – Stéphane Mar 8 '18 at 14:27
  • Thanks @Stéphane. I added the information to the main body. – exhuma Mar 9 '18 at 8:40
2

Quick and dirty:

  • Stop Postgres
  • Move the main database directory to another disk where there's enough room for vacuuming
  • In the original location of main, add a symlink to the new location
  • Vacuum
  • Delete the symlink and move the main directory back to its original location
  • Start Postgres

E.g.,:

$ service postgresql stop $ mv /var/lib/postgresql/9.5/main /mnt/bigdisk $ ln -sr /mnt/bigdisk/main /var/lib/postgresql/9.5 $ vacuumdb --all --full $ rm /var/lib/postgresql/9.5/main $ mv /mnt/bigdisk/main /var/lib/postgresql/9.5 $ service postgresql start

0

If you have the disk space to do a dump and restore, you should have the disk space to do a vacuumdb --full. The problem is that vacuumdb --full will make a copy of the entire data file. So, what you could do is:

  1. copy the files that hold the huge table to a different drive, for example, a slower, bigger drive.
  2. make symbolic links from the original location to the new place on the other drive.
  3. run vacuumdb --full, now it should read the data from the other disk, and write the final table to your original data disk.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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