Free disk space

Administrative Snippets

Free disk space

Works with PostgreSQL

8.3

Written in

plperlu

Depends on

Linux

Author: Žiga Kranjec

This function returns file system information provided by unix utilities df(1) and mount(8)

You can use it to get this information in SQL instead of shell, which can sometimes be inconvenient.

It tested on Linux only; it might work on some other unices but not on Windows.

CREATE OR REPLACE FUNCTION disk_free(
 OUT dev integer, OUT filesystem text, OUT fstype text, OUT mountpoint text, 
 OUT size numeric, OUT used numeric, OUT avail numeric, 
 OUT use double precision, OUT options text)
  RETURNS SETOF record AS
$BODY$
# Get UNIX file system information
# this uses UNIX df(1) and mount(1) commands

my %d=();

# reported by df(1)
my $df=`df -B1 -a -P`; my @df=split(/[\n\r]+/,$df); shift @df;
for my $l (@df) { 
	my @a=split(/\s+/,$l); my $i=$a[0];
	$d{$i}={
		'filesystem'=>$i,'size'=>$a[1],'used'=>$a[2],
		'avail'=>$a[3],'use'=>$a[4],'mountpoint'=>$a[5]
	};
	if( $d{$i}{'use'}eq'-') { $d{$i}{'use'}=undef; } 
	else { $d{$i}{'use'}=~s/%//; }
	my @stat=stat($d{$i}{'mountpoint'});
	$d{$i}{'dev'}=$stat[0];
}

# reported by mount(8)
my $mt=`mount`; my @mt=split(/[\n\r]+/,$mt);
for my $l (@mt) {
	my @a=split(/\s+/,$l);
	$d{$a[0]}{'fstype'}=$a[4];
	$d{$a[0]}{'options'}=$a[5];
}

for my $i (keys(%d)) { return_next($d{$i}); }

return undef;
$BODY$
  LANGUAGE 'plperlu' VOLATILE
  COST 1000
  ROWS 10;
ALTER FUNCTION disk_free() OWNER TO postgres;
COMMENT ON FUNCTION disk_free() IS 'Get UNIX file system information';

Example

select * from disk_free();

 dev  | filesystem  |   fstype    |        mountpoint        |     size      |     used     |     avail     | use  |              options              
------+-------------+-------------+--------------------------+---------------+--------------+---------------+------+-----------------------------------
 2054 | /dev/sda6   | ext3        | /tmp                     |    1476407296 |     53538816 |    1347870720 |    4 | (rw)
   16 | tmpfs       | tmpfs       | /dev/shm                 |    4254875648 |            0 |    4254875648 |    0 | (rw,nosuid,nodev)
 2051 | /dev/sda3   | ext3        | /var                     |   78742790144 |  16139456512 |   58603335680 |   22 | (rw)
 2055 | /dev/sda7   | ext3        | /home                    | 2837008560128 | 207753547776 | 2485143212032 |    8 | (rw)
   10 | devpts      | devpts      | /dev/pts                 |             0 |            0 |             0 |      | (rw,noexec,nosuid,gid=5,mode=620)
 2050 | /dev/sda2   | ext3        | /                        |    2952847360 |    599957504 |    2202890240 |   22 | (rw,errors=remount-ro)
 2052 | /dev/sda4   | ext3        | /usr                     |   29529182208 |   2355245056 |   25673940992 |    9 | (rw)
 2049 | /dev/sda1   | ext3        | /boot                    |     484179968 |    121042944 |     338137088 |   27 | (rw)
    3 | proc        | proc        | /proc                    |             0 |            0 |             0 |      | (rw,noexec,nosuid,nodev)
   17 | binfmt_misc | binfmt_misc | /proc/sys/fs/binfmt_misc |             0 |            0 |             0 |      | (rw,noexec,nosuid,nodev)
    0 | sysfs       | sysfs       | /sys                     |             0 |            0 |             0 |      | (rw,noexec,nosuid,nodev)
   13 | udev        | tmpfs       | /dev                     |      10485760 |       159744 |      10326016 |    2 | (rw,mode=0755)
(12 rows)