PostgreSQL USE plpythonu get Linux FileSystem usage
背景
PostgreSQL数据库表空间和目录一一对应, 目录有多少剩余空间, 代表表空间的剩余空间.
但是作为数据库的使用者, 可能没有操作系统的权限去查看目录有多少空间, 那么如何才能做到在数据库中获取文件系统的剩余空间呢?
比较简单的做法是使用plpythonu语言, 如下 :
查看系统安装了哪些语言handler.
digoal=# select * from pg_pltemplate ;
tmplname | tmpltrusted | tmpldbacreate | tmplhandler | tmplinline | tmplvalidator | tmpllibrary | tmplacl
------------+-------------+---------------+------------------------+--------------------------+---------------------+-------------------+---------
plpgsql | t | t | plpgsql_call_handler | plpgsql_inline_handler | plpgsql_validator | $libdir/plpgsql |
pltcl | t | t | pltcl_call_handler | | | $libdir/pltcl |
pltclu | f | f | pltclu_call_handler | | | $libdir/pltcl |
plperl | t | t | plperl_call_handler | plperl_inline_handler | plperl_validator | $libdir/plperl |
plperlu | f | f | plperlu_call_handler | plperlu_inline_handler | plperlu_validator | $libdir/plperl |
plpythonu | f | f | plpython_call_handler | plpython_inline_handler | plpython_validator | $libdir/plpython2 |
plpython2u | f | f | plpython2_call_handler | plpython2_inline_handler | plpython2_validator | $libdir/plpython2 |
plpython3u | f | f | plpython3_call_handler | plpython3_inline_handler | plpython3_validator | $libdir/plpython3 |
(8 rows)
安装语言 :
digoal=# create language plpythonu;
CREATE LANGUAGE
使用online code测试 :
digoal=# do language plpythonu $$
import os
import statvfs
phydevs = []
f = open("/proc/filesystems", "r")
for line in f:
if not line.startswith("nodev"):
phydevs.append(line.strip())
retlist = []
f = open('/etc/mtab', "r")
for line in f:
if line.startswith('none'):
continue
fields = line.split()
device = fields[0]
mountpoint = fields[1]
fstype = fields[2]
if fstype not in phydevs:
continue
if device == 'none':
device = ''
vfs=os.statvfs(mountpoint)
available=vfs[statvfs.F_BAVAIL]*vfs[statvfs.F_BSIZE]/(1024*1024*1024)
capacity=vfs[statvfs.F_BLOCKS]*vfs[statvfs.F_BSIZE]/(1024*1024*1024)
used=capacity-available
plpy.notice('mountpoint',mountpoint,'capacityGB',capacity,'usedGB',used,'availableGB',available)
$$;
输出 :
NOTICE: ('mountpoint', '/', 'capacityGB', 28, 'usedGB', 14, 'availableGB', 14)
CONTEXT: PL/Python anonymous code block
NOTICE: ('mountpoint', '/pgdata/digoal/1921/data01', 'capacityGB', 39, 'usedGB', 9, 'availableGB', 30)
CONTEXT: PL/Python anonymous code block
NOTICE: ('mountpoint', '/pgdata/digoal/1921/data02', 'capacityGB', 39, 'usedGB', 2, 'availableGB', 37)
CONTEXT: PL/Python anonymous code block
NOTICE: ('mountpoint', '/pgdata/digoal/1921/data03', 'capacityGB', 39, 'usedGB', 2, 'availableGB', 37)
CONTEXT: PL/Python anonymous code block
NOTICE: ('mountpoint', '/pgdata/digoal/1921/data04', 'capacityGB', 39, 'usedGB', 2, 'availableGB', 37)
CONTEXT: PL/Python anonymous code block
NOTICE: ('mountpoint', '/pgdata/digoal/1921/data05', 'capacityGB', 39, 'usedGB', 2, 'availableGB', 37)
CONTEXT: PL/Python anonymous code block
NOTICE: ('mountpoint', '/pgdata/digoal/1921/data06', 'capacityGB', 39, 'usedGB', 5, 'availableGB', 34)
CONTEXT: PL/Python anonymous code block
DO
需要注意的是这个language是untrusted的, 只有超级用户能够创建它的函数.
digoal=> grant usage on language plpythonu to digoal;
ERROR: language "plpythonu" is not trusted
HINT: Only superusers can use untrusted languages.
例如使用普通用户执行以上online code, 将会报错 :
digoal=> do language plpythonu $$
import os
import statvfs
phydevs = []
f = open("/proc/filesystems", "r")
for line in f:
if not line.startswith("nodev"):
phydevs.append(line.strip())
retlist = []
f = open('/etc/mtab', "r")
for line in f:
if line.startswith('none'):
continue
fields = line.split()
device = fields[0]
mountpoint = fields[1]
fstype = fields[2]
if fstype not in phydevs:
continue
if device == 'none':
device = ''
vfs=os.statvfs(mountpoint)
available=vfs[statvfs.F_BAVAIL]*vfs[statvfs.F_BSIZE]/(1024*1024*1024)
capacity=vfs[statvfs.F_BLOCKS]*vfs[statvfs.F_BSIZE]/(1024*1024*1024)
used=capacity-available
plpy.notice('mountpoint',mountpoint,'capacityGB',capacity,'usedGB',used,'availableGB',available)
$$;
ERROR: permission denied for language plpythonu
使用超级用户创建函数, 让普通用户调用是可以的.
digoal=# create or replace function get_fs() returns void as $$
digoal$# import os
digoal$# import statvfs
digoal$# phydevs = []
digoal$# f = open("/proc/filesystems", "r")
digoal$# for line in f:
digoal$# if not line.startswith("nodev"):
digoal$# phydevs.append(line.strip())
digoal$# retlist = []
digoal$# f = open('/etc/mtab', "r")
digoal$# for line in f:
digoal$# if line.startswith('none'):
digoal$# continue
digoal$# fields = line.split()
digoal$# device = fields[0]
digoal$# mountpoint = fields[1]
digoal$# fstype = fields[2]
digoal$# if fstype not in phydevs:
digoal$# continue
digoal$# if device == 'none':
digoal$# device = ''
digoal$# vfs=os.statvfs(mountpoint)
digoal$# available=vfs[statvfs.F_BAVAIL]*vfs[statvfs.F_BSIZE]/(1024*1024*1024)
digoal$# capacity=vfs[statvfs.F_BLOCKS]*vfs[statvfs.F_BSIZE]/(1024*1024*1024)
digoal$# used=capacity-available
digoal$# plpy.notice('mountpoint',mountpoint,'capacityGB',capacity,'usedGB',used,'availableGB',available)
digoal$# $$ language plpythonu;
CREATE FUNCTION
普通用户调用 :
postgres=# \c digoal
You are now connected to database "digoal" as user "postgres".
digoal=# select get_fs();
NOTICE: ('mountpoint', '/', 'capacityGB', 28, 'usedGB', 14, 'availableGB', 14)
CONTEXT: PL/Python function "get_fs"
NOTICE: ('mountpoint', '/pgdata/digoal/1921/data01', 'capacityGB', 39, 'usedGB', 9, 'availableGB', 30)
CONTEXT: PL/Python function "get_fs"
NOTICE: ('mountpoint', '/pgdata/digoal/1921/data02', 'capacityGB', 39, 'usedGB', 2, 'availableGB', 37)
CONTEXT: PL/Python function "get_fs"
NOTICE: ('mountpoint', '/pgdata/digoal/1921/data03', 'capacityGB', 39, 'usedGB', 2, 'availableGB', 37)
CONTEXT: PL/Python function "get_fs"
NOTICE: ('mountpoint', '/pgdata/digoal/1921/data04', 'capacityGB', 39, 'usedGB', 2, 'availableGB', 37)
CONTEXT: PL/Python function "get_fs"
NOTICE: ('mountpoint', '/pgdata/digoal/1921/data05', 'capacityGB', 39, 'usedGB', 2, 'availableGB', 37)
CONTEXT: PL/Python function "get_fs"
NOTICE: ('mountpoint', '/pgdata/digoal/1921/data06', 'capacityGB', 39, 'usedGB', 5, 'availableGB', 34)
CONTEXT: PL/Python function "get_fs"
get_fs
--------
(1 row)
参考
1. http://www.linux-field.com/archives/161
[mps@mps-1 ~]$ python
Python 2.4.3 (#1, Sep 3 2009, 15:37:37)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-46)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import os
>>> import statvfs
>>> vfs=os.statvfs("/home")
>>> vfs
(4096, 4096, 70959944, 70058799, 66396080, 73269248, 73234981, 73234981, 0, 255)
>>> dir(statvfs)
['F_BAVAIL', 'F_BFREE', 'F_BLOCKS', 'F_BSIZE', 'F_FAVAIL', 'F_FFREE', 'F_FILES', 'F_FLAG', 'F_FRSIZE', 'F_NAMEMAX', '__builtins__', '__doc__', '__file__', '__name__']
>>> available=vfs[statvfs.F_BAVAIL]*vfs[statvfs.F_BSIZE]/(1024*1024*1024)
>>> available
253
>>> capacity=vfs[statvfs.F_BLOCKS]*vfs[statvfs.F_BSIZE]/(1024*1024*1024)
>>> capacity
270
>>> used=capacity-available
>>> used
17
>>>
2. http://blog.csdn.net/magic_zj00/article/details/7207445
3. http://docs.python.org/library/statvfs.html
4. http://docs.python.org/library/os.html