Free disk space

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
Line 49: Line 49:
 
   COST 1000
 
   COST 1000
 
   ROWS 10;
 
   ROWS 10;
ALTER FUNCTION os.disk_free() OWNER TO postgres;
+
ALTER FUNCTION disk_free() OWNER TO postgres;
 
COMMENT ON FUNCTION disk_free() IS 'Get UNIX file system information';
 
COMMENT ON FUNCTION disk_free() IS 'Get UNIX file system information';
 
</source>
 
</source>

Revision as of 18:23, 20 April 2010

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)
Personal tools