PgFouine Automatic Report Setup with PostgreSQL & Logrotate

Monitoring & analysing your PostgreSQL server logs with PgFouine provides you with a way to see which queries are performing badly and need optimisation. It can also provide insight into busy periods and give some basic query stats such as the number of INSERT, DELETE, UPDATE, and SELECT operations.

Requirements

• PgFouine
• php
• Logrotate
• PostgreSQL setup to log to syslog (Tested with PostgreSQL 8.3 but should work with earlier versions)

PostgreSQL Setup

First you need to setup PostgreSQL to log to syslog. Set the following in your postgresql.conf

log_destination = 'syslog'

# Ensure that the following lines are REMMED out
#
# logging_collector
# log_directory
# log_filename
# log_truncate_on_rotation

You may set the “syslog_facility” and “syslog_ident” options if you wish, but i handle these items in the syslog-ng.conf file.

You will also need to tell Postgres what to log

client_min_messages = notice            # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   log
                                        #   notice
                                        #   warning
                                        #   error

log_min_messages = notice               # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic

log_error_verbosity = default           # terse, default, or verbose messages

log_min_error_statement = debug2        # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic (effectively off)

log_min_duration_statement = 200        # -1 is disabled, 0 logs all statements
                                        # and their durations, > 0 logs only
                                        # statements running at least this time.
log_line_prefix = '%t %d %u %h '

I have a fairly busy DB server so I am only interested in queries that take longer then 200ms. You will need to set this value to suit your needs, set it to low and you will produce many log lines, set it to high and you wont produce anything.

Syslog Setup

Next you need to setup syslog to log PostgreSQL to a different file (the following instruction will work for Syslog-ng on an OpenSUSE box, your milage may vary).

Setup a filter:

filter f_postgres   { match('^postgres'); };

And a Destination:

# PostgreSQL messages
destination postgres { file("/var/log/pgsql/postgresql.log"); };
log { source(src); filter(f_postgres); destination(postgres); flags(final); };

Scripts

Create the following as a script for bash. Ensure you setup the execute bit on the file ie “chmod +x pgfouine_process”.

#!/bin/bash

PGFOUINE="/srv/www/htdocs/pgfouine/pgfouine.php"
DATESTAMP=$(date +%G%m%d)
YEAR=$(date +%G)
MONTH=$(date +%m)
REPORTDIR="/srv/www/htdocs/reports"
PGSQLLOG="/var/log/pgsql/postgresql.log"

mkdir $REPORTDIR/$YEAR/$MONTH -p

php $PGFOUINE -file $PGSQLLOG > $REPORTDIR/$YEAR/$MONTH/$DATESTAMP.htm

You may customise this script to suit your needs depending on where you put the PgFouine files and where you want to store/serve your reports from. You may also with to send the report via email once it is generated, the sky is the limit (ok, sorry for the cliche).

Logrotate

To execute this script each time the log file is rotated put the following file into your /etc/logrotate.d directory.

/var/log/pgsql/postgresql.log {
    compress
    dateext
    rotate 99
    daily
    notifempty
    missingok
    create 640 postgres users
    copytruncate
    prerotate
        /var/lib/pgsql/bin/pgfouine_process
    endscript
}

The important thing for this is the prerotate section. This will run the script first before rotating the log file.

If you don’t have Logrotate setup you could easily run the pgfouine script on a daily basis with Cron.