- 浏览: 243006 次
最新评论
pgbadger监控安装和使用
https://github.com/dalibo/pgbadger/tree/v3.3 --安装包的下载地址,可以随意选择自己想要的版本
1、解压下载的安装包
drwxr-xr-x 3 root root 4096 May 5 08:19 pgbadger-5.1 --解压出来的目录
-rw-r--r-- 1 root root 275644 Sep 3 03:21 pgbadger-5.1.zip
2、安装
[root@localhost ~]# cd pgbadger-5.1
[root@localhost pgbadger-5.1]# ll
total 1004
-rw-r--r-- 1 root root 55530 May 5 08:19 ChangeLog
-rw-r--r-- 1 root root 365 May 5 08:19 CONTRIBUTING.md
drwxr-xr-x 2 root root 4096 May 5 08:19 doc
-rw-r--r-- 1 root root 903 May 5 08:19 LICENSE
-rw-r--r-- 1 root root 1399 May 5 08:19 Makefile.PL
-rw-r--r-- 1 root root 81 May 5 08:19 MANIFEST
-rw-r--r-- 1 root root 334 May 5 08:19 META.yml
-rw-r--r-- 1 root root 873457 May 5 08:19 pgbadger
-rw-r--r-- 1 root root 22729 May 5 08:19 README
[root@localhost pgbadger-5.1]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for pgBadger
[root@localhost pgbadger-5.1]# make
cp pgbadger blib/script/pgbadger
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/pgbadger
Manifying blib/man1/pgbadger.1
[root@localhost pgbadger-5.1]# make install
Installing /usr/share/man/man1/pgbadger.1
Installing /usr/bin/pgbadger
Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/pgBadger/.packlist
Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod
3、查看安装版本
[root@testos ]# which pgbadger
/usr/bin/pgbadger
[root@testos ~]# pgbadger -V
pgBadger version 5.1
4、设置postgresql.conf
log_line_prefix = '%t-%d-%h-%a :'
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
5、网页查看
http://192.168.10.173/out.html --暂时打不开,还有没装完的。
6、安装httpd、php
yum install httpd ---要先自己配置yum,才能使用yum install 进行安装
chkconfig httpd on --添加到自动启动
service httpd start
yum install php
/var目录下就多了www目录
[root@localhost www]# ll
total 32
drwxr-xr-x 2 root root 4096 Jul 15 2009 cgi-bin
drwxr-xr-x 3 root root 4096 Sep 3 03:39 error
drwxr-xr-x 2 root root 4096 Jul 15 2009 html
drwxr-xr-x 3 root root 4096 Sep 3 03:39 icons
[root@localhost www]# pwd
/var/www
7、在html下新建php文件,并测试http://192.168.10.173:/test.php能否正常打开
网页显示如下:
<?
phpinfo();
?>
---显示图形化界面才是正确的,而不是只读取文本内容
8、安装apache
>tar -zxvf httpd-2.2.11.tar.gz
>cd httpd-2.2.11
>./configure --prefix=/usr/local/apache
make
make install
9、重启apache服务,再次验证
vi /usr/local/apache/conf/httpd.conf
找到"DirectoryIndex index.html在index.html 前添加 test.php
service httpd restart
http://192.168.10.173:/test.php --可以看到图形化界面
10、想办法把pg监控的log显示到这个界面
vi /etc/httpd/conf/httpd.conf 修改两处
DocumentRoot "/var/www/" --这样就可以读取www下面所有的目录和文件,因为我在/var/www下面新建了一个pgbadger子目录,out.html就放在下面
如果不修改设置,就会提示找不到/pgbadger/out.html
11、尝试手动执行
cd /usr/bin
[root@localhost bin]# pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.csv -o /var/www/pgbadger/out.html
Can't locate Text/CSV_XS.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at /usr/bin/pgbadger line 1583.
---看样子应该是没办法读取csv格式的文件
修改配置文件,将日志文件改成log
log_destination = 'csvlog' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
# These are only used if logging_collector is on:
log_directory = '/pg/pg_log1' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
12、再次执行
[root@localhost bin]# pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.log -o /var/www/pgbadger/out.html
FATAL: unable to detect log file format from /pg/pg_log1/postgresql-2014-09-03_193449.log, please use -f option. ---无法检测格式
需要指定格式
[root@localhost pg_log1]# pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.csv -o /var/www/pgbadger/out.html -f csv --报错
Can't locate Text/CSV_XS.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at /usr/bin/pgbadger line 1583.
[root@localhost pg_log1]# pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.csv -o /var/www/pgbadger/out.html -f stderr --正确
[========================>] Parsed 441 bytes of 441 (100.00%), queries: 0, events: 0
或者
pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.log -o /var/www/pgbadger/out.html -f csvlog
13、验证成功
http://192.168.12.173:/pgbadger/out.html
可以打开监控页面,但是完全无数据
[root@localhost bin]# pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.log -o /var/www/pgbadger/out.html -不添加格式后,又可以了
[========================>] Parsed 4303 bytes of 4303 (100.00%), queries: 8, events: 6
LOG: Ok, generating html report...
--Ok,搞定,可以在网页上面看到监控的数据了。
14、定期执行命令
[root@localhost ~]# cat run.sh
#!/bin/bash
/usr/bin/pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.log -o /var/www/pgbadger/out.html
vi /etc/crontab
0 */1 * * * root /root/run.sh
============================再次安装pgbadger====================
[root@pg pgbadger]# find / -name "pgbadger"
/var/www/html/pgbadger
/var/www/html/pgbadger/blib/script/pgbadger
/var/www/html/pgbadger/pgbadger
/usr/local/bin/pgbadger
[root@pg pgbadger]# cat run.sh
#!/bin/bash
/usr/local/bin/pgbadger --prefix '%t-%d-%h-%a :' /home/pgsql/9.1/data/pg_log/postgresql-201* -o /var/www/html/pgbadger/out.html
1、下载
https://github.com/dalibo/pgbadger
https://github.com/dalibo/pgbadger/releases
2、安装 --192.168.12.29
[root@antiywh-5NMQMH1 tmp]# tar -zxvf pgbadger-7.1.tar.gz
pgbadger-7.1/
pgbadger-7.1/.gitignore
pgbadger-7.1/.perltidyrc
pgbadger-7.1/CONTRIBUTING.md
pgbadger-7.1/ChangeLog
pgbadger-7.1/LICENSE
pgbadger-7.1/MANIFEST
pgbadger-7.1/META.yml
pgbadger-7.1/Makefile.PL
pgbadger-7.1/README
pgbadger-7.1/doc/
pgbadger-7.1/doc/pgBadger.pod
pgbadger-7.1/pgbadger
pgbadger-7.1/tools/
pgbadger-7.1/tools/README
pgbadger-7.1/tools/pgbadger_tools
[root@antiywh-5NMQMH1 pgbadger-7.1]# make && make install --直接安装报错
make: *** 没有指明目标并且找不到 makefile。 停止。
[root@antiywh-5NMQMH1 pgbadger-7.1]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for pgBadger
[root@antiywh-5NMQMH1 pgbadger-7.1]# make && make install
cp pgbadger blib/script/pgbadger
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pgbadger
Manifying blib/man1/pgbadger.1p
Installing /usr/local/share/man/man1/pgbadger.1p
Installing /usr/local/bin/pgbadger
Appending installation info to /usr/lib64/perl5/perllocal.pod
root@antiywh-5NMQMH1 pgbadger-7.1]# which pgbadger
/usr/local/bin/pgbadger
[root@antiywh-5NMQMH1 ~]# pgbadger -V
pgBadger version 7.1
3、安装php和httpd依赖包
yum install -y httpd httpd-devel php php-devel
chkconfig httpd on
[root@antiywh-5NMQMH1 ~]# service httpd start
正在启动 httpd:httpd: Could not reliably determine the server's fully qualified domain name, using 220.250.64.18 for ServerName
[确定]
[root@antiywh-5NMQMH1 data]# service httpd restart
停止 httpd: [确定]
正在启动 httpd: [确定]
4、安装apache
>tar -zxvf httpd-2.2.11.tar.gz
>cd httpd-2.2.11
>./configure --prefix=/usr/local/apache
make && make install
5、修改一些pg库的参数
logging_collector = on
#log_destination = 'stderr'
#log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_line_prefix = '%t-%d-%h-%a :'
6、修改参数
vi /etc/httpd/conf/httpd.conf
DocumentRoot "/var/www/" --这样就可以读取www下面所有的目录和文件,因为我在/var/www下面新建了一个pgbadger子目录,out.html就放在下面
如果不修改设置,就会提示找不到/pgbadger/out.html
ServerName 127.0.0.1:80
<Directory "/var/www/pgbadger"> --指定具体目录
Options Indexes FollowSymLinks
Order allow,deny
Allow from all
AllowOverride All
</Directory>
vi /usr/local/apache/conf/httpd.conf
写一个网页验证:
[root@antiywh-5NMQMH1 pgbadger]# pwd
/var/www/pgbadger
[root@antiywh-5NMQMH1 pgbadger]# cat test.php
<?php
echo "tina's website";
?>
7、重启apache服务,再次验证
service httpd restart
http://192.168.12.29:/pgbadger/test.php
若是启动报错,说端口占用,就先kill掉
[root@antiywh-5NMQMH1 html]# netstat -lnp|grep 80
tcp 0 0 :::80 :::* LISTEN 16634/httpd
[root@antiywh-5NMQMH1 html]# ps 16634
PID TTY STAT TIME COMMAND
16634 ? Ss 0:00 /usr/local/apache/bin/httpd -k start
kill -9 16634
8、启动pgbadger的
/usr/local/bin/pgbadger --prefix '%t-%d-%h-%a :' /home/pgsql/9.1/data/pg_log/postgresql-201* -o /var/www/html/pgbadger/out.html
-----------
[root@localhost mha4mysql-node-0.52]# perl Makefile.PL;make;make install
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Makefile.pm line 4.
BEGIN failed--compilation aborted at inc/Module/Install/Makefile.pm line 4.
Compilation failed in require at inc/Module/Install.pm line 307.
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Can.pm line 6.
BEGIN failed--compilation aborted at inc/Module/Install/Can.pm line 6.
Compilation failed in require at inc/Module/Install.pm line 307.
Can't locate ExtUtils/MM_Unix.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Metadata.pm line 316.
make: *** 没有指明目标并且找不到 makefile。 停止。
make: *** 没有规则可以创建目标“install”。 停止。
解决办法: yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
CPAN(Comprehensive Perl Archive Network)是internet上Perl模块最大的集散地,包含了现今公布的几乎所有的perl模块,
通过上面的例子,可以获知利用这个格式,可以到CPAN上安装需要的模块。
到CPAN的官方站点下载CPAN模块
http://search.cpan.org/~andk/CPAN-1.9301/lib/CPAN.pm
解压,编绎,安装
$tar zxvf CPAN-1.9301.tar.gz
$cd CPAN-1.9301
$perl Makefile.PL
ImportingPAUSE public key into your GnuPG keychain... done!
(Youmay wish to trust it locally with 'gpg --lsign-key 450F89EC')
WARNING:SIGN is not a known parameter.
Checkingif your kit is complete...
Looksgood
Warning:prerequisite Test::Harness 2.62 not found. We have 2.56.
Warning:prerequisite Test::More 0 not found.
'SIGN'is not a known MakeMaker parameter name.
WritingMakefile for CPAN
http://search.cpan.org/~andk/
CPAN-2.10.tar.gz
[root@pgtina CPAN-2.10]# perl Makefile.PL
Importing PAUSE public key into your GnuPG keychain... gpg: new configuration file `/root/.gnupg/gpg.conf' created
gpg: WARNING: options in `/root/.gnupg/gpg.conf' are not yet active during this run
done!
(You may wish to trust it locally with 'gpg --lsign-key 450F89EC')
Checking if your kit is complete...
Looks good
Warning: prerequisite CPAN::Meta::Requirements 2.121 not found.
Warning: prerequisite HTTP::Tiny 0.005 not found.
Warning: prerequisite Test::More 0 not found.
Writing Makefile for CPAN
make && make install
依然去http://search.cpan.org/~andk/ 查找这个CPAN::Meta::Requirements HTTP::Tiny
装了两个之后再检测就差一个
[root@pgtina CPAN-2.10]# perl Makefile.PL
Importing PAUSE public key into your GnuPG keychain... done!
(You may wish to trust it locally with 'gpg --lsign-key 450F89EC')
Warning: prerequisite Test::More 0 not found.
Writing Makefile for CPAN
yum install perl-Time-HiRes
http://192.168.12.29/pgbadger/out.html 7.1版本的
http://192.168.12.250/pgbadger/out.html 3.3版本的 这个版本的看着舒服些
---前段部分我是个外行,只能简单的实现下我想要的效果~大家可以忽略以下内容----------
怎么添加首页面
[root@pg html]# pwd
/var/www/html
[root@pg html]# cat index.php
<html>
<head>
<title>数据库监控工具</title>
</head>
<body>
<br/></br/>
<h1 align='center'>欢迎使用数据库监控系统</h1>
<p align='center'>
<a href='pgbadger/out.html'>主库日志监控(20服务器)</a> <br/>
<br/>
<a href='pgbadger/out_18.html'>从库日志监控(18服务器)</a>
</p>
<p align='center'>
<font color='red'>备注:日志分析结果2小时更新一次</font>
</p>
<p>
20数据库运维日志<br>
<?php
$file = fopen("log.txt","r");
while(!feof($file))
{
echo fgets($file). "<br />";
}
fclose($file);
?>
</p>
</body>
</html>
[root@antiywh-5NMQMH1 www]# vi getdata.sh
#!/bin/bash
su - postgres -c "psql -d tm_samples" <<EOF
\o /var/www/html/log.txt
SELECT * FROM (
SELECT RELNAME ,pg_relation_size(RELNAME::regclass )/1024/1024 tabMB FROM PG_STAT_USER_TABLES
)AA where tabMB > 10
ORDER BY tabMB DESC;
\q
EOF
echo "日志文件数(保存四天日志):" >> /var/www/html/log.txt
ls /home/pgsql/9.1/data/pg_log/|wc -l >> /var/www/html/log.txt
尽量配置到/var/www/html目录下
[root@pgtina html]# ll
total 24
-rwxr-xr-x. 1 root root 376 Dec 7 10:57 getdata.sh
-rw-r--r--. 1 root root 525 Dec 7 10:21 index.php
-rw-rw-r--. 1 postgres postgres 6691 Dec 7 10:57 log.txt
drwxrwxrwx. 2 root root 4096 Dec 7 10:10 pgbadger
-rw-r--r--. 1 root root 32 Dec 4 15:48 test.php
[root@pgtina html]#
/////////////////////////////////////////////////////////////新的监控页面(表大小+表行数)///////////////////////////////////////////////////
如果在字符才中有双引号的话,可以使用反斜杠"\"来进行转义
10 */2 * * * root sh /tmp/run.sh
pgbadger监控页面的数据
[root@antiywh-5NMQMH1 pg_log]# cat /tmp/run.sh
#!/bin/bash
/usr/local/bin/pgbadger --prefix '%t-%d-%h-%a :' /home/pgsql/9.1/data/pg_log/postgresql-201* -o /var/www/html/pgbadger/out.html
[root@pgtina html]# pwd
/var/www/html
[root@pgtina html]# ll
total 24
-rw-r--r--. 1 root root 172 Dec 8 15:08 db_jk.log
drwxr-xr-x. 2 root root 4096 Dec 8 15:18 file
-rwxrwxrwx. 1 root root 2741 Dec 8 15:39 getdata.sh
-rwxr-xr-x. 1 root root 864 Dec 8 14:18 index.php
drwxrwxrwx. 2 root root 4096 Dec 8 14:10 pgbadger
-rw-r--r--. 1 root root 32 Dec 4 15:48 test.php
主页面(展示数据库的名字跟表个数),子页面(表大小+表行数)
[root@pgtina html]# cat getdata.sh
#!/bin/bash
#2015-12-8 tina
date=`date +"%Y-%m-%d %H:%M:%S"`
rm -f /var/www/html/db_jk.log
rm -f /var/www/html/file/*.php
rm -f /var/www/html/file/*.txt
rm -f /tmp/pg250_*.log
num1=`ls /home/pgsql/9.1/data/pg_log/|wc -l`
databases=$(psql -U postgres -c "select datname from pg_database where datname not like 'temp%' and datname not like 'postgres' order by datname;" |grep -v "datname" |grep -v "row"|grep -v "-")
for db in $databases
do
num2=$(psql -U postgres -d $db -c "select count(*) from pg_tables where schemaname='public';"|grep -v "count" |grep -v "row"|grep -v "-")
echo "$db $num2">>/var/www/html/db_jk.log
done
for db in $databases
do
tables=$(psql -U postgres -d $db -c "select tablename from pg_tables where schemaname='public' order by tablename;"|grep -v "tablename" |grep -v "row"|grep -v "\-")
for table in $tables
do
echo $table >>/tmp/pg250_$db.log
psql -U postgres -d $db -c "select count(*) from $table;" |grep -v "count" |grep -v "row"|grep -v "\-">>/tmp/pg250_$db.log
done
cat /tmp/pg250_$db.log|awk 'NF==1{printf "%s ", $1;next}1'>>/var/www/html/file/$db.txt
echo "<html>" >/var/www/html/file/$db.php
echo "<body>">>/var/www/html/file/$db.php
echo '<div style="position:absolute;width:100%;height:100%;z-index:-1">
<img src="./3.jpg" width="100%" height="100%" />
</div>' >>/var/www/html/file/$db.php
echo "<br/></br/>" >>/var/www/html/file/$db.php
echo "<h1 align='center'>欢迎使用数据库监控系统</h1>" >>/var/www/html/file/$db.php
echo "最近一次更新时间:$date <br/>">>/var/www/html/file/$db.php
echo "<br/></br/>" >>/var/www/html/file/$db.php
echo "日志文件数(保存四天日志):$num1" >>/var/www/html/file/$db.php
echo "<br/></br/>">>/var/www/html/file/$db.php
echo "表名 表大小(mb):<br/>" >>/var/www/html/file/$db.php
data=$(psql -U postgres -d $db -c "SELECT * FROM (SELECT RELNAME ,pg_relation_size(RELNAME::regclass )/1024/1024 tabMB FROM PG_STAT_USER_TABLES )AA ORDER BY tabMB DESC;"|grep -v "relname" |grep -v "-"| awk '{print $0,"<br/>"}')
echo $data >> /var/www/html/file/$db.php
echo "<br/>">>/var/www/html/file/$db.php
echo "表名 表行数:" >>/var/www/html/file/$db.php
echo "<br/>">>/var/www/html/file/$db.php
echo "<?php" >>/var/www/html/file/$db.php
echo "\$file = fopen(\"/var/www/html/file/$db.txt\",\"r\");" >>/var/www/html/file/$db.php
echo "while(!feof($file))" >>/var/www/html/file/$db.php
echo "{" >>/var/www/html/file/$db.php
echo 'echo fgets($file). "<br />";' >>/var/www/html/file/$db.php
echo "}" >>/var/www/html/file/$db.php
echo "fclose($file);" >>/var/www/html/file/$db.php
echo "?>" >>/var/www/html/file/$db.php
echo "</body>" >>/var/www/html/file/$db.php
echo "</html>" >>/var/www/html/file/$db.php
done
[root@pgtina html]# cat index.php
<html>
<head>
<title>数据库监控工具</title>
</head>
<body>
<div style="position:absolute;width:100%;height:100%;z-index:-1">
<img src="./file/4.jpg" width="100%" height="100%" />
</div>
<br/></br/>
<h1 align='center'>欢迎使用数据库监控系统</h1>
<p align='center'>
<a href='pgbadger/out.html'>主库日志监控(250测试服务器)</a> <br/>
<br/>
</p>
<p align='center'>
<font color='red'>备注:日志分析结果2小时更新一次</font>
</p>
服务器IP:192.168.12.250
<br/></br/>
服务器角色:测试库
<br/></br/>
数据库列表(数据库名 表个数):
<br/>
<?php
$file = fopen("/var/www/html/db_jk.log","r");
while(!feof($file))
{
$str = trim(fgets($file));
$arr = strstr($str,' ',TRUE);
echo '<a href="/file/'.$arr.'.php">'. $str. "</a><br />";
}
fclose($file);
?>
</p>
</body>
</html>
[root@pgtina html]# cat db_jk.log
devops 1
dw_report 16
dynfarming 6
engine 15
mongo_statistic 5
t_url 4
test 11
tm_exchange 11
tm_samples 311
yunwei 64
[root@pgtina file]# cat devops.php
<html>
<body>
<div style="position:absolute;width:100%;height:100%;z-index:-1">
<img src="./3.jpg" width="100%" height="100%" />
</div>
<br/></br/>
<h1 align='center'>欢迎使用数据库监控系统</h1>
最近一次更新时间:2015-12-08 15:08:54 <br/>
日志文件数(保存四天日志):8
<br/></br/>
表名 表大小(mb):<br/>
t_test_wubao | 14 <br/> (1 row) <br/> <br/>
<br/>
表名 表行数:
<br/>
<?php
$file = fopen("/var/www/html/file/devops.txt","r");
while(!feof())
{
echo fgets($file). "<br />";
}
fclose();
?>
</body>
</html>
访问:http://192.168.12.20
http://192.168.12.20/pgbadger/out.html
http://192.168.12.20/file/devops.php
https://github.com/dalibo/pgbadger/tree/v3.3 --安装包的下载地址,可以随意选择自己想要的版本
1、解压下载的安装包
drwxr-xr-x 3 root root 4096 May 5 08:19 pgbadger-5.1 --解压出来的目录
-rw-r--r-- 1 root root 275644 Sep 3 03:21 pgbadger-5.1.zip
2、安装
[root@localhost ~]# cd pgbadger-5.1
[root@localhost pgbadger-5.1]# ll
total 1004
-rw-r--r-- 1 root root 55530 May 5 08:19 ChangeLog
-rw-r--r-- 1 root root 365 May 5 08:19 CONTRIBUTING.md
drwxr-xr-x 2 root root 4096 May 5 08:19 doc
-rw-r--r-- 1 root root 903 May 5 08:19 LICENSE
-rw-r--r-- 1 root root 1399 May 5 08:19 Makefile.PL
-rw-r--r-- 1 root root 81 May 5 08:19 MANIFEST
-rw-r--r-- 1 root root 334 May 5 08:19 META.yml
-rw-r--r-- 1 root root 873457 May 5 08:19 pgbadger
-rw-r--r-- 1 root root 22729 May 5 08:19 README
[root@localhost pgbadger-5.1]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for pgBadger
[root@localhost pgbadger-5.1]# make
cp pgbadger blib/script/pgbadger
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/pgbadger
Manifying blib/man1/pgbadger.1
[root@localhost pgbadger-5.1]# make install
Installing /usr/share/man/man1/pgbadger.1
Installing /usr/bin/pgbadger
Writing /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/pgBadger/.packlist
Appending installation info to /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/perllocal.pod
3、查看安装版本
[root@testos ]# which pgbadger
/usr/bin/pgbadger
[root@testos ~]# pgbadger -V
pgBadger version 5.1
4、设置postgresql.conf
log_line_prefix = '%t-%d-%h-%a :'
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
5、网页查看
http://192.168.10.173/out.html --暂时打不开,还有没装完的。
6、安装httpd、php
yum install httpd ---要先自己配置yum,才能使用yum install 进行安装
chkconfig httpd on --添加到自动启动
service httpd start
yum install php
/var目录下就多了www目录
[root@localhost www]# ll
total 32
drwxr-xr-x 2 root root 4096 Jul 15 2009 cgi-bin
drwxr-xr-x 3 root root 4096 Sep 3 03:39 error
drwxr-xr-x 2 root root 4096 Jul 15 2009 html
drwxr-xr-x 3 root root 4096 Sep 3 03:39 icons
[root@localhost www]# pwd
/var/www
7、在html下新建php文件,并测试http://192.168.10.173:/test.php能否正常打开
网页显示如下:
<?
phpinfo();
?>
---显示图形化界面才是正确的,而不是只读取文本内容
8、安装apache
>tar -zxvf httpd-2.2.11.tar.gz
>cd httpd-2.2.11
>./configure --prefix=/usr/local/apache
make
make install
9、重启apache服务,再次验证
vi /usr/local/apache/conf/httpd.conf
找到"DirectoryIndex index.html在index.html 前添加 test.php
service httpd restart
http://192.168.10.173:/test.php --可以看到图形化界面
10、想办法把pg监控的log显示到这个界面
vi /etc/httpd/conf/httpd.conf 修改两处
DocumentRoot "/var/www/" --这样就可以读取www下面所有的目录和文件,因为我在/var/www下面新建了一个pgbadger子目录,out.html就放在下面
如果不修改设置,就会提示找不到/pgbadger/out.html
11、尝试手动执行
cd /usr/bin
[root@localhost bin]# pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.csv -o /var/www/pgbadger/out.html
Can't locate Text/CSV_XS.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at /usr/bin/pgbadger line 1583.
---看样子应该是没办法读取csv格式的文件
修改配置文件,将日志文件改成log
log_destination = 'csvlog' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
# These are only used if logging_collector is on:
log_directory = '/pg/pg_log1' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
12、再次执行
[root@localhost bin]# pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.log -o /var/www/pgbadger/out.html
FATAL: unable to detect log file format from /pg/pg_log1/postgresql-2014-09-03_193449.log, please use -f option. ---无法检测格式
需要指定格式
[root@localhost pg_log1]# pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.csv -o /var/www/pgbadger/out.html -f csv --报错
Can't locate Text/CSV_XS.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at /usr/bin/pgbadger line 1583.
[root@localhost pg_log1]# pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.csv -o /var/www/pgbadger/out.html -f stderr --正确
[========================>] Parsed 441 bytes of 441 (100.00%), queries: 0, events: 0
或者
pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.log -o /var/www/pgbadger/out.html -f csvlog
13、验证成功
http://192.168.12.173:/pgbadger/out.html
可以打开监控页面,但是完全无数据
[root@localhost bin]# pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.log -o /var/www/pgbadger/out.html -不添加格式后,又可以了
[========================>] Parsed 4303 bytes of 4303 (100.00%), queries: 8, events: 6
LOG: Ok, generating html report...
--Ok,搞定,可以在网页上面看到监控的数据了。
14、定期执行命令
[root@localhost ~]# cat run.sh
#!/bin/bash
/usr/bin/pgbadger --prefix '%t-%d-%h-%a :' /pg/pg_log1/*.log -o /var/www/pgbadger/out.html
vi /etc/crontab
0 */1 * * * root /root/run.sh
============================再次安装pgbadger====================
[root@pg pgbadger]# find / -name "pgbadger"
/var/www/html/pgbadger
/var/www/html/pgbadger/blib/script/pgbadger
/var/www/html/pgbadger/pgbadger
/usr/local/bin/pgbadger
[root@pg pgbadger]# cat run.sh
#!/bin/bash
/usr/local/bin/pgbadger --prefix '%t-%d-%h-%a :' /home/pgsql/9.1/data/pg_log/postgresql-201* -o /var/www/html/pgbadger/out.html
1、下载
https://github.com/dalibo/pgbadger
https://github.com/dalibo/pgbadger/releases
2、安装 --192.168.12.29
[root@antiywh-5NMQMH1 tmp]# tar -zxvf pgbadger-7.1.tar.gz
pgbadger-7.1/
pgbadger-7.1/.gitignore
pgbadger-7.1/.perltidyrc
pgbadger-7.1/CONTRIBUTING.md
pgbadger-7.1/ChangeLog
pgbadger-7.1/LICENSE
pgbadger-7.1/MANIFEST
pgbadger-7.1/META.yml
pgbadger-7.1/Makefile.PL
pgbadger-7.1/README
pgbadger-7.1/doc/
pgbadger-7.1/doc/pgBadger.pod
pgbadger-7.1/pgbadger
pgbadger-7.1/tools/
pgbadger-7.1/tools/README
pgbadger-7.1/tools/pgbadger_tools
[root@antiywh-5NMQMH1 pgbadger-7.1]# make && make install --直接安装报错
make: *** 没有指明目标并且找不到 makefile。 停止。
[root@antiywh-5NMQMH1 pgbadger-7.1]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for pgBadger
[root@antiywh-5NMQMH1 pgbadger-7.1]# make && make install
cp pgbadger blib/script/pgbadger
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pgbadger
Manifying blib/man1/pgbadger.1p
Installing /usr/local/share/man/man1/pgbadger.1p
Installing /usr/local/bin/pgbadger
Appending installation info to /usr/lib64/perl5/perllocal.pod
root@antiywh-5NMQMH1 pgbadger-7.1]# which pgbadger
/usr/local/bin/pgbadger
[root@antiywh-5NMQMH1 ~]# pgbadger -V
pgBadger version 7.1
3、安装php和httpd依赖包
yum install -y httpd httpd-devel php php-devel
chkconfig httpd on
[root@antiywh-5NMQMH1 ~]# service httpd start
正在启动 httpd:httpd: Could not reliably determine the server's fully qualified domain name, using 220.250.64.18 for ServerName
[确定]
[root@antiywh-5NMQMH1 data]# service httpd restart
停止 httpd: [确定]
正在启动 httpd: [确定]
4、安装apache
>tar -zxvf httpd-2.2.11.tar.gz
>cd httpd-2.2.11
>./configure --prefix=/usr/local/apache
make && make install
5、修改一些pg库的参数
logging_collector = on
#log_destination = 'stderr'
#log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_line_prefix = '%t-%d-%h-%a :'
6、修改参数
vi /etc/httpd/conf/httpd.conf
DocumentRoot "/var/www/" --这样就可以读取www下面所有的目录和文件,因为我在/var/www下面新建了一个pgbadger子目录,out.html就放在下面
如果不修改设置,就会提示找不到/pgbadger/out.html
ServerName 127.0.0.1:80
<Directory "/var/www/pgbadger"> --指定具体目录
Options Indexes FollowSymLinks
Order allow,deny
Allow from all
AllowOverride All
</Directory>
vi /usr/local/apache/conf/httpd.conf
写一个网页验证:
[root@antiywh-5NMQMH1 pgbadger]# pwd
/var/www/pgbadger
[root@antiywh-5NMQMH1 pgbadger]# cat test.php
<?php
echo "tina's website";
?>
7、重启apache服务,再次验证
service httpd restart
http://192.168.12.29:/pgbadger/test.php
若是启动报错,说端口占用,就先kill掉
[root@antiywh-5NMQMH1 html]# netstat -lnp|grep 80
tcp 0 0 :::80 :::* LISTEN 16634/httpd
[root@antiywh-5NMQMH1 html]# ps 16634
PID TTY STAT TIME COMMAND
16634 ? Ss 0:00 /usr/local/apache/bin/httpd -k start
kill -9 16634
8、启动pgbadger的
/usr/local/bin/pgbadger --prefix '%t-%d-%h-%a :' /home/pgsql/9.1/data/pg_log/postgresql-201* -o /var/www/html/pgbadger/out.html
-----------
[root@localhost mha4mysql-node-0.52]# perl Makefile.PL;make;make install
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Makefile.pm line 4.
BEGIN failed--compilation aborted at inc/Module/Install/Makefile.pm line 4.
Compilation failed in require at inc/Module/Install.pm line 307.
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Can.pm line 6.
BEGIN failed--compilation aborted at inc/Module/Install/Can.pm line 6.
Compilation failed in require at inc/Module/Install.pm line 307.
Can't locate ExtUtils/MM_Unix.pm in @INC (@INC contains: inc /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at inc/Module/Install/Metadata.pm line 316.
make: *** 没有指明目标并且找不到 makefile。 停止。
make: *** 没有规则可以创建目标“install”。 停止。
解决办法: yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
CPAN(Comprehensive Perl Archive Network)是internet上Perl模块最大的集散地,包含了现今公布的几乎所有的perl模块,
通过上面的例子,可以获知利用这个格式,可以到CPAN上安装需要的模块。
到CPAN的官方站点下载CPAN模块
http://search.cpan.org/~andk/CPAN-1.9301/lib/CPAN.pm
解压,编绎,安装
$tar zxvf CPAN-1.9301.tar.gz
$cd CPAN-1.9301
$perl Makefile.PL
ImportingPAUSE public key into your GnuPG keychain... done!
(Youmay wish to trust it locally with 'gpg --lsign-key 450F89EC')
WARNING:SIGN is not a known parameter.
Checkingif your kit is complete...
Looksgood
Warning:prerequisite Test::Harness 2.62 not found. We have 2.56.
Warning:prerequisite Test::More 0 not found.
'SIGN'is not a known MakeMaker parameter name.
WritingMakefile for CPAN
http://search.cpan.org/~andk/
CPAN-2.10.tar.gz
[root@pgtina CPAN-2.10]# perl Makefile.PL
Importing PAUSE public key into your GnuPG keychain... gpg: new configuration file `/root/.gnupg/gpg.conf' created
gpg: WARNING: options in `/root/.gnupg/gpg.conf' are not yet active during this run
done!
(You may wish to trust it locally with 'gpg --lsign-key 450F89EC')
Checking if your kit is complete...
Looks good
Warning: prerequisite CPAN::Meta::Requirements 2.121 not found.
Warning: prerequisite HTTP::Tiny 0.005 not found.
Warning: prerequisite Test::More 0 not found.
Writing Makefile for CPAN
make && make install
依然去http://search.cpan.org/~andk/ 查找这个CPAN::Meta::Requirements HTTP::Tiny
装了两个之后再检测就差一个
[root@pgtina CPAN-2.10]# perl Makefile.PL
Importing PAUSE public key into your GnuPG keychain... done!
(You may wish to trust it locally with 'gpg --lsign-key 450F89EC')
Warning: prerequisite Test::More 0 not found.
Writing Makefile for CPAN
yum install perl-Time-HiRes
http://192.168.12.29/pgbadger/out.html 7.1版本的
http://192.168.12.250/pgbadger/out.html 3.3版本的 这个版本的看着舒服些
---前段部分我是个外行,只能简单的实现下我想要的效果~大家可以忽略以下内容----------
怎么添加首页面
[root@pg html]# pwd
/var/www/html
[root@pg html]# cat index.php
<html>
<head>
<title>数据库监控工具</title>
</head>
<body>
<br/></br/>
<h1 align='center'>欢迎使用数据库监控系统</h1>
<p align='center'>
<a href='pgbadger/out.html'>主库日志监控(20服务器)</a> <br/>
<br/>
<a href='pgbadger/out_18.html'>从库日志监控(18服务器)</a>
</p>
<p align='center'>
<font color='red'>备注:日志分析结果2小时更新一次</font>
</p>
<p>
20数据库运维日志<br>
<?php
$file = fopen("log.txt","r");
while(!feof($file))
{
echo fgets($file). "<br />";
}
fclose($file);
?>
</p>
</body>
</html>
[root@antiywh-5NMQMH1 www]# vi getdata.sh
#!/bin/bash
su - postgres -c "psql -d tm_samples" <<EOF
\o /var/www/html/log.txt
SELECT * FROM (
SELECT RELNAME ,pg_relation_size(RELNAME::regclass )/1024/1024 tabMB FROM PG_STAT_USER_TABLES
)AA where tabMB > 10
ORDER BY tabMB DESC;
\q
EOF
echo "日志文件数(保存四天日志):" >> /var/www/html/log.txt
ls /home/pgsql/9.1/data/pg_log/|wc -l >> /var/www/html/log.txt
尽量配置到/var/www/html目录下
[root@pgtina html]# ll
total 24
-rwxr-xr-x. 1 root root 376 Dec 7 10:57 getdata.sh
-rw-r--r--. 1 root root 525 Dec 7 10:21 index.php
-rw-rw-r--. 1 postgres postgres 6691 Dec 7 10:57 log.txt
drwxrwxrwx. 2 root root 4096 Dec 7 10:10 pgbadger
-rw-r--r--. 1 root root 32 Dec 4 15:48 test.php
[root@pgtina html]#
/////////////////////////////////////////////////////////////新的监控页面(表大小+表行数)///////////////////////////////////////////////////
如果在字符才中有双引号的话,可以使用反斜杠"\"来进行转义
10 */2 * * * root sh /tmp/run.sh
pgbadger监控页面的数据
[root@antiywh-5NMQMH1 pg_log]# cat /tmp/run.sh
#!/bin/bash
/usr/local/bin/pgbadger --prefix '%t-%d-%h-%a :' /home/pgsql/9.1/data/pg_log/postgresql-201* -o /var/www/html/pgbadger/out.html
[root@pgtina html]# pwd
/var/www/html
[root@pgtina html]# ll
total 24
-rw-r--r--. 1 root root 172 Dec 8 15:08 db_jk.log
drwxr-xr-x. 2 root root 4096 Dec 8 15:18 file
-rwxrwxrwx. 1 root root 2741 Dec 8 15:39 getdata.sh
-rwxr-xr-x. 1 root root 864 Dec 8 14:18 index.php
drwxrwxrwx. 2 root root 4096 Dec 8 14:10 pgbadger
-rw-r--r--. 1 root root 32 Dec 4 15:48 test.php
主页面(展示数据库的名字跟表个数),子页面(表大小+表行数)
[root@pgtina html]# cat getdata.sh
#!/bin/bash
#2015-12-8 tina
date=`date +"%Y-%m-%d %H:%M:%S"`
rm -f /var/www/html/db_jk.log
rm -f /var/www/html/file/*.php
rm -f /var/www/html/file/*.txt
rm -f /tmp/pg250_*.log
num1=`ls /home/pgsql/9.1/data/pg_log/|wc -l`
databases=$(psql -U postgres -c "select datname from pg_database where datname not like 'temp%' and datname not like 'postgres' order by datname;" |grep -v "datname" |grep -v "row"|grep -v "-")
for db in $databases
do
num2=$(psql -U postgres -d $db -c "select count(*) from pg_tables where schemaname='public';"|grep -v "count" |grep -v "row"|grep -v "-")
echo "$db $num2">>/var/www/html/db_jk.log
done
for db in $databases
do
tables=$(psql -U postgres -d $db -c "select tablename from pg_tables where schemaname='public' order by tablename;"|grep -v "tablename" |grep -v "row"|grep -v "\-")
for table in $tables
do
echo $table >>/tmp/pg250_$db.log
psql -U postgres -d $db -c "select count(*) from $table;" |grep -v "count" |grep -v "row"|grep -v "\-">>/tmp/pg250_$db.log
done
cat /tmp/pg250_$db.log|awk 'NF==1{printf "%s ", $1;next}1'>>/var/www/html/file/$db.txt
echo "<html>" >/var/www/html/file/$db.php
echo "<body>">>/var/www/html/file/$db.php
echo '<div style="position:absolute;width:100%;height:100%;z-index:-1">
<img src="./3.jpg" width="100%" height="100%" />
</div>' >>/var/www/html/file/$db.php
echo "<br/></br/>" >>/var/www/html/file/$db.php
echo "<h1 align='center'>欢迎使用数据库监控系统</h1>" >>/var/www/html/file/$db.php
echo "最近一次更新时间:$date <br/>">>/var/www/html/file/$db.php
echo "<br/></br/>" >>/var/www/html/file/$db.php
echo "日志文件数(保存四天日志):$num1" >>/var/www/html/file/$db.php
echo "<br/></br/>">>/var/www/html/file/$db.php
echo "表名 表大小(mb):<br/>" >>/var/www/html/file/$db.php
data=$(psql -U postgres -d $db -c "SELECT * FROM (SELECT RELNAME ,pg_relation_size(RELNAME::regclass )/1024/1024 tabMB FROM PG_STAT_USER_TABLES )AA ORDER BY tabMB DESC;"|grep -v "relname" |grep -v "-"| awk '{print $0,"<br/>"}')
echo $data >> /var/www/html/file/$db.php
echo "<br/>">>/var/www/html/file/$db.php
echo "表名 表行数:" >>/var/www/html/file/$db.php
echo "<br/>">>/var/www/html/file/$db.php
echo "<?php" >>/var/www/html/file/$db.php
echo "\$file = fopen(\"/var/www/html/file/$db.txt\",\"r\");" >>/var/www/html/file/$db.php
echo "while(!feof($file))" >>/var/www/html/file/$db.php
echo "{" >>/var/www/html/file/$db.php
echo 'echo fgets($file). "<br />";' >>/var/www/html/file/$db.php
echo "}" >>/var/www/html/file/$db.php
echo "fclose($file);" >>/var/www/html/file/$db.php
echo "?>" >>/var/www/html/file/$db.php
echo "</body>" >>/var/www/html/file/$db.php
echo "</html>" >>/var/www/html/file/$db.php
done
[root@pgtina html]# cat index.php
<html>
<head>
<title>数据库监控工具</title>
</head>
<body>
<div style="position:absolute;width:100%;height:100%;z-index:-1">
<img src="./file/4.jpg" width="100%" height="100%" />
</div>
<br/></br/>
<h1 align='center'>欢迎使用数据库监控系统</h1>
<p align='center'>
<a href='pgbadger/out.html'>主库日志监控(250测试服务器)</a> <br/>
<br/>
</p>
<p align='center'>
<font color='red'>备注:日志分析结果2小时更新一次</font>
</p>
服务器IP:192.168.12.250
<br/></br/>
服务器角色:测试库
<br/></br/>
数据库列表(数据库名 表个数):
<br/>
<?php
$file = fopen("/var/www/html/db_jk.log","r");
while(!feof($file))
{
$str = trim(fgets($file));
$arr = strstr($str,' ',TRUE);
echo '<a href="/file/'.$arr.'.php">'. $str. "</a><br />";
}
fclose($file);
?>
</p>
</body>
</html>
[root@pgtina html]# cat db_jk.log
devops 1
dw_report 16
dynfarming 6
engine 15
mongo_statistic 5
t_url 4
test 11
tm_exchange 11
tm_samples 311
yunwei 64
[root@pgtina file]# cat devops.php
<html>
<body>
<div style="position:absolute;width:100%;height:100%;z-index:-1">
<img src="./3.jpg" width="100%" height="100%" />
</div>
<br/></br/>
<h1 align='center'>欢迎使用数据库监控系统</h1>
最近一次更新时间:2015-12-08 15:08:54 <br/>
日志文件数(保存四天日志):8
<br/></br/>
表名 表大小(mb):<br/>
t_test_wubao | 14 <br/> (1 row) <br/> <br/>
<br/>
表名 表行数:
<br/>
<?php
$file = fopen("/var/www/html/file/devops.txt","r");
while(!feof())
{
echo fgets($file). "<br />";
}
fclose();
?>
</body>
</html>
访问:http://192.168.12.20
http://192.168.12.20/pgbadger/out.html
http://192.168.12.20/file/devops.php
发表评论
-
pg 锁
2016-01-14 16:26 0pg 锁 ... -
postgresql 的三类日志
2016-01-14 15:59 18508一、PostgreSQL有3种日志: 1)pg_log(数据 ... -
pg存储过程--创建分区表
2016-01-13 15:46 01)将普通表改成按时间字段分区表 调用select fun_c ... -
pg常用自制shell脚本-tina
2016-01-13 15:30 49291)小型监控: 1.在pg库主机上部署,每5分钟执行一次,插入 ... -
postgresql 时间类型和相关函数
2016-01-13 10:41 5441今天来好好学习一下postgresql涉及时间的字段类型和一些 ... -
pg 表空间
2016-01-07 16:28 3119一、说明 在数据库运维工作中,经常会有数据目录使用率较高 ... -
pg 定期vacuum和reindex
2016-01-07 14:56 8608定期vacuum和reindex: 一 ... -
pg 序列
2016-01-06 16:58 1618一、简介 一个序列对象通常用于为行或者表生成唯一的标识符。 ... -
pg 简单备份和恢复
2016-01-06 15:53 3766pg的备份和恢复 pg_dump ... -
ERROR: invalid page header in block 27073 of relation base/21078/45300926
2016-01-06 15:12 2139突然断网,检查后通知我们UPS断电,db所在主机重启 1、连上 ... -
pg_cancel_backend()和pg_terminate_backend()
2016-01-05 17:42 3548pg_cancel_backend()和pg_terminat ... -
canceling statement due to conflict with recovery
2016-01-05 17:12 1675报错: canceling statement due to ... -
postgresql dblink 使用
2015-12-31 14:33 2037dblink的使用 pg的跨库查询工具 select dbli ... -
root用户不能使用psql或者pg_dump等pg命令
2015-12-24 14:40 7010root用户不能使用psql或者pg_dump等pg命令 [ ... -
postgresql新建库2个常见报错
2015-12-22 16:43 6242今天使用pg建库发现两个报错: ERROR: new c ... -
安装postgresql 9.1.1
2015-12-22 16:25 639安装postgresql 9.1.1 ---版本自选,步骤相同 ... -
oracle,postgresql,mysql一些使用上的区别记录
2015-12-16 11:38 01.限制行数: select * from ta where ... -
postgresql存储过程实例:已审核证书存入临时表
2015-12-14 16:44 648存储过程实例: 需求: 思路:建立存储过程 代码逻辑: 1 ... -
pg 函数sfa_tmp_sleep()执行越来越慢-sql分析
2015-12-11 09:48 672pg 函数sfa_tmp_sleep()执行越来越慢 ... -
pgpool 主从流复制模式下的安装使用
2015-12-11 09:50 4120pgpool-II 是一个位于 PostgreSQL 服务器和 ...
相关推荐
使用`rds-pgbadger`,你首先需要安装必要的依赖,包括Python环境和相关的Python库,如`boto3`(Amazon Web Services的Python SDK),`psycopg2`(用于与PostgreSQL交互)等。一旦设置好环境,你可以通过配置文件来...
此外,还可以结合其他监控工具(如Prometheus、Grafana)来进一步可视化和报警,以便及时发现并解决问题。 总结来说,`pgBadger_on_RDS`是通过在shell环境中利用pgBadger工具分析阿里云RDS实例的日志,以提升数据库...
- **跟踪工具**:利用`pg_trace`、`pgBadger`等工具进行更深入的性能分析和问题定位。 - **异常处理**:编写脚本或使用第三方工具自动处理常见错误,如连接超时、磁盘满等。 通过上述知识点的学习和实践,可以有效...
- **扩展**:安装额外的扩展如`pg_stat_statements`、`pgBadger`等,可以获取更详细的查询统计信息和日志分析。 #### 四、优化 为了提高数据库的性能,优化是必不可少的。PostgreSQL提供了多种优化手段: - **...
- **性能监控**:使用内置的`pg_stat_statements`扩展或者第三方工具(如pgAdmin、pgBadger)来监控数据库性能,定位慢查询。 - **日志分析**:通过配置日志输出,收集和分析日志信息,帮助诊断和解决问题。 - **...
- 监控工具:如pg_stat_activity、pg_top、pgBadger等监控数据库运行状态。 本手册旨在帮助开发者和数据库管理员全面掌握PostgreSQL,无论你是初学者还是有经验的DBA,都能从中受益。通过详细阅读和实践,你将能够...
- **监控指标**:监控包括CPU使用率、内存使用情况、磁盘I/O、网络带宽以及连接数等,这些都能反映出数据库的健康状态。 - **查询统计**:通过`pg_stat_activity`视图可以查看当前正在执行的SQL查询,识别阻塞或...
书中会介绍pg_statistic、pg_stat_activity等系统视图的使用,以及如何利用工具如pgAdmin、pgBadger和pg_top进行性能监控。 8. **复制与高可用性**:PostgreSQL提供了多种复制方案,如物理复制、逻辑复制和流复制。...
还可以使用工具如pgBadger、pg_top 或者 Grafana、Prometheus 等进行更详细的分析。 九、复制与高可用性 PostgreSQL 支持多种复制模式,包括流复制、逻辑复制和并行复制。这些机制可以实现数据冗余,提高系统的...
工具则涵盖了一系列辅助数据库管理和开发的应用,如SQL查询工具(如MySQL Workbench、DBeaver)、数据库迁移工具(如Flyway、Liquibase)、性能分析工具(如Percona Toolkit、pgBadger)等。这些工具极大地提高了...
11. **监控与诊断**:讨论如何使用pg_statistic、pg_stat_activity等内置视图监控数据库性能,以及第三方工具如pgAdmin、pgBadger等的使用。 12. **故障排查与维护**:提供故障排查技巧,如解析错误日志,诊断性能...
在使用`pg_awr`时,我们需要关注其安装、配置和使用方法,确保它能准确地捕获和分析PostgreSQL的性能指标。同时,由于PostgreSQL社区的活跃性,可能还有其他类似的工具或解决方案,如pg_stat_statements、pgBadger等...
- **监控工具**:介绍PostgreSQL自带的监控工具以及外部工具,如`pgBadger`、`pgTune`等。 - **性能测试**:通过基准测试来衡量不同配置下的性能差异。 - **最佳实践总结**:总结实践中遇到的问题及解决方案。 ####...
- **性能监控工具**:使用pg_stat_statements、pgBadger等工具监控数据库性能指标,及时发现性能瓶颈。 - **故障诊断与解决**:学习如何排查常见的性能问题,并采取相应措施进行修复。 #### 六、高级功能 - **并行...
4. **性能监控与故障排查**:详细阐述了如何使用各种工具和技术来监控数据库的运行状态,并快速定位及解决性能问题。 5. **基准测试**:教授如何建立有效的基准测试环境,以便更好地评估不同配置下的数据库性能。 ...
2. **性能监控**:深入探讨如何使用各种工具和技术来监控PostgreSQL的性能,包括但不限于使用`pg_stat_*`视图和扩展插件如`pgBadger`或`pgTune`。 3. **查询优化**:讨论了如何编写高效的SQL查询,以及如何利用索引...
而工具的使用则涵盖了监控、诊断和调优过程中所依赖的各种实用程序。 【文件名称列表】中的工具主要来自PuTTY套件,这是一个用于远程连接的工具集,包括putty.exe(SSH/Telnet客户端)、psftp.exe(SFTP客户端)、...
4. `pg_top` 和 `htop`:监控服务器资源使用情况,确保数据库运行在合理的硬件资源下。 5. `pgBadger` 和 `pg_stat_statements` 扩展:收集和分析日志,为长期性能调整提供数据支持。 **四、扩展与社区支持** ...
#### 六、压力测试工具的使用和建模 - **工具介绍**: - 介绍常用的性能测试工具,如PgBench、pgTune等。 - 分析这些工具的特点和适用场景。 - **测试案例设计**: - 学习如何根据实际业务需求设计合理的压力测试...