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
...

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