当前位置: 首页 > 工具软件 > Pgwatch2 > 使用案例 >

Postgresql监控pgwatch的搭建

宰坚
2023-12-01

一,需要环境:

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!=''&lt;IDLE&gt;'' 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!=''&lt;IDLE&gt;'' 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

转载于:https://www.cnblogs.com/weiji100/p/3974582.html

 类似资料: