PostgreSQL的监控二(pgwatch & pg_statsinfo)

2.3 pgwatch

http://www.cybertec.at/postgresql_produkte/pgwatch-cybertec-enterprise-postgresql-monitor/
主要特性：
- 配置简单
- 大量的监控图表
- 快速系统检查面板
- 自动收集统计信息
- 交互式的 Flash 图表
- 集成 sql worksheet

pgwatch的监控画面

pgwatch是个PHP做的web管理控制台，后台存储使用Postgresql 数据库，其工作的原理是利用cron定期经由dblink采集远程数据库性能数据，并以图形的形式显示。使用方法也很简单，只要把pgwatch配到Apache上，第一次访问的时候进入setup.PHP,引导设置pgwatch使用的后台数据库。然后再配置定期调用采集数据脚本的daemon，再在配置页面里填上监控目标机器的连接信息就可以了。

 
  Requirements:
 -------------
 
 You will need a handful of components to make this work:
 
    - Apache (webserver)
    - PHP 5 (scripting language)
    - pgsql extension for PHP (see http://www.PHP.net/manual/en/book.pgsql.PHP)
    - Postgresql 9 (to store the data we collect)
    - dblink (contribution module for Postgresql 9)
 
    - For now we only support Postgresql >= 9.0 databases.
     Older systems cannot be monitored,however,we will
     add support for future database releases.
 
 
 How to set it up:
 -----------------
 
    - Extract your package to your Apache directory
 
    - Adjust ownership of your application if www-data is improper
 
    - Open in browser the setup.PHP. It will lead you through the setup process.
 
    - You can choose setup.sh in linux and setup.bat in windows as alternatives.
 
    - Start the data collection daemon by adding the proper row to cron,something like:
        0 * * * * cd /var/www/pgwatch/util && /usr/bin/PHP5 getraw_d.PHP
        (But setup.sh will also remind you of it)
 
    - Log into the website and click "configure" to add new database servers
     / databases to make sure that your daemon will pick up the statistics.
 
 

然而实际配置时发现，收集数据的地方出错。

[root@zabbix util]#/usr/bin/PHP getraw_d.PHP

256:pgwatch_xml:given parameter isnota valid xml.in/var/www/html/pgwatch/classes/pgwatch_xml.PHP(18)<br/>

2.4 pg_statsinfo

Statistics Snapshot

pg_statsinfo gathers statistics periodically and stores them as snapshots into a repository database. The repository can be in the same database with the monitored instance or in another instance. Also,one repository can store snapshots from multiple monitored instances.

Size of a snapshot depends on the numbers of objects in DB. There are about 600 - 800kB per snapshot. In case of pg_statsinfo default settings,snapshots for each monitored DB requires 90 - 120MB per day.

Note that pg_statsinfo doesn't delete old snapshots. Please delete them manually.

Server Log Filter

Alert Function

If database statistics is over threshold which was set by user,pg_statsinfo detect and write alert log in postgresql-log(message level is 'ALERT').

(*1) Correlation of table is judged by only clustered table which is in cluster index.