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