测试操作系统:Centos 7.6
测试IP :192.168.112.88
操作系统安装信息:最小化安装
下载地址:
https://github.com/dalibo/pgbadger
https://pgbadger.darold.net/
Official releases:
Official release are published to the GitHub Release page of pgBadger.
Not all release are subject to a notification sent to the pgsql-announce mailling list, this is reserved to the main releases.
Binary packages:
pgBadger may have a binary package corresponding to your distribution. For RPM packages, thanks to the great work of Devrim GÜNDÜZ, you can find the pgBadger package at the PostgreSQL yum repository
Thanks to Christoph BERG packages for Debian and Ubuntu are available in the PostgreSQL apt repository
Development code:
The latest development code can always be found into the pgBadger's GitHub repository
这里我们选择 Official releases 下载.
1.解压文件.
unzip pgbadger-11.3.zip
2.安装 perl 以及依赖
yum install perl perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN
3.安装pgbadger
3.1 使用编译的方式进行安装
[root@PGHOST pgbadger-11.3]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for pgBadger
[root@PGHOST pgbadger-11.3]# make && make install
3.2 使用rpm包方式进行安装(未测试)
yum install xx.rpm 即可
4.查看帮助信息
[root@PGHOST pgbadger-11.3]# ./pgbadger --help
Usage: pgbadger [options] logfile [...]
PostgreSQL log analyzer with fully detailed reports and graphs.
Arguments:
logfile can be a single log file, a list of files, or a shell command
returning a list of files. If you want to pass log content from stdin
use - as filename. Note that input from stdin will not work with csvlog.
Options:
-a | --average minutes : number of minutes to build the average graphs of
queries and connections. Default 5 minutes.
-A | --histo-average min: number of minutes to build the histogram graphs
of queries. Default 60 minutes.
-b | --begin datetime : start date/time for the data to be parsed in log
(either a timestamp or a time)
-c | --dbclient host : only report on entries for the given client host.
-C | --nocomment : remove comments like * ... */ from queries.
-d | --dbname database : only report on entries for the given database.
-D | --dns-resolv : client ip addresses are replaced by their DNS name.
Be warned that this can really slow down pgBadger.
-e | --end datetime : end date/time for the data to be parsed in log
(either a timestamp or a time)
5.数据库参数配置
log_destination='csvlog'
logging_collector=on
log_rotation_age=1d
log_rotation_size=128MB
log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_error_verbosity = default
[postgres@PGHOST data]$ pg_ctl reload
server signaled
6.日志生成html报告
[root@PGHOST log]# pgbadger *.csv -o test.html -f stderr
7.安装httpd(可在不同机器,后期通过scp或者ftp方式上传html文件即可)
[root@PGHOST yum.repos.d]# yum install httpd
[root@PGHOST yum.repos.d]# chkconfig httpd on
[root@PGHOST yum.repos.d]# systemctl start httpd
[root@PGHOST html]# systemctl stop firewalld
[root@PGHOST html]# systemctl disable firewalld
[root@PGHOST log]# cp test.html var/www/html/
查看
http://192.168.112.88/test.html
页面显示正常.
8.部署pgbadger 定期执行脚本
cat pgbadger_run.sh
#!/bin/bash
source ~/.bash_profile
/u01/pgbadger-11.3/pgbadger /pgsoft/data/log/*.csv -o /var/www/html/test.html
crontab -l
0 */1 * * * root /u01/pgbadger_run.sh
