最全阿里云centos7监控postgres9(1)，软件测试面试题10道

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

       |           |          |             |             | postgres=CTc/postgres  +
       |           |          |             |             | monitoring=c/postgres

zabbix | zabbix | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(7 rows)

wiseucmsg=>

postgres模板下载地址 ： http://pg-monz.github.io/pg\_monz/index-en.html  
 4. 导入监控模板

wget https://codeload.github.com/pg-monz/pg_monz/tar.gz/2.2
[root@localhost ~]# cd pg_monz-2.2
[root@localhost ~/pg_monz-2.2]# ls
LICENSE pg_monz quick-install.txt README-en.md README.md
[root@localhost ~/pg_monz-2.2]# cd pg_monz
[root@localhost ~/pg_monz-2.2/pg_monz]# ls
template usr-local-bin usr-local-etc zabbix_agentd.d
[root@localhost ~/pg_monz-2.2/pg_monz]# cd template
[root@localhost ~/pg_monz-2.2/pg_monz/template]# ls
Template_App_pgpool-II-36.xml Template_App_pgpool-II.xml Template_App_PostgreSQL_SR.xml
Template_App_pgpool-II_watchdog.xml Template_App_PostgreSQL_SR_Cluster.xml Template_App_PostgreSQL.xml
[root@localhost ~/pg_monz-2.2/pg_monz/template]# sz Template_App_PostgreSQL.xml

模板在Github上有：  
 https://github.com/cavaliercoder/libzbxpgsql/tree/master/templates  
 链接：https://pan.baidu.com/s/16226FZ4UZjsSWqoazrzdNQ  
 提取码：cyol  
 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20191126115326664.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTA3OTc3,size_16,color_FFFFFF,t_70)  
 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20191126115425179.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTA3OTc3,size_16,color_FFFFFF,t_70)  
 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20191126115522727.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTA3OTc3,size_16,color_FFFFFF,t_70)  
 {$PG\_CONN} => host=localhost port=54321 user=monitoring connect\_timeout=10


{$PG\_DB} => wiseucmsg


**创建postgres图形**  
 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20191126133746314.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTA3OTc3,size_16,color_FFFFFF,t_70)  
 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20191126133930650.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwOTA3OTc3,size_16,color_FFFFFF,t_70)


### Zabbix 通过shell脚本监控PostgreSQL


授权：

CREATE ROLE zabbix WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE;

GRANT CONNECT ON DATABASE test TO zabbix;

alter user zabbix with password ‘zabbix’;

zabbix客户端配置文件，添加监控项

[root@localhost zabbix]# vim /etc/zabbix/zabbix_agentd.conf
[root@localhost zabbix]# systemctl restart zabbix-agent

UserParameter=pg_chk[*],/etc/zabbix/chk_pg.sh $1 $2

[root@localhost zabbix]# more chk_pg.sh

#shell 参数说明：


$1—>‘totalsize’)  
 $2—>数据库名

#!/bin/bash

by dwh 2018.6.21

case $1 in
‘totalsize’)
psql -h localhost -U zabbix -p 5432 test -t -c “select sum(pg_database_size(datid)) as total_size from pg_stat_database”
;;

‘db_cache’)
db_ca=psql -h localhost -U zabbix -p 5432 test -t -c "select cast(blks_hit/(blks_read+blks_hit+0.000001)\*100.0 as numeric(5,2)) as cache from pg_stat_database where datname = '$2'"
if [[ “$db_ca” == 0 ]];then
echo 0
else
echo $db_ca
fi
;;

‘db_success’)
db_su=psql -h localhost -U zabbix -p 5432 test -t -c "select cast(xact_commit/(xact_rollback+xact_commit+0.000001)\*100.0 as numeric(5,2)) as success from pg_stat_database w here datname = '$2'"
if [[ “$db_su” == 0 ]];then
echo 0
else
echo $db_su
fi
;;

‘server_processes’)
server_p=psql -h localhost -U zabbix -p 5432 test -t -c "select sum(numbackends) from pg\_stat\_database"
if [[ $server_p -eq 0 ]];then
echo 0
else
echo $server_p
fi
;;

‘tx_commited’)
tx_c=psql -h localhost -U zabbix -p 5432 test -t -c "select sum(xact\_commit) from pg\_stat\_database"
if [[ $tx_c -eq 0 ]];then
echo 0
else
echo $tx_c
fi
;;

‘tx_rolledback’)
tx_r=psql -h localhost -U zabbix -p 5432 test -t -c "select sum(xact\_rollback) from pg\_stat\_database"
if [[ $tx_r -eq 0 ]];then
echo 0
else
echo $tx_r
fi
;;

‘db_size’)
db_s=psql -h localhost -U zabbix -p 5432 test -t -c "select pg\_database\_size('$2')" #as size"
if [[ $db_s -eq 0 ]];then
echo 0
else
echo $db_s
fi
;;

‘db_connections’)
db_c=psql -h localhost -U zabbix -p 5432 test -t -c "select numbackends from pg\_stat\_database where datname = '$2'"
if [[ $db_c -eq 0 ]];then
echo 0
else
echo $db_c
fi
;;

‘db_returned’)
db_r=psql -h localhost -U zabbix -p 5432 test -t -c "select tup\_returned from pg\_stat\_database where datname = '$2'"
if [[ $db_r -eq 0 ]];then
echo 0
else
echo $db_r
fi
;;

‘db_fetched’)
db_f=psql -h localhost -U zabbix -p 5432 test -t -c "select tup\_fetched from pg\_stat\_database where datname = '$2'"
if [[ $db_f -eq 0 ]];then
echo 0
else
echo $db_f
fi
;;

‘db_inserted’)
db_i=psql -h localhost -U zabbix -p 5432 test -t -c "select tup\_inserted from pg\_stat\_database where datname = '$2'"
if [[ $db_i -eq 0 ]];then
echo 0
else
echo $db_i
fi
;;

‘db_updated’)
db_u=psql -h localhost -U zabbix -p 5432 test -t -c "select tup\_updated from pg\_stat\_database where datname = '$2'"
if [[ $db_u -eq 0 ]];then
echo 0
else
echo $db_u
fi
;;

‘db_deleted’)
db_d=psql -h localhost -U zabbix -p 5432 test -t -c "select tup\_deleted from pg\_stat\_database where datname = '$2'"
if [[ $db_d -eq 0 ]];then
echo 0
else
echo $db_d
fi
;;

‘db_commited’)
db_co=psql -h localhost -U zabbix -p 5432 test -t -c "select xact\_commit from pg\_stat\_database where datname = '$2'"
if [[ $db_co -eq 0 ]];then
echo 0
else
echo $db_co
fi
;;

‘db_rolled’)
db_ro=psql -h localhost -U zabbix -p 5432 test -t -c "select xact\_rollback from pg\_stat\_database where datname = '$2'"
if [[ $db_ro -eq 0 ]];then
echo 0
else
echo $db_ro
fi
;;

‘version’)

psql -h localhost -U zabbix -p 5432 test -t -c “select version()”

psql --version|head -n1 |awk -F " " ‘{print $3}’
;;
esac

### Zabbix监控TCP连接状态


获取tcp连接数的两种方法：

netstat -n | awk ‘/^tcp/ {++state[$NF]} END {for(key in state) print key,state[key]}’
ss -ant | awk ‘NR>1 {++s[$1]} END {for(k in s) print k,s[k]}’

规范脚本存放目录：





![img](https://img-blog.csdnimg.cn/img_convert/68b95040871678350a022bcbd6fc90d1.png)
![img](https://img-blog.csdnimg.cn/img_convert/7a9ce1319c97d1109571455d4bdafba4.png)
![img](https://img-blog.csdnimg.cn/img_convert/fbc333ef9330ba9ca6a73db6f42560f6.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化的资料的朋友，可以戳这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**

.(img-YNLsjrv7-1715486083360)]

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化的资料的朋友，可以戳这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**