Viswamitra's blog

pgbadger is one of the best log analysis tool for postgres database. To use the pgbadger tool we need to set some of the parameters in the postgresql.conf file.

Set the below parameters

——————————————————————————
log_statement=’none’
log_duration=on
log_min_duration_statement = 0
log_connections = on
log_disconnections = on

log_destination=’stderr’
logging_collector=’on’
log_directory=’pg_log’
log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’
log_file_mode = 0600
log_rotation_age=1d
log_rotation_size = 1GB
log_checkpoints = on
log_line_prefix = ‘%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h’
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_error_verbosity = default
log_timezone = ‘Asia/Kolkata’
——————————————————————————-

Restart the postgres cluster

pg_ctl restart

Download pgbadger from “https://github.com/dalibo/pgbadger”

Install pgbadger

tar xzf pgbadger-7.x.tar.gz
cd pgbadger-7.x/
perl Makefile.PL
make && sudo make install

pgbadger command options :

There are lot many options in the pgbadger , these are some of the options which I feel important.

–dbclient host : only report on entries for the given client host.
–dbname database : only report on entries for the given database.
–appname name : only report on entries for given application name
–dbuser username : only report on entries for the given user.

–begin datetime : Start time
–end datetime : End time

–format logtype   : possible values: syslog, syslog2, stderr and csv. Default: stderr.

–incremental :
–jobs number   : number of jobs to run at same time.
–Jobs number     : number of log file to parse in parallel.
–logfile-list file : file containing the list of files to process.
–outfile filename : define the filename for the output
–outdir path : directory where out file must be saved.
–prefix string   : the value of your custom log_line_prefix
–select-only     : only report SELECT queries.
–top number       : number of queries to store/display

Example :

 pgbadger –incremental –outdir /home/postgres/data/pg_log/pgbadger –prefix ‘%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h’ postgresql-2016-08-31_115809.log postgresql-2016-08-30_105346.log

 This will generate the below content in the directory

 /home/postgres/data/pg_log/pgbadger

 -rw-rw-r– 1 postgres postgres    249 Aug 30 12:19 LAST_PARSED
drwxrwxr-x 4 postgres postgres     29 Aug 30 12:19 2016
-rw-rw-r– 1 postgres postgres 686063 Aug 30 12:19 index.html