pgbadger 慢日誌分析工具

測試作業系統: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