【PgSQL插件】性能分析pgbadger日志分析工具

• 环境介绍

# uname -a
Linux p0-mcrm-midsrv-db01 4.18.0-553.58.1.el8_10.x86_64 #1 SMP Thu Jun 12 14:27:07 EDT 2025 x86_64 x86_64 x86_64 GNU/Linux
# cat /etc/redhat-release
Red Hat Enterprise Linux release 8.10 (Ootpa)

数据库版本

# su - postgres
$ psql -V
psql (PostgreSQL) 17.6

• 安装pgbadger-13.1

从Github上下载pgBadger源码，并将源码上传到PgSQL服务器/home/postgres/pgshare

GitHub - darold/pgbadger at v13.1

# ll
-rw-r--r-- 1 postgres postgres 4367754 11月 8 09:20 pgbadger-13.1.tar.gz

root用户执行

# yum install perl-ExtUtils-MakeMaker

# tar -zxvf pgbadger-13.1.tar.gz

# cd pgbadger-13.1

# perl Makefile.PL
Checking if your kit is complete...
Looks good
Generating a Unix-style Makefile
Writing Makefile for pgBadger
Writing MYMETA.yml and MYMETA.json

# make

# make install

$ ll /usr/local/bin/
总用量 1612
-r-xr-xr-x 1 postgres postgres 1624241 11月 8 09:21 pgbadger

# su - postgres
$ source .bash_profile
$ cat .bash_profile

export PGHOME=/data/pghome
export PGDATA=/data/pgbase/pgdata
export LD_LIBRARY_PATH=/data/pghome/lib

export PATH=/data/pghome/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/puppetlabs/bin:/bin

$ pgbadger --version
pgBadger version 13.1

安装过程参考：https://zhuanlan.zhihu.com/p/1900116221350896583

• 针对一个pg_log的分析

pgbadger -q postgresql-2.csv -o postgresql-20201202.html -f csv

根据故障时段的分析：

pgbadger -q postgresql-4.csv -b "2022-01-27 07:50:00" -e "2022-01-27 09:00:00" -d ccrc -o postgresql-2022.html -f csv