PostgreSQL USE plpythonu get Linux FileSystem usage

3 minute read

背景

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

Flag Counter

digoal’s 大量PostgreSQL文章入口