1

I lately ran out of disk space. I deleted some old data-rows from the CMS database. I have noticed that the free disk space did not grow as expected. I searched in serverfault and found that VACUUM FULL; command.

Unfortunately I am unable to run this command because of full disk.

This is my log:

postgres@Ubuntu-1510-wily-64-minimal:~$ psql -d cms
psql (9.4.6)
Type "help" for help.

cms=# auto vacuum
cms-# ;
ERROR:  syntax error at or near "auto"
LINE 1: auto vacuum
        ^
cms=# VACUUM FULL;
ERROR:  could not extend file "base/80180/114991.6": No space left on device
HINT:  Check free disk space.
cms=# \q
postgres@Ubuntu-1510-wily-64-minimal:~$ df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            985M     0  985M   0% /dev
tmpfs           201M   26M  175M  13% /run
/dev/sda1        47G   39G  5.8G  87% /
tmpfs          1001M  8.0K 1001M   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs          1001M     0 1001M   0% /sys/fs/cgroup
tmpfs           201M     0  201M   0% /run/user/0
postgres@Ubuntu-1510-wily-64-minimal:~$ psql -d cms
psql (9.4.6)
Type "help" for help.

cms=# VACUUM FULL;
ERROR:  could not extend file "base/80180/115638.5": No space left on device
HINT:  Check free disk space.
cms=# \q
postgres@Ubuntu-1510-wily-64-minimal:~$ df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            985M     0  985M   0% /dev
tmpfs           201M   26M  175M  13% /run
/dev/sda1        47G   39G  5.7G  88% /
tmpfs          1001M  8.0K 1001M   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs          1001M     0 1001M   0% /sys/fs/cgroup
tmpfs           201M     0  201M   0% /run/user/0
postgres@Ubuntu-1510-wily-64-minimal:~$ psql -d cms
psql (9.4.6)
Type "help" for help.

cms=# VACUUM FULL;
ERROR:  could not extend file "base/80180/115660.5": No space left on device
HINT:  Check free disk space.
cms=#

I am confused now. I have 5.7 Gig free and I am unable to free more because the disk is full.

Any idea?

Grim
  • 135
  • 7

1 Answers1

1

The operating system reserves some portion of the disk space for defragmenting and admin use, as postgresql does not run as root it does nt have access to the last 5% 9or whatever)

to fix this you'll need to free up some space, perhaps you can move some unrelated files off the system temporatily, (eg log files)

dropping all the indexes (and foreign key constraints) may free up enough disk. then re-build the indexes (pg_dump --schmea-only will among other things give you all the index definitions so you have something to restore)

backing up the large table to external media and then truncating it and re-loading it would also work have the same effect as vacuum full.

Jasen
  • 621
  • 5
  • 12