check_postgres 检测数据库的健康并报告异常情况

os: centos 7.4
postgresql: 10.4
check_postgres: 2.24.0

check_postgres is a script for monitoring various attributes of your database. It is designed to work with Nagios, MRTG, or in standalone scripts

安装

# yum install perl-DBI perl-DBD-Pg
# su - postgres

$ cd /tmp
$ git clone https://github.com/bucardo/check_postgres.git
$ cd check_postgres/
$ perl Makefile.PL
$ make
$ LC_ALL=C make test PGBINDIR=/usr/pgsql-10/bin

# make install
Manifying blib/man1/check_postgres.1p
Installing /usr/local/share/man/man1/check_postgres.1p
Installing /usr/local/bin/check_postgres.pl
Appending installation info to /usr/lib64/perl5/perllocal.pod

$ which check_postgres.pl
/usr/local/bin/check_postgres.pl
$ man check_postgres

# cd /usr/local/bin/
# check_postgres.pl --symlinks

使用

$ check_postgres.pl --action=connection --db=peiybdb
POSTGRES_CONNECTION OK: DB "peiybdb" version 10.4 | time=0.14s 

$ check_postgres_locks --warning=100 --critical="total=200:exclusive=20"
POSTGRES_LOCKS OK: DB "postgres" total=1  | time=0.00s peiybdb.total=0;100;200 postgres.total=1;100;200 template1.total=0;100;200 trade.total=0;100;200 

$ check_postgres_txn_idle --port=5432 --warning='+50' --critical='5 for 10 seconds'
POSTGRES_TXN_IDLE OK: DB "postgres" no idle in transaction | time=0.54s transaction_time=0;;10 

分析下 check_postgres_locks 具体查询细节

$ vi /usr/local/bin/check_postgres.pl

sub check_locks {

    ## Check the number of locks
    ## Supports: Nagios, MRTG
    ## By default, checks all databases
    ## Can check specific databases with include
    ## Can ignore databases with exclude
    ## Warning and critical are either simple numbers, or more complex:
    ## Use locktype=number:locktype2=number
    ## The locktype can be "total", "waiting", or the name of a lock
    ## Lock names are case-insensitive, and do not need the "lock" at the end.
    ## Example: --warning=100 --critical="total=200;exclusive=20;waiting=5"


$SQL = q{SELECT granted, mode, datname FROM pg_locks l RIGHT JOIN pg_database d ON (d.oid=l.database) WHERE d.datallowconn};


 define command {
     command_name    check_postgres_locks
     command_line    $USER2$/check_postgres.pl -H $HOSTADDRESS$ -u pgsql -db postgres --action locks -w $ARG1$ -c $ARG2$
 }

集成化脚本。

参考：
http://postgres.cn/docs/10/maintenance.html

https://bucardo.org/check_postgres/
https://bucardo.org/check_postgres/check_postgres.pl.html

http://bucardo.org/wiki/Check_postgres

https://github.com/bucardo/check_postgres/

https://github.com/bucardo/check_postgres/blob/master/README.dev