Analyze PostgreSQL log using pgBadger and scheduling using crontab
Sihar Simbolon
Posted on March 8, 2024
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
...
Apply PostgreSQL settings
postgres=# select pg_reload_conf();
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
Create folder for save PostgreSQL log
mkdir /home/xxxx/postgresql-log
Create bash script
touch /home/xxx/script/building-report.sh
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
Open crontab
crontab -e
Add pgBadger command
0 1 * * * /bin/bash /home/xxx/script/building-report.sh 2>&1 | logger -t pgbadger-cmd
Finish!!!
Reference:
pgBadger documentation
💖 💪 🙅 🚩
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.