3期基于继承的流水分区表气泡期限权限监控

1、PostgreSQL research公益是一辈子的事, Im 德哥Digoal, Just!首页日志相册音乐收藏博友关于我日志显示设置PostgreSQL json_accessorsPostgreSQL par ition table name convert to dat关于我PostgreSQL partition table or inherits table predi ct count and gap and privilege monitor20130104 16:42:15 |： PgSQL Admin订阅| 字号当管理的数据库越来越多, 分本文基于PostgreSQL 9.

2、2 进行讲解, 其PostgreSQL 的分区表除了性能方面需要加强例如,一、分区表的气泡, 例如以下情况 :文章22）9）（6）stemTAP（89）LOFTER精选以上分区表tbl_2012_04不二、分区表的子表权1. 新增了子表, 忘记给新增的子表赋予权限.例如系统中有另外的用户需要读分区表, 在一段时间后新增了子表, 很有可能会忘记赋予权限.PostgreSQL 9.0开始, 只要给主表赋权, 直接操作主表的话, 不需要子表权限. 但是直接表的话, 还是需要赋权的.子愿你被这世界温柔以待网易三、分区表的过期, 例如以下情况 :tbl子表 :tbl_2012_12 12条生活黄金定律t

3、bl_2013_01男童被沙埋遇难 父亲徒手挖沙Note how table access permissions aandled. Querying a parent table can automatically access data in child tables without further access privilege checking. This preserves the appearance that the data is (also) in the parent table. Accessing the child tables directly is, howev

4、er, not automatically allowed and would require further privileges to be granted.tbl子表 : tbl_2012_01 tbl_2012_02tbl_2012_03tbl_2012_05 tbl_2012_06 Stop crying your heart out Oasis那么到2013年02月份的时候, 子表tbl_2013_02还不, 通常会报表不的错误. 残疾小狗闹市欲被救流泪接下来将以上几种情况进行 救护车被截停 伤者不治身亡太冷游上岸1. 表名中的日期使用标准的yyyy, mm, dd 格式. 例如,

5、 tbl_2012_1_1 是不合法的,应该改成tbl_2012_01_01. 沈阳一小区现山寨版凯旋门2. 继承表的表名应该包含例如, tbl, tbl_2014.的表名作为前缀.网易客户端 当一个表直接继承多个如下 :的时候, 可用inhseqno来区分.ALTER TABLE网易博客LOFTER最多人用的同步digoal= select inhparent:regclass,inhrelid:reinhparent | inhrelid | inhseqno-+-+-t1| t|1t2| t|2(2 rows)解除继承后重新继承的话, inhseqno会增加.在被通过pg_class.o

6、id获取权限的函数, 用于比较子表的权限和create or replace function mon_part_get_acl(i_oid oid) returns aem as $declaredigoal= alter table t no inherit t1; ALTER TABLEdigoal= alter table t inherit t1; ALTER TABLEdigoal= select inhparent:regclass,inhrelid:regclass,inhseqno from pg_inherits ; inhparent | inhrelid | inhse

7、qno-+-+-t2| t|2t1| t|3(2 rows)个人中心 消息(1)写日志工具digoal= create table t1(id int); CREATE TABLEdigoal= create table t2(id int); CREATE TABLEdigoal= create table t(id int) inherits(t1);NOTICE: merging column id with inherited definition CREATE TABLEdigoal= alter table t inherit t2;If there is more than on

8、e direct parent for a child table (multiple inheritance), this number tells the order in which the inherited columns are to be arranged. The count starts at 1.result aem := :aem;beginselect relacl into result from pg_class where oid=i_oid and relacl is not null;if found thenreturn result;elsereturn

9、:aem;end if;exception when others thenreturn :aend;$ language plpgsql;把表名的数字转成日期的函数, 用于create or replace function mon_part_condate as $declarev_len int;v_date text := ;i text := ;v_suffix text := ;beginv_suffix := replace(i_relname,i_prefix,);FOREACH i IN ARRAY regexp_split_to_array(v_suffix, )loopi

10、f (i = 0 and i = 18 and substr(v_date,1,2) = 4 and v_len =365 then - 预计按年分区pdate - date_trunc(day, (pdate - interval 1 year):datewhen pdate-lag_pdate =28 then - 预计按月分区pdate - date_trunc(day, (pdate - interval 1 month):datewhen pdate-lag_pdate =1 then - 预计按日分区1else0.9 - 分区间隔小于1天end AS predict_gapfrom

11、(selectrow_number() over (partition by inhparent order bymon_part_conv_to_date(inhrelid:regclass:text,inhparent:regclass:text) AS rn,current_database() AS dbname,inhparent:regclass:text AS inhparent,lag(inhrelid:regclass:text,1) over (partition by inhparent order bymon_part_conv_to_date(inhrelid:reg

12、class:text,inhparent:regclass:text) AS lag_inhrelid,inhrelid:regclass:text AS inhrelid,mon_part_get_acl(inhparent) AS acl_inhparent,mon_part_get_acl(inhrelid) AS acl_inhrelid,mon_part_conv_to_date(inhrelid:regclass:text,inhparent:regclass:text) AS pdate,lag(mon_part_conv_to_date(inhrelid:regclass:te

13、xt,inhparent:regclass:text), 1) over (partition byinhparent order by mon_part_conv_to_date(inhrelid:regclass:text,inhparent:regclass:text) AS lag_pdate,max(mon_part_conv_to_date(inhrelid:regclass:text,inhparent:regclass:text) over (partition byinhparent) AS max_pdatefrom pg_inherits) t) t;视图结构如下 :例如

14、保持未来有10个子表可用,并且未来2年(731day)子表已经创建,继承表的权限是否一致,select * from v_mon_partition_tableswhere acl_inhparentacl_inhrelid;select * from (select dbname,inhparent,lag_inhrelid,inhrelid,max_pdate,predict_gap,future_tables_cnt,row_number() over (partition by inhparent order by pdate desc) AS rnfrom v_mon_partiti

15、on_tables where(future_tables_cnt=10 or max_pdate - current_date 731) and pdate 22220101:dateand lag_pdate is not null) t order by rn;select * from v_mon_partition_tables wheregappredict_gapand pdate 22220101:date and lag_pdate is not null;View payment.v_mon_partition_tables Column|Type| Modifiers-+

16、-+-rn| bigint|dbname| name |inhparent | text | lag_inhrelid | text | inhrelid | text |acl_inhparent| a acl_inhrelid| aem | pdate| date|lag_pdate| date|max_pdate | date |gap| integer |predict_gap| numeric| future_tables_cnt | numeric|测试 :1.日期继承表气泡SQL,2. 何时过期SQL, 例如保持未来有10个子表可用,并且未来2年(731day)子表已经创建,se

17、lect * from(select dbname,inhparent,lag_inhrelid,inhrelid,max_pdate,predict_gap,future_tables_cnt,row_number() over(partition by inhparent order by pdate desc) AS rnfrom v_mon_partition_tableswhere(future_tables_cnt=10 or max_pdate - current_date 731)and pdate 22220101:dateand lag_pdate is not null)

18、 t order by rn;dbname | inhparent | inhrelid |pdate| max_pdate | gap | future_tables_cnt-+-+-+-+-+-+-digoal | tbl| tbl_201202 | 2012-02-01 | 2012-05-01 | 31 | -8.0000000000000000digoal | tbl| tbl_201204 | 2012-04-01 | 2012-05-01 | 60 | -8.0000000000000000digoal= create table tbl(id int, crt_time tim

19、estamp(0); CREATE TABLEdigoal= create table tbl_201201 (id int, crt_time timestamp(0) inherits(tbl); NOTICE: merging column id with inherited definitionNOTICE: merging column crt_time with inherited definition CREATE TABLEdigoal= create table tbl_201202 (id int, crt_time timestamp(0) inherits(tbl);

20、NOTICE: merging column id with inherited definitionNOTICE: merging column crt_time with inherited definition CREATE TABLEdigoal= create table tbl_201204 (id int, crt_time timestamp(0) inherits(tbl); NOTICE: merging column id with inherited definitionNOTICE: merging column crt_time with inherited def

21、inition CREATE TABLEdigoal= create table tbl_201205 (id int, crt_time timestamp(0) inherits(tbl); NOTICE: merging column id with inherited definitionNOTICE: merging column crt_time with inherited definition CREATE TABLEdigoal= select * from v_mon_partition_tables wheregappredict_gapand pdate 2222010

22、1:date and lag_pdate is not null;dbname | inhparent | inhrelid |pdate| lag_pdate | gap | predict_gap-+-+-+-+-+-+-digoal | tbl| tbl_201204 | 2012-04-01 | 2012-02-01 | 60 |31(1 row)digoal | tbl| tbl_201205 | 2012-05-01 | 2012-05-01 |30 | -8.2666666666666667(3 rows)创建超过未来2年的子表(现在是201301) :再次执行SQL :3. 继

23、承表的权限是否一致,当前未赋任何权限, 所以权限是一致的.当修改的权限后, 如果未给子表赋予权限, 那么权限将不一致, 所以有结果 :digoal= grant select on tbl to skycac;GRANTdigoal= select dbname,inhparent,inhrelid,acl_inhparent,acl_inhrelid from v_mon_partition_tableswhere acl_inhparentacl_inhrelid;dbname | inhparent | inhrelid |acl_inhparent| acl_inhrelid-+-+-

24、+-+-digoal | tbl| tbl_201201 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201202 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201204 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201205 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201206 | d

25、igoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201207 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal= select dbname,inhparent,inhrelid,acl_inhparent,acl_inhrelid from v_mon_partition_tables digoal-where acl_inhparentacl_inhrelid;dbname | inhparent | inhrelid | acl_inhparent | acl_inhreli

26、d-+-+-+-+-(0 rows)select * from (select dbname,inhparent,lag_inhrelid,inhrelid,max_pdate,predict_gap,future_tables_cnt,row_number() over (partition by inhparent order by pdate desc) AS rnfrom v_mon_partition_tables where(future_tables_cnt=10 or max_pdate - current_date 731) and pdate 22220101:datean

27、d lag_pdate is not null) t order by rn;dbname | inhparent | inhrelid | pdate | max_pdate | gap | future_tables_cnt-+-+-+-+-+-+-(0 rows)create table tbl_201206 (id int, crt_time timestamp(0) inherits(tbl);.create table tbl_201507 (id int, crt_time timestamp(0) inherits(tbl);digoal | tbl| tbl_201208 |

28、 digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201209 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201210 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201211 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201212 | digoal=arwdDxt/digoal,skycac=r/

29、digoal | digoal | tbl| tbl_201301 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201302 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201303 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201304 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_20130

30、5 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201306 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201307 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201308 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201309 | digoal=arwdDxt/digoal,skycac

31、=r/digoal | digoal | tbl| tbl_201310 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201311 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201312 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201401 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_20

32、1402 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201403 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201404 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201405 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201406 | digoal=arwdDxt/digoal,sky

33、cac=r/digoal | digoal | tbl| tbl_201407 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201408 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201409 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201410 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl

34、_201411 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201412 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201501 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201502 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201503 | digoal=arwdDxt/digoal,

35、skycac=r/digoal | digoal | tbl| tbl_201504 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201505 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201506 | digoal=arwdDxt/digoal,skycac=r/digoal | digoal | tbl| tbl_201507 | digoal=arwdDxt/digoal,skycac=r/digoal | (42 rows)脚本中未修

36、改, 以上SQL已调整, 注意同步调整以下脚本的内容.用以上的SQL写个SHELL脚本就可以进行例如 :了, 或者结合nagios来进行.oczdb-172-16-3-150- cat t.sh#!/bin/bash# 环境变量export LANG=en_US.utf8export PGHOME=/opt/pgsqlexport LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/libexport PATH=$PGHOME/bin:$PATH:.# 参数if $# -

37、ne 3 ; thenecho -e Please check your parameter.echo -e $0 HOST PORT ROLE nexit 1fiTODAY=date +%Y%m%d=digoalHOST=$1PORT=$2ROLE=$3for DB in psql -q -t -h $HOST -p $PORT -U $ROLE postgres -c selectdatname not in (postgres,template0,template1)doecho -e -date +%F %T-Partition Table MONIP:$HOST PORT:$PORT

38、 DBNAME:psql -h $HOST -p $PORT -U $ROLE $DB = 0 and i = 18 and substr(v_date,1,2) = 4 and v_len =365 thenpdate - date_trunc(year, (pdate - interval 1 year):datewhen pdate-lag_pdate =28 thenpdate - date_trunc(month, (pdate - interval 1 month):datewhen pdate-lag_pdate =7 thenpdate-lag_pdatewhen pdate-

39、lag_pdate =1 thenpdate - date_trunc(day, (pdate - interval 1 day):dateelse0.9end AS predict_gapfrom(selectrow_number() over (partition by inhparent order by mon_part_conv_to_date(inhrelid:regclass):text) ASrn,current_database() AS dbname,(inhparent:regclass):text AS inhparent,(inhrelid:regclass):tex

40、t AS inhrelid,mon_part_get_acl(inhparent) AS acl_inhparent,mon_part_get_acl(inhrelid) AS acl_inhrelid,mon_part_conv_to_date(inhrelid:regclass):text) AS pdate,lag(mon_part_conv_to_date(inhrelid:regclass):text), 1) over (partition by inhparent order bymon_part_conv_to_date(inhrelid:regclass):text) AS

41、lag_pdate,max(mon_part_conv_to_date(inhrelid:regclass):text) over (partition by inhparent) AS max_pdatefrom pg_inherits) t) t;select -日期继承表气泡: as monitor;select dbname,inhparent,inhrelid,pdate,lag_pdate,gap,predict_gap from v_mon_partition_tableswheregappredict_gapand pdate 22220101:dateand lag_pdat

42、e is not null;select -日期继承表过期(需满足大于未来10个表,且大于未来2年(731day): as monitor;select * from(select dbname,inhparent,inhrelid,max_pdate,predict_gap,future_tables_cnt,row_number() over (partition byinhparent order by pdate desc) AS rnfrom v_mon_partition_tableswhere(future_tables_cnt=10 or max_pdate - current

43、_date 731)and pdate 22220101:dateand lag_pdate is not null) twhere rn=1;select -select server_ip,server_port,dbname,name_inhparentfrom mon_inheritswhere acl_inhparentacl_inhrelidgroup by server_ip,server_port,dbname,name_inhparent;EOFdone执行 :oczdb-172-16-3-150- ./t.sh 127.0.0.1 9201 postgres-2013-01

44、-04 17:14:16-Partition Table MON-IP:127.0.0.1 PORT:9201 DBNAME:digoal-CREATE FUNCTIONCREATE FUNCTIONCREATE VIEWmonitor-日期继承表气泡:(1 row)dbname | inhparent |inhrelid|pdate| lag_pdate | gap | predict_gap-+-+-+-+-+-+-digoal | digoal.tbl | digoal.tbl_201204 | 2012-04-01 | 2012-02-01 | 60 |31(1 row)monitor

45、-日期继承表过期(需满足大于未来10个表,且大于未来2年(731day):(1 row)dbname | inhparent | inhrelid | max_pdate | predict_gap | future_tables_cnt | rn-+-+-+-+-+-+-(0 rows)monitor-所以继承表的权限和是否一致,输出不一致的:(1 row)dbname | inhparent |inhrelid|acl_inhparent| acl_inhrelid-+-+-+-+-digoal | digoal.tbl | digoal.tbl_201201 | digoal=arwdDxt/digoal,s