开发者学堂课程【PostgreSQL快速入门：21 PostgreSQL 监控2 趋势监控数据收集和分析nagios实时监控部署和自定义监控】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/16/detail/80
21 PostgreSQL 监控2 趋势监控数据收集和分析nagios实时监控部署和自定义监控
五、自定义脚本
在编写自定义脚本前首先要搞清楚nagios的判断标准。先来查看规则，例如输入/opt/nagios/libexec/check_load -w 15.10.5 -c 30.25.20
显示结果，再输入 echo $?查看返回值，返回值为0正常
显示结果中的|是将结果信息分为两端，前者为状态，后者为 information。同样我们可以实现此类操作：
1.脚本的返回值
nagios 根据脚本返回值来输出该监控服务的状态.返回值和状态对应关系如下.
0:(OK)
1 : (WARNING) //告警
2 : (CRITICAL) //严重告警
other : (UNKNOWN) //未知状态
可以看到此时开始监控，有些为正常
2.脚本的标准输出
pg92@db-172-16-3-39-〉/opt/ check_postgres-2.20.0/ check postgres.pl--PGBTNDIR=/opt/pgsql92/bin --output=nagios
--datadir=/pedata1919 --action=checkpoint -w 300s -c 600s
POSTGRES_CHECKPOINT CRTTICAL: Last checkpoint was 11418 seconds ago | age=11418;300;600 //返回值为2，信息分为两段：一个为当前状态，一个为 information
以上监控命令的标准输出为
POSTGRES_CHECKPOINT CRITICAL: Last checkpoint was 11418 seconds ago |age=11418;300:600
被监控机上的脚本是在 nagios 服务端通过 check_nrpe 远程调用的方式执行的.被监控端的nrpe程序以什么用户组执行在如下配置中:
[root@db-172-16-3-39 check_postgres-2.20.0]# cat / etc/xinetd. d/nrpe |grep -E " group|user"
user = pg92
group= pg92
（此处写脚本时使用 postgres 进行调用，如果写其它脚本相当于使用其它用户调用，这个用户必须对脚本有执行权限，否则会报错）
因此监控脚本需要有以上用户组的可执行权限.
六、自写脚本
来自于 bucardo 提供的 check_postgres 脚本。大部分的常用监控项：
是否打开归档（数据库是否开归档很重要，如果开启归档可以做一些备份）
是否打开 vacuum（如果没有自动打开垃圾回收，数据库可能膨胀严重）
数据库年龄
（postgres 使用多版本并行控制，使用32位的一个数据类型，该数据类型很容易达到极限溢出，所以是一个循环使用。例如从0-21循环区间中循环使用，到21后又会返回到0。所以数据库的年龄不允许超过最大值，最大值为2的31次方-当前年龄。例如当前年龄为100万，那么可存活时间为2的31次方-100万。数据库中有很多机制会保护不到达最大年龄，会自动做 vacuum 降低年龄。如果距到达postgre极限还差一百万时不允许在数据库中提交事务，还差一千万时会进行提示及时进行垃圾回收。在数据库中提交事务相当于在消耗年龄。集群为统一的，若数据库中某一个集群的年龄即将到达极限，那么所有集群都将到达极限）
场景：数据库遇到无法正常申请事务号的错误,原因是某些库由于年龄太老,已经到达允许的xid上限(xidStopLimit = xidWrapLimit - 1000000),数据库预留了100万的事务作为维护用.所以此时不允许任何会话申请新的事务号.（如果100万个对象年龄到达极限，那么在降低年龄时需要将100万个对象的年龄都降低。但是在做freeze时，每做一次 freeze，就需要消耗一次事务号，如果对象超过100万，需要消耗超过100万的事务号才能将数据库年龄降低。如果遇到该场景，可以修改源代码，将1000000的值提高，例如将 warnlimit 提高到10000000）
这样的话就必须进入单用户模式做 vacuum freeze 处理.将年龄降下来
另一种情况是警告.如果在到达(xidWarnLimit = xidStopLimit - 10000000)的年龄后,数据库会给出提示,尽快执行vacuum freeze将数据库的年龄降下来,避免达到 xidstopLimit ,那个时候就必须进单用户处理了.
总的来说数据库有几个参数来防止数据库到达 xidStopLimit .
vacuum_freeze_table_age //指当freeze_table到达指定年龄后，用 vacuum 扫全表
autovacuum_freeze_max_age //当数据库没有开启 autovacuum 时，只要当表达到限定年龄时会自动做 freeze表进行全表扫描
避免发生数据库年龄到达 xidStopLimit 的几种方法:
（1）合理的设置 vacuum_freeze_table_age,这个参数决定了表的年龄到达这个值以来还未发生过全表数据块的vacuum的话,那么这个 vacuum 或 autovacuum 操作就会扫描全表,并且触发 freeze,从而降低表的年龄.所以这个值不能太大.这样的话数据库有足够多的机会降低表的年龄.但是也不能太小，太小会频繁的触发全表扫,带来 IO 开销.（根据数据库 I/O 情况、数据库事务消耗情况合理设置。例如每天消耗大概一亿的事务，数据库 I/O 允许扫描1G 的表。数据库中最大的表为100G，还有19个事务允许去将表年龄降低。19个亿的事务每天消耗1个亿，相当于19天。即表可以允许用19天时间来降低年龄。）
（2）合理的设置autovacuum_freeze_max_age.这个参数即使在没有开启 autovacuum 的情况下.只要年龄到达这个值也会触发 autovacuum.
这个值是一个比较好的保护值,最大是20亿.但是不建议这么大.应该预留足够的值防止到达 xidStopLimit 和xidWarnLimit 阶段.预留多少合适呢?例如完成全库扫描要1天.并且一天会产生1亿个事务的话,那么预留最少要1亿比较好.这样的话就设置成19亿
（3）数据库的使用要合理.例如对于长事务,并且事务隔离级别是 repeatable read 或 SERIALIZABLE 的话.会影响vacuum freeze 降低对象的年龄（例如有一个存在的事务隔离级别为 repeatable read 或 SERIALIZABLE，那么该事务只要不关闭，vacuum_freeze 不能降低任何表。因为 repeatable read 就确保必须要读到需要的镜像的数据。因此在数据库使用过程中只要是隔离级别的事务，vacuum freeze 不能提前结束该事务，如果在事务结束前就结束了vacuum freeze，那么没有办法降低任何一个表的年龄）
同样要避免和逻辑备份的时间点冲突.逻辑备份使用的是 repeatable read 隔离级别,并且备份时间长
参见:
http://blog.163.com/digoal@126/blog/static/163877040201241134721101/
http://blog.163.com/digoal@126/blog/static/163877040201322510519547/
或 pg_dump的代码
1、控制单表的大小,因为太大会增加单次扫描的负担.如果未能降低年龄.重复扫描更加浪费（例如 I/O 为每秒100兆，那么单表最好不超过10G。与 I/O 能力有关，如果 I/O 能力强，单表可以大。）
2、提升存储 IO 能力,这样也能提升 VACUUM FREEZE 的速率
3、必要的情况下,进行分库(严格来说是分集群，不要放在一个集群下面.因为xid是整个集群统一分配的)
4、加大maintenance_work_mem,也能一定程度上提升vacuum速率.
数据库单用户模式处理数据库到达 xidStopLimit 场景的方法
首先关闭数据库
然后进入单用户，进入指定的数据库名.
postgres --single -E $dbname
backend>
为了最快的降低数据库的年龄，其实没有必要做全库的 vacuum freeze，数据库的年龄由数据库中年龄最大的表决定:所以只要把年龄最大的表年龄降下来，数据库的年龄就会变成年龄次大的表的年龄，以此类推.
那么我们进入单用户后，首先要按年龄排序，查出年龄大的表-select b.nspnome a.relname, a.relfrozerxid, age (a .relfrozenxid) from pg_class a,pg_namespace b where a.relnanespace=b.oid and a. relkind=' r’ order by a relfrozenari d: : text : : int8 limit 10;
根据查到的表做vacuum freeze.
vacuum freeze tbl;
做完以后，查询一下数据库的年龄是否降了，降到合理范围后(建议降到)
select datname, datfrozexid, age(datfrozenxid) from pg_database ;
然后就可以启动数据库了，启动后建议做全库的 vacuum freeze，彻底降低年龄，同时定制空闲时间的vacuum freeze.
5、连接数
6、提交和回滚的比例(问题修复后.,手工清除统计信息pg_stat_reset(),连接到对应的库执行)
对于数据库如果回滚比例很高，一定程度上说明程序存在问题或者数据库中有逻辑错误导致事务不能提交
7、standby 延迟
通过 replication 对象来监控，sent_location 指对方已经收到的location，pg_current_xlog_insert_location指当前数据库正在写的 location，通过对比两者可以得知 standby 与主库存在多少延迟
8、锁等待
9、长事务/空闲事务
10、prepared 事务
11、序列剩余量(每个库查询)
12、未使用的索引(每个库查询)
一个数据库中对于有频繁删除更新插入的表，索引太多会影响性能，如果索引创建无用，那么还会降低性能，所以监控未使用的索引也是必要的。
以 PostgreSQL9.3为例.脚本如下:
vi nagios. sh
#!/bin/bash
#exp1 : $prog arch //两种用法，集群级别用法
#exp2 : $prog idx $dbname //单库使用
# please use database superuser
export LANG=en_US. utf8
export PGPORT=5432 //连接到服务器
export PGDATA=/data01/pgdata/pg_root //端口号
export PGHDME=/opt/ pgsqlexport LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/ usr/lib84:/usr/local/lib84:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE="date +"%Y%m%H%M”’
export PATH=$PGHOME/bin:$PATH:
export PGUSER=postgres //使用哪个用户连接
export PGHOST=$PGDATA
export PGDATABASE=postgres //database
#monitor parameter
#cluster monitorarch="select case setting when ' on’then ' Normal ’ else '‘Warning 'end ||name||',' ||setting from pg_settings where
name=' archive_mode’:" //SQL 语句，通过该语句监控是否打开归档
autovacuum="select case setting when ' on’then ' Nornal ' else ' Warning ’ end||namn||,' ||setting from pg_settings where
name=' autovacuum’;"age="select case when age>600000000 then 'Warning ’ else 'Normal ' end||datname||’age ' ||age from (select
datname, age(datfrozenxid) as age from pg_database) t order by age ;" //年龄监控，查 pg_database 字段。设置阈值，>600000000做告警。
conn="select case when t2.setting-t1. cnt < 200 then ' Warning ' else ' Normal ' end||'max ‘||t2. setting||’ now' ||t1. cnt from(select count (*) as cnt from pg_stat_activity)t1， (select setting::int8 from pg_settings where name = ' max_connections')
t2;" //平均数监控通过查看 pg_stat_activity，查看当前连接在数据库下的连接。max_connections 指数据库允许的最大连接数。此处当剩余连接数<200时warningrollback=select case when xact rollback/xact_comit::.numeric<0.1 then ' Normal ' else 'Warning ' end||datname||’c'||xact_commit||’r ' ||xact_rollback from pg_stat_database where xact_commit>0 order by 1 desc:" //数据库回滚比例<0.1时正常，>0.1则告警standby="select case when abs>102400 then ‘Warning ' else ' Normal ’ end||' ins '||ins||’ sent '||sent||’ diff '||abs from
(select pg_current_xlog_insert_location() as ins, sent_location assent,abs (pg_xlog location_diff (pg_current_xlog insert_location (), sent_location)) as abs from pg_stat_replication) t:" //如果使用standby，此处才会有信息，若没有使用则无信息
lock="select 'Warning ' ||client_addr||’||usename||’'||datname||’‘||query_start ||’‘||now ()||’‘||query from
pg_stat_activity where waiting and now ()-query_start > interval ' 1 min';" //锁超过1min 则告警xact="select ‘ Warning ' ||client_add||‘||usename||’‘lldatname||’'||xact_start ||’'||now ()||’'||query from
pg_stat_activity where now ()-xact_start > interval ' 1 min ;" //超过1min 则告警。但是 copy语句肯定超过了1min，如果为拷贝则过滤掉pre_xact="select 'Warning '||transaction||’‘||gid||’'||prepared||’‘||owner||’'||database from pg_prepared_xacts where
now ()-prepared > interval ' 1 min’;" //超过1min 则告警
#per database monitor
seq="do language plpgsql \$\$
declare
v_seq name ;
v_max int8 := 0;
v_last int8 := 0;
begin
for v_seq in //序列监控select quote _ident (t2.nspname)|| '.' || quote_ident(t1.relname) from pg_class t1,pg_namespace t2 where
t1.relnamespace=t2.oid and relkind=' S'
loop
execute ' select max_value,last_value from ' ||v_seq into v_max，v_last ;
if v_max-v_last <500000000 then //序列若不是循环使用，那么剩余小于5亿时告警。可以将序列值往回调，但需要注意序列值不会存在冲突
raise notice 'Warning seq %last % max %”, v_seq ,v_last,v_max ;
-- else
--raise notice ' Normal seq % last % max %', v_seq v_last, v_max ;
end if;
end loop ;
end;
\$\$;"idx="select 'Warning '||schemaname||’ ‘||relname||''||index_relname||''||idx_scan from pg_ stat_all_indexes whereIdx_scan<10 and schemaname not in ( pg.catalog ,' pg_toast' ) and indexrelid not in (select conindid from pg constraint where
contype in (‘p’ ,'u' )) and pg_relation size (indexrelid)>65536 order by idx scan"
//如果索引大小>60k并且扫描次数<10，认为该索引无用
eval SQL="$"$1
#echo”$SQL"
DB=$2
res= 'psql -q -A -n -t $DB -c "$SQL” 2>&1
if [ $? -eq 0 ]: then
(echo “$res" |grep “Critical" >/dev/null) && echo "$res" && exit 2
(echo “$res" |grep “Warning" >/dev/null) && echo “$res" && exit 1 //脚本结束前进行一些判断：如果找到 Critical 返回2，如果为 Warning，返回1，其它返回0
echo"Sres" ; exit 0
else
echo “$res" ; exit 3 //如果不返回0，则返回3，获取未知状态
fi
chmod 500 nagios. Sh
使用该脚本后检查输入chmod 500 check_pg.sh
./check_pg.sh arch
./check_pg.sh autovacuum
./check_pg.sh age
皆正常
./check_pg.sh rollback
显示其中一个告警，继续输入 echo $?
返回值为1
再来进行配置
输入#add by digoal
command[check_pg_arch]=/opt/nagios/libexec/check_pg.sh arch
command[check_pg_autovacuum]=/opt/nagios/libexec/check_pg.sh autovacuum
command[check_pg_age]=/opt/nagios/libexec/check_pg.sh age
command[check_pg_conn]=/opt/nagios/libexec/check_pg.sh conn
command[check_pg_rollback]=/opt/nagios/libexec/check_pg.sh rollback
command[check_pg_standby]=/opt/nagios/libexec/check_pg.sh standby
command[check_pg_lock]=/opt/nagios/libexec/check_pg.sh lock
command[check_pg_xact]=/opt/nagios/libexec/check_pg.sh xact
command[check_pg_pre_xact]=/opt/nagios/libexec/check_pg.sh pre_xact
command[check_pg_seq1]=/opt/nagios/libexec/check_pg.sh seq digoal
command[check_pg_seq2]=/opt/nagios/libexec/check_pg.sh seq digoal_01
command[check_pg_seq3]=/opt/nagios/libexec/check_pg.sh seq test
command[check_pg_idx1]=/opt/nagios/libexec/check_pg.sh idx digoal
command[check_pg_idx2]=/opt/nagios/libexec/check_pg.sh idx digoal_01
command[check_pg_idx3]=/opt/nagios/libexec/check_pg.sh idx test
配置好后进行重启，输入
service xinted restart
重启后需要将以上配置添加到服务中，输入
vi service.cfg
输入
define service<
use generic-service
host_name db_3_33
service_description arch
check_command check_nrpe!check_pg_arch
>
define service<
use generic-service
host_name db_3_33
service_description autovacuum
check_command check_nrpe!check_pg_autovacuum
>
define service<
use generic-service
host_name db_3_33
service_description age
check_command check_nrpe!check_pg_age
>
define service<
use generic-service
host_name db_3_33
service_description conn
check_command check_nrpe!check_pg_conn
>
define service<
use generic-service
host_name db_3_33
service_description rollback
check_command check_nrpe!check_pg_rollback
>
define service<
use generic-service
host_name db_3_33
service_description seq1
check_command check_nrpe!check_pg_seq1
>
define service<
use generic-service
host_name db_3_33
service_description seq2
check_command check_nrpe!check_pg_seq2
>
define service<
use generic-service
host_name db_3_33
service_description idx1
check_command check_nrpe!check_pg_idx1
>
define service<
use generic-service
host_name db_3_33
service_description idx2
check_command check_nrpe!check_pg_idx2
>
演示只添加了部分，添加完后进行重启，输入
service nagios restart
此时再来查看
可以看到服务很多。此处 total processes 告警，有189个进程
可以调整阀值，进入配置中修改
/opt/nagios/libexec/check_procs -w 250 -c 300
之后输入service xinetd restart
再来查看，可以看到已经正常
显示为状态信息，如果还需要其它信息，可以使用|分隔开，在输出标准输出时增加一条|即可。
以上就是整个 nagios 服务端、客户端以及自定义脚本如何配置内容。对于自定义脚本存在一个 bucardo 插件，如果了解可以使用进行修改一些内容。不但支持 nagios 还支持其它多种画图工具。
下节讲解第三方插件例如pgstatspack,pg_statsinfo,pgsnap，支持一些图形化的报表输出。
