1

I recently run out of disc space no my server due to implementing some audit tools on Postgresql database. Two tables holding data about changes made by users quickly reached 16GB each, and totally filled 40GB server. I've checked size of this tables by command in PGAdmin4:

SELECT
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

Following that, I deleted these two tables to save space, but despite that I haven't actually recovered any disc space. I run df and du commands in debian to check disc usage:

root@vps673587:/home/maciejzak# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda1        40G   36G  2.4G  94% /
udev             10M     0   10M   0% /dev
tmpfs           774M   81M  694M  11% /run
tmpfs           1.9G  8.0K  1.9G   1% /dev/shm
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           1.9G     0  1.9G   0% /sys/fs/cgroup
tmpfs           387M     0  387M   0% /run/user/1003
tmpfs           387M     0  387M   0% /run/user/1001


root@vps673587:/home/maciejzak# sudo du -sh /*
8.9M    /bin
72M     /boot
8.0K    /dev
7.0M    /etc
92M     /home
0       /initrd.img
0       /initrd.img.old
537M    /lib
4.0K    /lib64
16K     /lost+found
8.0K    /media
4.0K    /mnt
4.0K    /opt
du: cannot access ‘/proc/10127/task/10127/fd/4’: No such file or directory
du: cannot access ‘/proc/10127/task/10127/fdinfo/4’: No such file or directory
du: cannot access ‘/proc/10127/fd/4’: No such file or directory
du: cannot access ‘/proc/10127/fdinfo/4’: No such file or directory
0       /proc
124K    /root
81M     /run
4.9M    /sbin
12K     /srv
0       /sys
44K     /tmp
968M    /usr
3.6G    /var
0       /vmlinuz
0       /vmlinuz.old

So as you can see, 94% (36GB) on sda1 is used. Meanwhile only 3,6 GB is actually used on /var. What happened with rest of that disc space? I've run lsof command:

root@vps673587:/home/maciejzak#  lsof | grep -i deleted
sudo       2529            root    0u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
sudo       2529            root    1u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
sudo       2529            root    2u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
proftpd    2530            root    0u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
proftpd    2530            root    1u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
proftpd    2530            root    2u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
proftpd    2530            root   10r      REG                8,1       5262      29171 /etc/init.d/proftpd (deleted)
systemctl  2540            root    0u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
systemctl  2540            root    1u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
systemctl  2540            root    2u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
systemd-t  2541            root    0u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
systemd-t  2541            root    1u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
systemd-t  2541            root    2u      CHR              136,0        0t0          3 /dev/pts/0 (deleted)
postgres   8927        postgres   19u      REG                8,1      24576     262826 /var/lib/postgresql/10/main/base/18036/68886_fsm (deleted)
postgres   8927        postgres   58u      REG                8,1          0     263332 /var/lib/postgresql/10/main/base/18036/68886 (deleted)
postgres   8927        postgres   59u      REG                8,1       8192     263850 /var/lib/postgresql/10/main/base/18036/68886_vm (deleted)
postgres   8927        postgres   60u      REG                8,1          0     263783 /var/lib/postgresql/10/main/base/18036/68894 (deleted)
postgres   8927        postgres   61u      REG                8,1          0     263782 /var/lib/postgresql/10/main/base/18036/68893 (deleted)
postgres   8927        postgres   62u      REG                8,1    4251648     263049 /var/lib/postgresql/10/main/base/18036/68889_fsm (deleted)
postgres   8927        postgres   64u      REG                8,1    4251648     263320 /var/lib/postgresql/10/main/base/18036/69116_fsm (deleted)
postgres   8927        postgres   65u      REG                8,1      24576     262943 /var/lib/postgresql/10/main/base/18036/69110_fsm (deleted)
postgres   8927        postgres   66u      REG                8,1          0     262940 /var/lib/postgresql/10/main/base/18036/69110 (deleted)
postgres   8927        postgres   67u      REG                8,1       8192     263851 /var/lib/postgresql/10/main/base/18036/69110_vm (deleted)
postgres   8927        postgres   68u      REG                8,1          0     263336 /var/lib/postgresql/10/main/base/18036/69119 (deleted)
postgres   8927        postgres   69u      REG                8,1          0     263341 /var/lib/postgresql/10/main/base/18036/69120 (deleted)
postgres   8927        postgres   70u      REG                8,1   16777216     263791 /var/lib/postgresql/10/main/pg_wal/000000010000000B0000003A (deleted)
postgres   8927        postgres   72u      REG                8,1          0     263759 /var/lib/postgresql/10/main/base/18036/68891 (deleted)
postgres   8927        postgres   73u      REG                8,1          0     263781 /var/lib/postgresql/10/main/base/18036/68892 (deleted)
postgres   8927        postgres   74u      REG                8,1          0     263340 /var/lib/postgresql/10/main/base/18036/68889 (deleted)
postgres   8927        postgres   75u      REG                8,1 1073741824     263822 /var/lib/postgresql/10/main/base/18036/68889.1 (deleted)
postgres   8927        postgres   76u      REG                8,1 1073741824     263824 /var/lib/postgresql/10/main/base/18036/68889.2 (deleted)
postgres   8927        postgres   77u      REG                8,1 1073741824     263826 /var/lib/postgresql/10/main/base/18036/68889.3 (deleted)
postgres   8927        postgres   78u      REG                8,1 1073741824     263828 /var/lib/postgresql/10/main/base/18036/68889.4 (deleted)
postgres   8927        postgres   79u      REG                8,1 1073741824     263831 /var/lib/postgresql/10/main/base/18036/68889.5 (deleted)
postgres   8927        postgres   80u      REG                8,1 1073741824     263834 /var/lib/postgresql/10/main/base/18036/68889.6 (deleted)
postgres   8927        postgres   81u      REG                8,1 1073741824     263838 /var/lib/postgresql/10/main/base/18036/68889.7 (deleted)
postgres   8927        postgres   82u      REG                8,1 1073741824     263841 /var/lib/postgresql/10/main/base/18036/68889.8 (deleted)
postgres   8927        postgres   83u      REG                8,1 1073741824     263844 /var/lib/postgresql/10/main/base/18036/68889.9 (deleted)
postgres   8927        postgres   84u      REG                8,1 1073741824     263847 /var/lib/postgresql/10/main/base/18036/68889.10 (deleted)
postgres   8927        postgres   85u      REG                8,1          0     263308 /var/lib/postgresql/10/main/base/18036/69118 (deleted)
postgres   8927        postgres   86u      REG                8,1          0     263085 /var/lib/postgresql/10/main/base/18036/68896 (deleted)
postgres   8927        postgres   87u      REG                8,1          0     263305 /var/lib/postgresql/10/main/base/18036/69116 (deleted)
postgres   8927        postgres   88u      REG                8,1 1073741824     263823 /var/lib/postgresql/10/main/base/18036/69116.1 (deleted)
postgres   8927        postgres   89u      REG                8,1 1073741824     263825 /var/lib/postgresql/10/main/base/18036/69116.2 (deleted)
postgres   8927        postgres   90u      REG                8,1 1073741824     263827 /var/lib/postgresql/10/main/base/18036/69116.3 (deleted)
postgres   8927        postgres   91u      REG                8,1 1073741824     263829 /var/lib/postgresql/10/main/base/18036/69116.4 (deleted)
postgres   8927        postgres   92u      REG                8,1 1073741824     263832 /var/lib/postgresql/10/main/base/18036/69116.5 (deleted)
postgres   8927        postgres   93u      REG                8,1 1073741824     263835 /var/lib/postgresql/10/main/base/18036/69116.6 (deleted)
postgres   8927        postgres   94u      REG                8,1 1073741824     263839 /var/lib/postgresql/10/main/base/18036/69116.7 (deleted)
postgres   8927        postgres   95u      REG                8,1 1073741824     263842 /var/lib/postgresql/10/main/base/18036/69116.8 (deleted)
postgres   8927        postgres   96u      REG                8,1 1073741824     263845 /var/lib/postgresql/10/main/base/18036/69116.9 (deleted)
postgres   8927        postgres   97u      REG                8,1 1073741824     263848 /var/lib/postgresql/10/main/base/18036/69116.10 (deleted)
postgres   8927        postgres   99u      REG                8,1          0     263342 /var/lib/postgresql/10/main/base/18036/69121 (deleted)
postgres   8927        postgres  100u      REG                8,1 1073741824     263016 /var/lib/postgresql/10/main/base/18036/68889.11 (deleted)
postgres   8927        postgres  101u      REG                8,1 1073741824     263074 /var/lib/postgresql/10/main/base/18036/69116.11 (deleted)
postgres   8927        postgres  102u      REG                8,1 1073741824     263020 /var/lib/postgresql/10/main/base/18036/68889.12 (deleted)
postgres   8927        postgres  103u      REG                8,1 1073741824     263113 /var/lib/postgresql/10/main/base/18036/69116.12 (deleted)
postgres   8927        postgres  104u      REG                8,1 1073741824     263082 /var/lib/postgresql/10/main/base/18036/68889.13 (deleted)
postgres   8927        postgres  105u      REG                8,1 1073741824     263133 /var/lib/postgresql/10/main/base/18036/69116.13 (deleted)
postgres   8927        postgres  106u      REG                8,1 1073741824     263131 /var/lib/postgresql/10/main/base/18036/68889.14 (deleted)
postgres   8927        postgres  107u      REG                8,1 1073741824     263136 /var/lib/postgresql/10/main/base/18036/69116.14 (deleted)
postgres   8927        postgres  108u      REG                8,1 1073741824     259461 /var/lib/postgresql/10/main/base/18036/68889.15 (deleted)
postgres   8927        postgres  109u      REG                8,1 1073741824     263153 /var/lib/postgresql/10/main/base/18036/69116.15 (deleted)
postgres   8927        postgres  110u      REG                8,1   50221056     263135 /var/lib/postgresql/10/main/base/18036/68889.16 (deleted)
postgres   8927        postgres  111u      REG                8,1   49745920     263155 /var/lib/postgresql/10/main/base/18036/69116.16 (deleted)
postgres   9058        postgres   78u      REG                8,1      24576     262826 /var/lib/postgresql/10/main/base/18036/68886_fsm (deleted)
postgres   9058        postgres   79u      REG                8,1          0     263332 /var/lib/postgresql/10/main/base/18036/68886 (deleted)
postgres   9058        postgres   80u      REG                8,1       8192     263850 /var/lib/postgresql/10/main/base/18036/68886_vm (deleted)
postgres   9058        postgres   81u      REG                8,1          0     263781 /var/lib/postgresql/10/main/base/18036/68892 (deleted)
postgres   9058        postgres   82u      REG                8,1          0     263782 /var/lib/postgresql/10/main/base/18036/68893 (deleted)
postgres   9058        postgres   83u      REG                8,1          0     263783 /var/lib/postgresql/10/main/base/18036/68894 (deleted)
postgres   9058        postgres   84u      REG                8,1          0     263085 /var/lib/postgresql/10/main/base/18036/68896 (deleted)
postgres   9058        postgres   85u      REG                8,1      24576     262943 /var/lib/postgresql/10/main/base/18036/69110_fsm (deleted)
postgres   9058        postgres   86u      REG                8,1          0     262940 /var/lib/postgresql/10/main/base/18036/69110 (deleted)
postgres   9058        postgres   87u      REG                8,1       8192     263851 /var/lib/postgresql/10/main/base/18036/69110_vm (deleted)
postgres   9058        postgres   88u      REG                8,1          0     263336 /var/lib/postgresql/10/main/base/18036/69119 (deleted)
postgres   9058        postgres   89u      REG                8,1          0     263341 /var/lib/postgresql/10/main/base/18036/69120 (deleted)
postgres   9058        postgres   90u      REG                8,1          0     263342 /var/lib/postgresql/10/main/base/18036/69121 (deleted)
postgres   9058        postgres   91u      REG                8,1   16777216     263313 /var/lib/postgresql/10/main/pg_wal/000000010000000B000000E8 (deleted)
postgres   9087        postgres  106u      REG                8,1          0     263332 /var/lib/postgresql/10/main/base/18036/68886 (deleted)
postgres   9087        postgres  107u      REG                8,1          0     263781 /var/lib/postgresql/10/main/base/18036/68892 (deleted)
postgres   9087        postgres  108u      REG                8,1          0     263782 /var/lib/postgresql/10/main/base/18036/68893 (deleted)
postgres   9087        postgres  109u      REG                8,1          0     263783 /var/lib/postgresql/10/main/base/18036/68894 (deleted)
postgres   9087        postgres  110u      REG                8,1          0     263085 /var/lib/postgresql/10/main/base/18036/68896 (deleted)
postgres   9087        postgres  111u      REG                8,1          0     262940 /var/lib/postgresql/10/main/base/18036/69110 (deleted)
postgres   9087        postgres  112u      REG                8,1          0     263336 /var/lib/postgresql/10/main/base/18036/69119 (deleted)
postgres   9087        postgres  113u      REG                8,1          0     263341 /var/lib/postgresql/10/main/base/18036/69120 (deleted)
postgres   9087        postgres  114u      REG                8,1          0     263342 /var/lib/postgresql/10/main/base/18036/69121 (deleted)
postgres   9500        postgres   56u      REG                8,1          0     263332 /var/lib/postgresql/10/main/base/18036/68886 (deleted)
postgres   9500        postgres   57u      REG                8,1          0     263781 /var/lib/postgresql/10/main/base/18036/68892 (deleted)
postgres   9500        postgres   58u      REG                8,1          0     263782 /var/lib/postgresql/10/main/base/18036/68893 (deleted)
postgres   9500        postgres   59u      REG                8,1          0     263783 /var/lib/postgresql/10/main/base/18036/68894 (deleted)
postgres   9500        postgres   60u      REG                8,1          0     263085 /var/lib/postgresql/10/main/base/18036/68896 (deleted)
postgres   9500        postgres   62u      REG                8,1          0     263759 /var/lib/postgresql/10/main/base/18036/68891 (deleted)
postgres   9500        postgres   63u      REG                8,1          0     263340 /var/lib/postgresql/10/main/base/18036/68889 (deleted)
postgres   9500        postgres   64u      REG                8,1 1073741824     263822 /var/lib/postgresql/10/main/base/18036/68889.1 (deleted)
postgres   9500        postgres   65u      REG                8,1 1073741824     263824 /var/lib/postgresql/10/main/base/18036/68889.2 (deleted)
postgres   9500        postgres   66u      REG                8,1 1073741824     263826 /var/lib/postgresql/10/main/base/18036/68889.3 (deleted)
postgres   9500        postgres   67u      REG                8,1 1073741824     263828 /var/lib/postgresql/10/main/base/18036/68889.4 (deleted)
postgres   9500        postgres   68u      REG                8,1 1073741824     263831 /var/lib/postgresql/10/main/base/18036/68889.5 (deleted)
postgres   9500        postgres   69u      REG                8,1 1073741824     263834 /var/lib/postgresql/10/main/base/18036/68889.6 (deleted)
postgres   9500        postgres   70u      REG                8,1 1073741824     263838 /var/lib/postgresql/10/main/base/18036/68889.7 (deleted)
postgres   9500        postgres   71u      REG                8,1 1073741824     263841 /var/lib/postgresql/10/main/base/18036/68889.8 (deleted)
postgres   9500        postgres   72u      REG                8,1 1073741824     263844 /var/lib/postgresql/10/main/base/18036/68889.9 (deleted)
postgres   9500        postgres   73u      REG                8,1 1073741824     263847 /var/lib/postgresql/10/main/base/18036/68889.10 (deleted)
postgres   9500        postgres   74u      REG                8,1          0     262940 /var/lib/postgresql/10/main/base/18036/69110 (deleted)
postgres   9500        postgres   75u      REG                8,1          0     263336 /var/lib/postgresql/10/main/base/18036/69119 (deleted)
postgres   9500        postgres   76u      REG                8,1          0     263341 /var/lib/postgresql/10/main/base/18036/69120 (deleted)
postgres   9500        postgres   77u      REG                8,1          0     263342 /var/lib/postgresql/10/main/base/18036/69121 (deleted)
postgres   9500        postgres   80u      REG                8,1       8192     263851 /var/lib/postgresql/10/main/base/18036/69110_vm (deleted)
postgres   9500        postgres   81u      REG                8,1          0     263308 /var/lib/postgresql/10/main/base/18036/69118 (deleted)
postgres   9500        postgres   82u      REG                8,1          0     263305 /var/lib/postgresql/10/main/base/18036/69116 (deleted)
postgres   9500        postgres   83u      REG                8,1 1073741824     263823 /var/lib/postgresql/10/main/base/18036/69116.1 (deleted)
postgres   9500        postgres   84u      REG                8,1 1073741824     263825 /var/lib/postgresql/10/main/base/18036/69116.2 (deleted)
postgres   9500        postgres   85u      REG                8,1 1073741824     263827 /var/lib/postgresql/10/main/base/18036/69116.3 (deleted)
postgres   9500        postgres   86u      REG                8,1 1073741824     263829 /var/lib/postgresql/10/main/base/18036/69116.4 (deleted)
postgres   9500        postgres   87u      REG                8,1 1073741824     263832 /var/lib/postgresql/10/main/base/18036/69116.5 (deleted)
postgres   9500        postgres   88u      REG                8,1 1073741824     263835 /var/lib/postgresql/10/main/base/18036/69116.6 (deleted)
postgres   9500        postgres   89u      REG                8,1 1073741824     263839 /var/lib/postgresql/10/main/base/18036/69116.7 (deleted)
postgres   9500        postgres   90u      REG                8,1 1073741824     263842 /var/lib/postgresql/10/main/base/18036/69116.8 (deleted)
postgres   9500        postgres   91u      REG                8,1 1073741824     263845 /var/lib/postgresql/10/main/base/18036/69116.9 (deleted)
postgres   9500        postgres   92u      REG                8,1 1073741824     263848 /var/lib/postgresql/10/main/base/18036/69116.10 (deleted)
postgres   9573        postgres   31u      REG                8,1          0     262940 /var/lib/postgresql/10/main/base/18036/69110 (deleted)
postgres   9573        postgres   32u      REG                8,1          0     263336 /var/lib/postgresql/10/main/base/18036/69119 (deleted)
postgres   9573        postgres   33u      REG                8,1          0     263341 /var/lib/postgresql/10/main/base/18036/69120 (deleted)
postgres   9573        postgres   34u      REG                8,1          0     263342 /var/lib/postgresql/10/main/base/18036/69121 (deleted)
postgres   9589        postgres   22u      REG                8,1          0     262940 /var/lib/postgresql/10/main/base/18036/69110 (deleted)
postgres   9589        postgres   23u      REG                8,1          0     263336 /var/lib/postgresql/10/main/base/18036/69119 (deleted)
postgres   9589        postgres   24u      REG                8,1          0     263341 /var/lib/postgresql/10/main/base/18036/69120 (deleted)
postgres   9589        postgres   25u      REG                8,1          0     263342 /var/lib/postgresql/10/main/base/18036/69121 (deleted)

It seems that a lot of space is still used up by (deleted) files from Postgres database. How can I free that disc space?

Improve this question
6
  • How exactly did you "delete" the two tables? drop table? or truncate table? or delete from ...?
    – user1822
    Commented Jul 30, 2019 at 12:04
  • I deleted them via pgAdmin4, by right clicking on them and selecting Delete/Drop. I suppose it's equivalent to sql drop table
    – Addas
    Commented Jul 30, 2019 at 12:15
  • What version of PostgreSQL?
    – jjanes
    Commented Jul 30, 2019 at 13:19
  • PostgreSQL 10 on Debian 8 (Jesse)
    – Addas
    Commented Jul 30, 2019 at 13:55
  • What are 8927 and 9500 doing (for example, log in as a superusr and look in pg_stat_activity)? When you drop an object, all back ends get sent a message to close any open file handles they have into that object. But they might be in a state where they will not immediately respond to that message.
    – jjanes
    Commented Jul 30, 2019 at 14:50

1 Answer 1

Reset to default
0

using

kill -9 <process number>

command in debian finally deleted all files that were still using disc space. To find out process number of (deleted) files that were still using disc space use command

lsof | grep -i deleted
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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