I need to run VACUUM FULL with no available disk space

Question

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

Answer 1

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 -c /some/partition/all.dbs.out.gz | psql

Answer 2

Method with little to no downtime (minimal user-impact)

---

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;

Answer 3

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
• Start Postgres
• Vacuum
• Stop Postgres
• 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
$ service postgresql start
$ vacuumdb --all --full
$ service postgresql stop
$ rm /var/lib/postgresql/9.5/main
$ mv /mnt/bigdisk/main /var/lib/postgresql/9.5
$ service postgresql start

Answer 4

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.

Answer 5

After using exhuma's answer above to move the tablespace of many tables to a different disk, the following can be helpful to:

• Vacuum all tables, one at a time.
• For all moved tables, move their tablespace back to the default pg_default.

Run vacuumdb on all tables, one at a time

db=my_database
psql -U postgres -d $db -tA -c "SELECT table_name
  FROM information_schema.tables WHERE table_schema='public'" \
| while read -r tbl; do
    echo "==Table $tbl"
    vacuumdb -U postgres -d $db -t "\"$tbl\"" --full --verbose 2>&1 
  done \
| tee -a vacuum-$db.log

Save commands to move back all moved tables

This finds all tables which use a tablespace named "tempspace", and writes to a file the SQL commands to move them back to pg_default

psql -U postgres -d $db -tA -c "SELECT tablename from pg_tables WHERE tablespace='tempspace';" \
| while read -r tbl; do
    echo "ALTER TABLE \"$tbl\" SET tablespace pg_default;"
  done \
>my_pg_restore_tblspc.sql

Finally, run the SQL commands in the file

psql -U postgres -d "$db" -a -f my_pg_restore_tblspc.sql