PostgreSQL Log Analyze using pgBadger

Enable log for PostgreSQL

sudo {{ texteditor.value }} /etc/postgresql/9.5/main/postgresql.conf

log_min_duration_statement = 0
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

sudo /etc/init.d/postgresql restart
tail -f /var/log/postgresql/postgresql-9.5-main.log

Install pgBadger

pgbadger

sudo apt-get install make
 
wget https://github.com/dalibo/pgbadger/archive/v9.0.tar.gz
tar xzf v9.0.tar.gz && cd v9.0/
perl Makefile.PL && make && sudo make install
 
pgbadger /var/log/postgresql/postgresql-9.5-main.log

PostgreSQL Log Rotate

sudo {{ texteditor.value }} /etc/logrotate.d/postgresql-common

/var/log/postgresql/*.log {
       size 10M
       rotate 1
       copytruncate
       delaycompress
       notifempty
       missingok
       su root root
}