Skip to content
Permalink
Branch: master
Find file Copy path
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
242 lines (202 sloc) 9.33 KB

PostgreSQL USE plpythonu get Linux FileSystem usage

作者

digoal

日期

2013-07-22

标签

PostgreSQL , plpython , pl language , 获取文件系统使用情况


背景

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文章入口

免费领取阿里云RDS PostgreSQL实例、ECS虚拟机

You can’t perform that action at this time.