Here I present a simple script that queries the database for the log file locations and automatically chooses the one for yesterday. The trick here is to apply strftime to the log filename as configured in the database. This way, it doesn’t matter how complex the log file name is in the database, it’s alawys easy to guess. All this works as long as the system’s strftime and PostgreSQL’s log file escapes are equivalent; this should never be an issue.

There are some limits to the current script. It assumes log_directory is to be found within data_directory and is not an absolute path. It also assumes there are no strftime escape sequences in the directory names themselves. Fixing either or both of these is not hard if a more general script is needed. It is also hardcoded to choose the csv log file, but this is easy to change.

Finally it runs pgbadger on the log file from yesterday, and outputs html in the webserver’s directory. The assumption here is that PostgreSQL will write its log into a new file every day, possibly in a rotating sequence.

The script is mean to be called every day with cron.

#!/usr/local/bin/zsh

zmodload zsh/datetime

datadir=`/usr/local/bin/psql -A -t -q -w -c "show data_directory" ;`
logdir=`/usr/local/bin/psql -A -t -q -w -c "show log_directory" ;`
filename=`/usr/local/bin/psql -A -t -q -w -c "show log_filename" ;`

strftime -s log $datadir/$logdir/$filename $(( $epochtime[ 1 ] - 24 * 60 * 60 ))

/usr/local/bin/pgbadger -q -I -O /var/www/html/pgbadger/ -o index.html  ${log:r}.csv