剩余磁盘空间

管理片段

剩余磁盘空间

适用于 PostgreSQL

8.3

用

plperlu

依赖于

Linux

作者：Žiga Kranjec

此函数返回由 unix 工具 df(1) 和 mount(8) 提供的文件系统信息

您可以使用它在 SQL 中获取此信息，而不是使用 shell，这有时可能不方便。

它只在 Linux 上测试过；它可能在其他一些类 Unix 系统上工作，但在 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';

示例

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)