Analyze PostgreSQL log using pgBadger and scheduling using crontab

sihar

Sihar Simbolon

Posted on March 8, 2024

Analyze PostgreSQL log using pgBadger and scheduling using crontab

pgBadger can help us analyze PostgreSQL log with fully detailed reports and graphs.

Step by Step
Prepare your PostgreSQL log format

...
# pgbadger settings
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
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
log_min_duration_statement = 0
...
Enter fullscreen mode Exit fullscreen mode

Apply PostgreSQL settings

postgres=# select pg_reload_conf();
Enter fullscreen mode Exit fullscreen mode

Install pgBadger
Better using other server/vps to install pgBadger and save pgBadger report there.
To install pgBadger you can use this instruction

Create folder for output directory pgBadger

mkdir /var/www/postgresql-reports
Enter fullscreen mode Exit fullscreen mode

Create folder for save PostgreSQL log

mkdir /home/xxxx/postgresql-log
Enter fullscreen mode Exit fullscreen mode

Create bash script

touch /home/xxx/script/building-report.sh
Enter fullscreen mode Exit fullscreen mode

Fill the script using this

#!/bin/bash

filename="postgresql-"$(date -d "1 day ago" +%Y-%m-%d)

#copy h-1 postgresql log from remote server
scp your_username@your_server_address:/var/log/postgresql/$filename".log" /home/xxxx/postgresql-log/

#building report using pgbadger
pgbadger -p "%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h " /home/xxxx/postgresql-log/$filename".log" -O /var/www/postgresql-reports/ -o $filename".html" --no-progressbar
Enter fullscreen mode Exit fullscreen mode

Open crontab

crontab -e
Enter fullscreen mode Exit fullscreen mode

Add pgBadger command

0 1 * * * /bin/bash /home/xxx/script/building-report.sh 2>&1 | logger -t pgbadger-cmd
Enter fullscreen mode Exit fullscreen mode

Finish!!!

Reference:
pgBadger documentation

💖 💪 🙅 🚩
sihar
Sihar Simbolon

Posted on March 8, 2024

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related