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

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

Billboard image

The fastest way to detect downtimes

Join Vercel, CrowdStrike, and thousands of other teams that trust Checkly to streamline monitoring.

Get started now

Top comments (0)

Heroku

This site is built on Heroku

Join the ranks of developers at Salesforce, Airbase, DEV, and more who deploy their mission critical applications on Heroku. Sign up today and launch your first app!

Get Started

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay