DEV Community

Cover image for Analyze PostgreSQL log using pgBadger and scheduling using crontab
Sihar Simbolon
Sihar Simbolon

Posted on

Analyze PostgreSQL log using pgBadger and scheduling using crontab

#postgres #pgbadger #scheduling

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

Top comments (0)