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
Top comments (0)