Let us try to generate a very simple pgbadger report
1. Run a simple query:
\c pgbenchdb
select pg_sleep(2);
2. launch the below command:
pgbager $PGDATA/log/postgresql-Sun.log
3. Download the out.html generated in the same directory from where the previous command was run.
4. Open the Report and navigate the report:
Flow diagram:
>> postgreslog --- pgbadger --> output in html format (JSON)
You can get an incremental updates as well for the report.
A. pg_setting - we need to have at minimum for pgbadger to work flawlessly or effectively:
1. log_min_duration_statement = 0 [increase the value if needed, this value creates additional stress to a loaded machine]
2. for stderr (default)
log_line_prefix = '%t [%p]: db=%d,user=%u,app=%a,client=%h '
3. Other options to enable:
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
4. Ensure english is the language for log messages
lc_messages='en_US.UTF-8'
lc_messages='C'
5. Recommendation: Do not enable both log_min_duration_statement, log_duration and log_statement all together
log_min_duration_statement --- number of ms over which the queries shouldbe reported
log_duration --- set to log_min_duration_statement set to -1 and log_duration set
or
log_statement =all (no setting for other 2)
B. Verify pg_setting shows the new values were set properly
select name,setting
from pg_settings
where name in ('log_destination'
, 'log_min_duration_statement'
, 'log_line_prefix'
, 'log_checkpoints'
, 'log_connections'
, 'log_disconnections'
, 'log_lock_waits'
, 'log_temp_files'
, 'log_autovacuum_min_duration'
, 'log_error_verbosity'
, 'lc_messages'
)
order by 1;
alter system set log_min_duration_statement = 0;
alter system set log_line_prefix = '%t [%p]: db=%d,user=%u,app=%a,client=%h ';
alter system set log_checkpoints = on;
alter system set log_connections = on;
alter system set log_disconnections = on;
alter system set log_lock_waits = on;
alter system set log_temp_files = 0;
alter system set log_autovacuum_min_duration = 0;
select pg_reload_conf();
select name,setting
from pg_settings
where name in ('log_destination'
, 'log_min_duration_statement'
, 'log_line_prefix'
, 'log_checkpoints'
, 'log_connections'
, 'log_disconnections'
, 'log_lock_waits'
, 'log_temp_files'
, 'log_autovacuum_min_duration'
, 'log_error_verbosity'
, 'lc_messages'
)
order by 1;
-- We will need to take a postgresql bounce, since the log_connections and log_disconnections wont reflect with pg_reload_conf().
Target value:
name | setting
-----------------------------+------------------------------------------
lc_messages | en_US.UTF-8
log_autovacuum_min_duration | 0
log_checkpoints | on
log_connections | on
log_destination | stderr
log_disconnections | on
log_error_verbosity | default
log_line_prefix | %t [%p]: db=%d,user=%u,app=%a,client=%h
log_lock_waits | on
log_min_duration_statement | 0
log_temp_files | 0
(11 rows)
postgres=#
From pgBadger documentation: https://pgbadger.darold.net/documentation.html
1. Download the pgbadger from github
url: https://github.com/darold/pgbadger
2. Install using the below step
tar xzf pgbadger-11.x.tar.gz
cd pgbadger-11.x/
perl Makefile.PL
make && sudo make install
Instead I followed the below steps instead of 1 & 2:
mkdir pgbadger
git clone https://github.com/darold/pgbadger.git pgbadger
perl Makefile.PL
make && sudo make install
-bash-4.2$ git clone https://github.com/darold/pgbadger.git pgbadger
Cloning into 'pgbadger'...
remote: Enumerating objects: 5038, done.
remote: Counting objects: 100% (726/726), done.
remote: Compressing objects: 100% (297/297), done.
remote: Total 5038 (delta 458), reused 541 (delta 403), pack-reused 4312
Receiving objects: 100% (5038/5038), 12.47 MiB | 13.21 MiB/s, done.
Resolving deltas: 100% (3122/3122), done.
-bash-4.2$
-bash-4.2$ perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for pgBadger
-bash-4.2$
As root:
[root@vcentos79-postgres-ha1 pgbadger]# make && sudo make install
which: no pod2markdown in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
Makefile:824: You must install pod2markdown to generate README.md from doc/pgBadger.pod
echo "=head1 SYNOPSIS" > doc/synopsis.pod
./pgbadger --help >> doc/synopsis.pod
echo "=head1 DESCRIPTION" >> doc/synopsis.pod
sed -i.bak 's/ +$//g' doc/synopsis.pod
rm doc/synopsis.pod.bak
sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod
sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod
rm doc/pgBadger.pod.bak
Manifying blib/man1/pgbadger.1p
rm doc/synopsis.pod
which: no pod2markdown in (/sbin:/bin:/usr/sbin:/usr/bin)
Makefile:824: You must install pod2markdown to generate README.md from doc/pgBadger.pod
echo "=head1 SYNOPSIS" > doc/synopsis.pod
./pgbadger --help >> doc/synopsis.pod
echo "=head1 DESCRIPTION" >> doc/synopsis.pod
sed -i.bak 's/ +$//g' doc/synopsis.pod
rm doc/synopsis.pod.bak
sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod
sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod
rm doc/pgBadger.pod.bak
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
rm doc/synopsis.pod
[root@vcentos79-postgres-ha1 pgbadger]# echo $?
0
[root@vcentos79-postgres-ha1 pgbadger]#
Error#1:
-bash-4.2$ perl Makefile.PL
Can't locate ExtUtils/MakeMaker.pm in @INC (@INC contains: /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 Makefile.PL line 1.
BEGIN failed--compilation aborted at Makefile.PL line 1.
Resolution:
yum install perl-devel
...
Transaction test succeeded
Running transaction
Installing : gdbm-devel-1.10-8.el7.x86_64 1/10
Installing : pyparsing-1.5.6-9.el7.noarch 2/10
Installing : systemtap-sdt-devel-4.0-13.el7.x86_64 3/10
Installing : perl-ExtUtils-Manifest-1.61-244.el7.noarch 4/10
Installing : perl-Test-Harness-3.28-3.el7.noarch 5/10
Installing : libdb-devel-5.3.21-25.el7.x86_64 6/10
Installing : perl-ExtUtils-MakeMaker-6.68-3.el7.noarch 7/10
Installing : perl-ExtUtils-Install-1.58-299.el7_9.noarch 8/10
Installing : 4:perl-devel-5.16.3-299.el7_9.x86_64 9/10
Installing : 1:perl-ExtUtils-ParseXS-3.18-3.el7.noarch 10/10
Verifying : 1:perl-ExtUtils-ParseXS-3.18-3.el7.noarch 1/10
Verifying : libdb-devel-5.3.21-25.el7.x86_64 2/10
Verifying : perl-Test-Harness-3.28-3.el7.noarch 3/10
Verifying : perl-ExtUtils-Install-1.58-299.el7_9.noarch 4/10
Verifying : perl-ExtUtils-Manifest-1.61-244.el7.noarch 5/10
Verifying : systemtap-sdt-devel-4.0-13.el7.x86_64 6/10
Verifying : pyparsing-1.5.6-9.el7.noarch 7/10
Verifying : gdbm-devel-1.10-8.el7.x86_64 8/10
Verifying : perl-ExtUtils-MakeMaker-6.68-3.el7.noarch 9/10
Verifying : 4:perl-devel-5.16.3-299.el7_9.x86_64 10/10
Installed:
perl-devel.x86_64 4:5.16.3-299.el7_9
Dependency Installed:
gdbm-devel.x86_64 0:1.10-8.el7 libdb-devel.x86_64 0:5.3.21-25.el7 perl-ExtUtils-Install.noarch 0:1.58-299.el7_9
perl-ExtUtils-MakeMaker.noarch 0:6.68-3.el7 perl-ExtUtils-Manifest.noarch 0:1.61-244.el7 perl-ExtUtils-ParseXS.noarch 1:3.18-3.el7
perl-Test-Harness.noarch 0:3.28-3.el7 pyparsing.noarch 0:1.5.6-9.el7 systemtap-sdt-devel.x86_64 0:4.0-13.el7
Complete!
Error#2:
-bash-4.2$ make && sudo make install
which: no pod2markdown in (/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/usr/pgsql-15/bin:/usr/pgsql-15/lib)
Makefile:824: You must install pod2markdown to generate README.md from doc/pgBadger.pod
cp pgbadger blib/script/pgbadger
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pgbadger
echo "=head1 SYNOPSIS" > doc/synopsis.pod
./pgbadger --help >> doc/synopsis.pod
echo "=head1 DESCRIPTION" >> doc/synopsis.pod
sed -i.bak 's/ +$//g' doc/synopsis.pod
rm doc/synopsis.pod.bak
sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod
sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod
rm doc/pgBadger.pod.bak
Manifying blib/man1/pgbadger.1p
rm doc/synopsis.pod
We trust you have received the usual lecture from the local System
Administrator. It usually boils down to these three things:
#1) Respect the privacy of others.
#2) Think before you type.
#3) With great power comes great responsibility.
[sudo] password for postgres:
postgres is not in the sudoers file. This incident will be reported.
-bash-4.2$
Fix: Run the same command as root
[root@vcentos79-postgres-ha1 pgbadger]# make && sudo make install
which: no pod2markdown in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
Makefile:824: You must install pod2markdown to generate README.md from doc/pgBadger.pod
echo "=head1 SYNOPSIS" > doc/synopsis.pod
./pgbadger --help >> doc/synopsis.pod
echo "=head1 DESCRIPTION" >> doc/synopsis.pod
sed -i.bak 's/ +$//g' doc/synopsis.pod
rm doc/synopsis.pod.bak
sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod
sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod
rm doc/pgBadger.pod.bak
Manifying blib/man1/pgbadger.1p
rm doc/synopsis.pod
which: no pod2markdown in (/sbin:/bin:/usr/sbin:/usr/bin)
Makefile:824: You must install pod2markdown to generate README.md from doc/pgBadger.pod
echo "=head1 SYNOPSIS" > doc/synopsis.pod
./pgbadger --help >> doc/synopsis.pod
echo "=head1 DESCRIPTION" >> doc/synopsis.pod
sed -i.bak 's/ +$//g' doc/synopsis.pod
rm doc/synopsis.pod.bak
sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod
sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod
rm doc/pgBadger.pod.bak
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
rm doc/synopsis.pod
[root@vcentos79-postgres-ha1 pgbadger]# echo $?
0
[root@vcentos79-postgres-ha1 pgbadger]#
3. Verify if the pgbadger file is accessible, if so from where
-bash-4.2$ which pgbadger
/usr/local/bin/pgbadger
-bash-4.2$ ls -altr /usr/local/share/man/man1/
total 48
drwxr-xr-x. 21 root root 243 Mar 19 2023 ..
-r--r--r--. 1 root root 45127 Jan 28 06:38 pgbadger.1p
drwxr-xr-x. 2 root root 25 Jan 28 06:38 .
-bash-4.2$ pgbadger --help
Usage: pgbadger [options] logfile [...]
PostgreSQL log analyzer with fully detailed reports and graphs.
Arguments:
..
