Postgresql监控pgwatch的搭建
一，需要环境：
You will need a handful of components to make this work: - Apache (webserver) #apache搭建web页面 - PHP 5 (scripting language) #php5 - pgsql extension for PHP (see http://www.php.net/manual/en/book.pgsql.php) #php的pgsql的扩展支持 - PostgreSQL 9 (to store the data we collect) #postgresql9 - dblink (contribution module for PostgreSQL 9) #dblink 数据库连接常用软件 - For now we only support PostgreSQL >= 9.0 databases. Older systems cannot be monitored, however, we will add support for future database releases.
我的pgsql是yum安装的，就是为了省事，但是在安装pgwatch环境的时候费了不少劲，主要原因也是我对pgsql不太了解，才接触，所以主要讲一下yum的安装方法，编译的pgsql搭建以上环境很轻松，百度。
1，首先apache的安装一般centos都自带，没有的话yum安装就可以，讲一下遇到的一些问题。
1 [root@wds00 pgwatch-1.0]# vim /etc/httpd/conf/httpd.conf 2 # Deny from all 3 # </LimitExcept> 4 #</Directory> 5 6 # 7 # DirectoryIndex: sets the file that Apache will serve if a directory 8 # is requested. 9 # 10 # The index.html.var file (a type-map) is used to deliver content- 11 # negotiated documents. The MultiViews Option can be used for the 12 # same purpose, but it is much slower. 13 # 14 DirectoryIndex index.php index.html index.html.var #添加index.php索引 15 16 # 17 # AccessFileName: The name of the file to look for in each directory 18 # for additional configuration directives. See also the AllowOverride 19 # directive. 20 # 21 AccessFileName .htaccess 22 23 # 24 # The following lines prevent .htaccess and .htpasswd files from being 25 # viewed by Web clients. 26 # 27 <Files ~ "^\.ht"> 28 Order allow,deny 29 Deny from all 30 Satisfy All 31 </Files> 32 ######################################## 33 # 34 # Dynamic Shared Object (DSO) Support 35 # 36 # To be able to use the functionality of a module which was built as a DSO you 37 # have to place corresponding `LoadModule' lines at this location so the 38 # directives contained in it are actually available _before_ they are used. 39 # Statically compiled modules (those listed by `httpd -l') do not need 40 # to be loaded here. 41 # 42 # Example: 43 # LoadModule foo_module modules/mod_foo.so 44 # 45 LoadModule auth_basic_module modules/mod_auth_basic.so 46 LoadModule auth_digest_module modules/mod_auth_digest.so 47 LoadModule authn_file_module modules/mod_authn_file.so 48 LoadModule authn_alias_module modules/mod_authn_alias.so 49 LoadModule authn_anon_module modules/mod_authn_anon.so 50 LoadModule authn_dbm_module modules/mod_authn_dbm.so 51 LoadModule authn_default_module modules/mod_authn_default.so 52 LoadModule authz_host_module modules/mod_authz_host.so 53 LoadModule authz_user_module modules/mod_authz_user.so 54 LoadModule authz_owner_module modules/mod_authz_owner.so 55 LoadModule authz_groupfile_module modules/mod_authz_groupfile.so 56 LoadModule authz_dbm_module modules/mod_authz_dbm.so 57 LoadModule authz_default_module modules/mod_authz_default.so 58 LoadModule ldap_module modules/mod_ldap.so 59 LoadModule authnz_ldap_module modules/mod_authnz_ldap.so 60 LoadModule include_module modules/mod_include.so 61 LoadModule log_config_module modules/mod_log_config.so 62 LoadModule logio_module modules/mod_logio.so 63 LoadModule env_module modules/mod_env.so 64 LoadModule ext_filter_module modules/mod_ext_filter.so 65 LoadModule mime_magic_module modules/mod_mime_magic.so 66 LoadModule expires_module modules/mod_expires.so 67 LoadModule deflate_module modules/mod_deflate.so 68 LoadModule headers_module modules/mod_headers.so 69 LoadModule usertrack_module modules/mod_usertrack.so 70 LoadModule setenvif_module modules/mod_setenvif.so 71 LoadModule mime_module modules/mod_mime.so 72 LoadModule dav_module modules/mod_dav.so 73 LoadModule status_module modules/mod_status.so 74 LoadModule autoindex_module modules/mod_autoindex.so 75 LoadModule info_module modules/mod_info.so 76 LoadModule dav_fs_module modules/mod_dav_fs.so 77 LoadModule vhost_alias_module modules/mod_vhost_alias.so 78 LoadModule negotiation_module modules/mod_negotiation.so 79 LoadModule dir_module modules/mod_dir.so 80 LoadModule actions_module modules/mod_actions.so 81 LoadModule speling_module modules/mod_speling.so 82 LoadModule userdir_module modules/mod_userdir.so 83 LoadModule alias_module modules/mod_alias.so 84 LoadModule substitute_module modules/mod_substitute.so 85 LoadModule rewrite_module modules/mod_rewrite.so 86 LoadModule proxy_module modules/mod_proxy.so 87 LoadModule proxy_balancer_module modules/mod_proxy_balancer.so 88 LoadModule proxy_ftp_module modules/mod_proxy_ftp.so 89 LoadModule proxy_http_module modules/mod_proxy_http.so 90 LoadModule proxy_ajp_module modules/mod_proxy_ajp.so 91 LoadModule proxy_connect_module modules/mod_proxy_connect.so 92 LoadModule cache_module modules/mod_cache.so 93 LoadModule suexec_module modules/mod_suexec.so 94 LoadModule disk_cache_module modules/mod_disk_cache.so 95 LoadModule cgi_module modules/mod_cgi.so 96 LoadModule version_module modules/mod_version.so 97 LoadModule php5_module modules/libphp5.so #添加php模块 98 ######################################## 99 AddType application/x-httpd-php .php 100 AddType application/x-httpd-php-source .phps #添加php文件
2.安装php支持的postgresql扩展
[root@localhost config]# yum list |grep pgsql php-pgsql.x86_64 5.3.3-27.el6_5.1 @updates apr-util-pgsql.x86_64 1.3.9-3.el6_0.1 base dovecot-pgsql.x86_64 1:2.0.9-7.el6_5.1 updates dspam-pgsql.x86_64 3.10.2-4.el6 epel erlang-pgsql.x86_64 0-6.20101203svn.el6 epel exim-pgsql.x86_64 4.72-6.el6 epel gnokii-smsd-pgsql.x86_64 0.6.30-1.el6 epel libdbi-dbd-pgsql.x86_64 0.8.3-5.1.el6 base libodb-pgsql.i686 2.3.1-1.el6 epel libodb-pgsql.x86_64 2.3.1-1.el6 epel libodb-pgsql-devel.i686 2.3.1-1.el6 epel libodb-pgsql-devel.x86_64 2.3.1-1.el6 epel mod_auth_pgsql.x86_64 2.0.3-10.1.el6 base mydns-pgsql.x86_64 1.2.8.31-1.el6 epel nagios-plugins-pgsql.x86_64 1.4.16-10.el6 epel php-pear-MDB2-Driver-pgsql.noarch 1.5.0-0.8.b4.el6 epel plpgsql_check_93.x86_64 0.9.2-1.rhel6 pgdg93 plpgsql_check_93-debuginfo.x86_64 0.9.2-1.rhel6 pgdg93 root-sql-pgsql.x86_64 5.34.19-1.el6 epel rsyslog-pgsql.x86_64 5.8.10-8.el6 base trytond-pgsql.noarch 1.8.10-1.el6 epel uuid-pgsql.x86_64 1.6.1-10.el6 base zabbix-proxy-pgsql.x86_64 1.8.20-2.el6 epel zabbix-server-pgsql.x86_64 1.8.20-2.el6 epel zabbix-web-pgsql.noarch 1.8.20-2.el6 epel zabbix20-proxy-pgsql.x86_64 2.0.12-2.el6 epel zabbix20-server-pgsql.x86_64 2.0.12-2.el6 epel zabbix20-web-pgsql.noarch 2.0.12-2.el6 epel zabbix22-dbfiles-pgsql.noarch 2.2.1-5.el6 epel zabbix22-proxy-pgsql.x86_64 2.2.1-5.el6 epel zabbix22-server-pgsql.x86_64 2.2.1-5.el6 epel zabbix22-web-pgsql.noarch 2.2.1-5.el6 epel
[root@localhost config]# yum -y install php-pgsql.x86_64
3.安装dblink
在网上找了很久关于yum安装dblink的资料，几乎没有，最后找到debian安装的办法，主要就是一个dblink.sql的文件。 方法如下： [root@wds00 /]# rpm -qa |grep post postgresql92-contrib-9.2.9-1PGDG.rhel6.x86_64 postfix-2.6.6-2.2.el6_1.x86_64 postgresql92-libs-9.2.9-1PGDG.rhel6.x86_64 postgresql92-jdbc-9.2.1002-1PGDG.rhel6.x86_64 postgresql92-9.2.9-1PGDG.rhel6.x86_64 postgresql92-server-9.2.9-1PGDG.rhel6.x86_64 [root@wds00 /]# [root@localhost config]# find / -name dblink* /usr/pgsql-9.2/lib/dblink.so #重要 /usr/pgsql-9.2/share/man/man3/dblink_get_result.3 /usr/pgsql-9.2/share/man/man3/dblink_is_busy.3 /usr/pgsql-9.2/share/man/man3/dblink_exec.3 /usr/pgsql-9.2/share/man/man3/dblink.3 /usr/pgsql-9.2/share/man/man3/dblink_close.3 /usr/pgsql-9.2/share/man/man3/dblink_fetch.3 /usr/pgsql-9.2/share/man/man3/dblink_error_message.3 /usr/pgsql-9.2/share/man/man3/dblink_connect.3 /usr/pgsql-9.2/share/man/man3/dblink_get_notify.3 /usr/pgsql-9.2/share/man/man3/dblink_cancel_query.3 /usr/pgsql-9.2/share/man/man3/dblink_build_sql_update.3 /usr/pgsql-9.2/share/man/man3/dblink_build_sql_delete.3 /usr/pgsql-9.2/share/man/man3/dblink_disconnect.3 /usr/pgsql-9.2/share/man/man3/dblink_get_connections.3 /usr/pgsql-9.2/share/man/man3/dblink_get_pkey.3 /usr/pgsql-9.2/share/man/man3/dblink_build_sql_insert.3 /usr/pgsql-9.2/share/man/man3/dblink_send_query.3 /usr/pgsql-9.2/share/man/man3/dblink_open.3 /usr/pgsql-9.2/share/man/man3/dblink_connect_u.3 /usr/pgsql-9.2/share/extension/dblink.control /usr/pgsql-9.2/share/extension/dblink--unpackaged--1.0.sql /usr/pgsql-9.2/share/extension/dblink--1.0.sql #重要 [root@localhost config]# [root@wds00 pgwatch-1.0]# cat /usr/pgsql-9.2/share/extension/dblink--1.0.sql |psql -U postgres -h 192.168.102.43 Use "CREATE EXTENSION dblink" to load this file. 登陆数据库 执行CREATE EXTENSION dblink；成功
postgres=# CREATE EXTENSION dblink;
CREATE EXTENSION
postgres=# select extname,extversion from pg_extension;
extname | extversion
---------+------------
plpgsql | 1.0
dblink | 1.0
(2 行记录)
二，下载pgwatch安装包1.0版本。
1 wget http://www.cybertec.at/download/pgwatch/pgwatch-1.0.tar.gz -O /usr/loca/src/pgwatch-1.0.tar.gz 2 3 cd /usr/local/src/ 4 5 tar zxvf /usr/loca/src/pgwatch-1.0.tar.gz #解压 6 7 [root@wds00 src]# ls 8 pgwatch-1.0 pgwatch-1.0.tar.gz
三，修改配置文件
由于之前网上一哥们（玩命写博客）的文章给了我们很大帮助，但是他搭建的时候pgsql的版本还比较低，所以有些问题没有遇到，现在pgsql大家都用version2.0 或者version3.0版本的，所以在安装上都会遇到不出图的问题，我也一样，下面仔细说说原因与解决办法。
1，配置文件
[root@wds00 config]# pwd /var/www/html/config [root@wds00 config]# ls config.ini config.ini.template fetch_8_4.xml fetch_9_0.xml fetch_9_1.xml fetch_9_2.xml #模板文件用来生成config.ini 的，里面版本就没有9.1以上的，所以生成出来的配置文件也不能有9.1以上版本的xml文件了，里面的所有xml文件都是一个软连接，指向fetch_8_4.xml。
[root@wds00 config]# ll
总用量 32
-rw-r--r-- 1 apache apache 2289 9月 15 19:14 config.ini
-rw-r--r-- 1 apache apache 2313 9月 15 19:03 config.ini.template
-rw-r--r-- 1 apache apache 23351 9月 15 16:59 fetch_8_4.xml
lrwxrwxrwx 1 apache apache 13 9月 15 17:42 fetch_9_0.xml -> fetch_8_4.xml
lrwxrwxrwx 1 apache apache 13 9月 15 17:42 fetch_9_1.xml -> fetch_8_4.xml
lrwxrwxrwx 1 apache apache 13 9月 15 19:16 fetch_9_2.xml -> fetch_8_4.xml
[root@wds00 config]#cat config.ini.template
; When during synchronization a daily aggregation also happens for tables and indexes info. ; This variable tells the system what length of interval to reprocess. ; Must be at least 1 hour, but to avoid "holes" in data due to any syytem halts ; it's better to choose a bigger interval. sync_agg_reprocess_interval = "2 days" ; allowed pg versions (dropdown in configure menu) allowed_pg_versions[] = "8.4" allowed_pg_versions[] = "9.0" allowed_pg_versions[] = "9.1" allowed_pg_versions[] = "9.2" #我自己后加的 ; Allow caching dbrow objects for acceleration purposes, but needs more memory allow_cache = true
###########################################################################
修改 fetch_8_4.xml，重中之重，不然你可能会sql查询报错，原因就是由于pgsql8.4与9.2 9.3这类版本不一样导致的，里面的一个 pg_stat_activity数据列名称不一样。我的是pgsql9.2
pgwatch主要就是查这个表然后统计数据在显示的，如果你连查表都出问题，更别说显示了。
feeb=# \d pg_stat_activity
View "pg_catalog.pg_stat_activity"
Column | Type | Modifiers
------------------+--------------------------+-----------
datid | oid |
datname | name |
pid | integer |
usesysid | oid |
usename | name |
application_name | text |
client_addr | inet |
client_hostname | text |
client_port | integer |
backend_start | timestamp with time zone |
xact_start | timestamp with time zone |
query_start | timestamp with time zone |
state_change | timestamp with time zone |
waiting | boolean |
state | text |
query | text |
##########################################
看一下fetch_8_4.xml文件里面怎么写的
<query debug="true" log="Retrieving t_stat_activity">
INSERT INTO raw_data.t_stat_activity
(tstamp, node_id, database_id, datid, datname, procpid, usesysid, usename, current_query,
waiting, xact_start, query_start, backend_start, client_addr, client_port)
SELECT '{{input:now}}', '{{input:node_id}}', '{{input:db_id}}', *
FROM dblink('{{input:link}}', 'SELECT datid, datname, procpid, usesysid, usename, current_query,
waiting, xact_start, query_start, backend_start, client_addr, client_port
FROM pg_catalog.pg_stat_activity WHERE datname=''{{input:dbname}}''
AND current_query!=''<IDLE>'' AND procpid!=pg_backend_pid()')
AS (datid oid, datname name, procpid integer, usesysid oid, usename name,
current_query text, waiting boolean, xact_start timestamp, query_start timestamp,
backend_start timestamp, client_addr inet, client_port integer)
我简单标红了几个地方，具体的我也不找了，我直接把正确的复制过来，如下：
<?xml version='1.0' standalone='yes'?>
<queries>
<query debug="true" log="Retrieving t_stat_activity">
INSERT INTO raw_data.t_stat_activity
(tstamp, node_id, database_id, datid, datname, procpid, usesysid, usename, current_query,
waiting, xact_start, query_start, backend_start, client_addr, client_port)
SELECT '{{input:now}}', '{{input:node_id}}', '{{input:db_id}}', *
FROM dblink('{{input:link}}', 'SELECT datid, datname, pid, usesysid, usename, query,
waiting, xact_start, query_start, backend_start, client_addr, client_port
FROM pg_catalog.pg_stat_activity WHERE datname=''{{input:dbname}}''
AND query!=''<IDLE>'' AND pid!=pg_backend_pid()')
AS (datid oid, datname name, pid integer, usesysid oid, usename name,
query text, waiting boolean, xact_start timestamp, query_start timestamp,
backend_start timestamp, client_addr inet, client_port integer)
</query>
###########################################################################
2,php文件
[root@wds00 classes]# pwd /var/www/html/classes [root@wds00 classes]# ls auth.php config_node.php database.php env.php logging.php pgwatch_smarty.php validator.php yui_datatable.php chart.php configure.php dates.php FusionCharts_Gen.php log.php pgwatch_xml.php xmlpage.php config_database.php dashboard.php dbrow.php FusionCharts.php pgwatch_database.php sql_worksheet.php xmlpageset.php
###################################
[root@wds00 classes]# cat config_database.php
// collect raw data from remote database
public function sync($now){
$node = $this->get_node();
new log("NOTICE", "Synchronizing database " . $this->database_name);
$version = preg_replace('/^(\d+\.\d+).*/', '\1', $node->version);
switch ($version){
case "8.4":
$xml_file = "config/fetch_8_4.xml";
break;
case "9.0":
$xml_file = "config/fetch_9_0.xml";
break;
case "9.1":
$xml_file = "config/fetch_9_1.xml";
break;
case "9.2": #此处自行添加。
$xml_file = "config/fetch_9_2.xml";
break;
三，完成以上修改之后就可以运行setup.php了，生成配置文件，配置数据库链接，添加计划任务定时收集数据。
0 * * * * cd/var/www/html/util && /usr/bin/php getraw_d.php
感谢QQ好友-等等路人（370508695），欢迎交流，如有错误希望能留言指出，我会及时修改，谢谢支持。
请珍惜别人的劳动转载请指明出处谢谢
官网监控推荐：http://wiki.postgresql.org/wiki/Monitoring
参考博客：http://blog.csdn.net/lengzijian/article/details/8127207
