check_postgres脚本集



一、简介

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

 

二、安装

2.1 编译安装

[root@check_postgres ~]# tar -zxvf check_postgres-2.21.0.tar.gz[root@check_postgres ~]# cd check_postgres-2.21.0 [root@check_postgres check_postgres-2.21.0]# perl Makefile.PL Configuring check_postgres 2.21.0Checking if your kit is complete...Looks goodWriting Makefile for check_postgres [root@check_postgres check_postgres-2.21.0]# makecp check_postgres.pl blib/script/check_postgres.pl/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/check_postgres.plManifying blib/man1/check_postgres.1p [root@check_postgres check_postgres-2.21.0]# make installInstalling /usr/local/share/man/man1/check_postgres.1pInstalling /usr/local/bin/check_postgres.plAppending installation info to /usr/lib64/perl5/perllocal.pod

2.2 生成链接

[root@check_postgres ~]# mkdir -p /opt/check_postgres/bin[root@check_postgres ~]# chown postgres:postgres /opt/check_postgres/ -R[root@check_postgres ~]# su - postgres[postgres@check_postgres ~]$ cd /opt/check_postgres/bin/[postgres@check_postgres bin]$ check_postgres.pl --symlinks[postgres@check_postgres bin]$ lscheck_postgres_archive_ready      check_postgres_hitratio           check_postgres_pgagent_jobs         check_postgres_replicate_rowcheck_postgres_autovac_freeze     check_postgres_hot_standby_delay  check_postgres_pgbouncer_backends   check_postgres_same_schemacheck_postgres_backends           check_postgres_index_size         check_postgres_pgbouncer_checksum   check_postgres_sequencecheck_postgres_bloat              check_postgres_last_analyze       check_postgres_pgb_pool_cl_active   check_postgres_settings_checksumcheck_postgres_checkpoint         check_postgres_last_autoanalyze   check_postgres_pgb_pool_cl_waiting  check_postgres_slony_statuscheck_postgres_cluster_id         check_postgres_last_autovacuum    check_postgres_pgb_pool_maxwait     check_postgres_table_sizecheck_postgres_commitratio        check_postgres_last_vacuum        check_postgres_pgb_pool_sv_active   check_postgres_timesynccheck_postgres_connection         check_postgres_listener           check_postgres_pgb_pool_sv_idle     check_postgres_txn_idlecheck_postgres_custom_query       check_postgres_locks              check_postgres_pgb_pool_sv_login    check_postgres_txn_timecheck_postgres_database_size      check_postgres_logfile            check_postgres_pgb_pool_sv_tested   check_postgres_txn_wraparoundcheck_postgres_dbstats            check_postgres_new_version_bc     check_postgres_pgb_pool_sv_used     check_postgres_versioncheck_postgres_disabled_triggers  check_postgres_new_version_box    check_postgres_prepared_txns        check_postgres_wal_filescheck_postgres_disk_space         check_postgres_new_version_cp     check_postgres_query_runtimecheck_postgres_fsm_pages          check_postgres_new_version_pg     check_postgres_query_timecheck_postgres_fsm_relations      check_postgres_new_version_tnm    check_postgres_relation_size

 

修改环境变量（加入命令路径）：

export PATH=/opt/check_postgres/bin:/opt/pg93/bin:$PATH:$HOME/binexport PGDATA=/opt/pg93/dataexport LD_LIBRARY_PATH=/opt/pg93/lib:$LD_LIBRARY_PATH

 

使修改生效：

[postgres@check_postgres ~]$ source .bash_profile

三、检查涉及的方面

check_postgres涉及方面比较广泛，不仅有常规方面检查，同时也涉及一些主流工具的检查，如：pgbouncer、pgAgent、slony、bucardo等。

archive_ready         - Check the number of WAL files ready in the pg_xlog/archive_status autovac_freeze        - Checks how close databases are to autovacuum_freeze_max_age. backends              - Number of connections, compared to max_connections. bloat                 - Check for table and index bloat. checkpoint            - Checks how long since the last checkpoint cluster_id            - Checks the Database System Identifier commitratio           - Report if the commit ratio of a database is too low. connection            - Simple connection check. custom_query          - Run a custom query. database_size         - Report if a database is too big. dbstats               - Returns stats from pg_stat_database: Cacti output only disabled_triggers     - Check if any triggers are disabled disk_space            - Checks space of local disks Postgres is using. fsm_pages             - Checks percentage of pages used in free space map. fsm_relations         - Checks percentage of relations used in free space map. hitratio              - Report if the hit ratio of a database is too low. hot_standby_delay     - Check the replication delay in hot standby setup index_size            - Checks the size of indexes only. last_analyze          - Check the maximum time in seconds since any one table has been analyzed. last_autoanalyze      - Check the maximum time in seconds since any one table has been autoanalyzed. last_autovacuum       - Check the maximum time in seconds since any one table has been autovacuumed. last_vacuum           - Check the maximum time in seconds since any one table has been vacuumed. listener              - Checks for specific listeners. locks                 - Checks the number of locks. logfile               - Checks that the logfile is being written to correctly. new_version_bc        - Checks if a newer version of Bucardo is available. new_version_box       - Checks if a newer version of boxinfo is available. new_version_cp        - Checks if a newer version of check_postgres.pl is available. new_version_pg        - Checks if a newer version of Postgres is available. new_version_tnm       - Checks if a newer version of tail_n_mail is available. pgagent_jobs          - Check for no failed pgAgent jobs within a specified period of time. pgb_pool_cl_active    - Check the number of active clients in each pgbouncer pool. pgb_pool_cl_waiting   - Check the number of waiting clients in each pgbouncer pool. pgb_pool_maxwait      - Check the current maximum wait time for client connections in pgbouncer pools. pgb_pool_sv_active    - Check the number of active server connections in each pgbouncer pool. pgb_pool_sv_idle      - Check the number of idle server connections in each pgbouncer pool. pgb_pool_sv_login     - Check the number of login server connections in each pgbouncer pool. pgb_pool_sv_tested    - Check the number of tested server connections in each pgbouncer pool. pgb_pool_sv_used      - Check the number of used server connections in each pgbouncer pool. pgbouncer_backends    - Check how many clients are connected to pgbouncer compared to max_client_conn. pgbouncer_checksum    - Check that no pgbouncer settings have changed since the last check. prepared_txns         - Checks number and age of prepared transactions. query_runtime         - Check how long a specific query takes to run. query_time            - Checks the maximum running time of current queries. relation_size         - Checks the size of tables and indexes. replicate_row         - Verify a simple update gets replicated to another server. same_schema           - Verify that two databases have the exact same tables, columns, etc. sequence              - Checks remaining calls left in sequences. settings_checksum     - Check that no settings have changed since the last check. slony_status          - Ensure Slony is up to date via sl_status. table_size            - Checks the size of tables only. timesync              - Compare database time to local system time. txn_idle              - Checks the maximum "idle in transaction" time. txn_time              - Checks the maximum open transaction time. txn_wraparound        - See how close databases are getting to transaction ID wraparound. version               - Check for proper Postgres version. wal_files             - Check the number of WAL files in the pg_xlog directory

 

四、工具使用

参阅http://bucardo.org/check_postgres/check_postgres.pl.html

 

 

五、问题

Q1

错误：

[postgres@check_postgres ~]$ check_postgres_connection -db postgresCannot find Time::HiRes, needed if 'showtime' is true at /opt/check_postgres/bin/check_postgres_connection line 1267.

 

解决办法：

[root@check_postgres ~]# yum install perl-Time-HiRes

​