pgFouine - a PostgreSQL log analyzer

O pgfouine é um analisador de log do Postgres, foi umas das primeiras tarefas que me deram no mundo do Postgres. Recomento fazer em um servidor de testes varias vezes antes de mexer em um servidor de produção.

Faça por sua conta e risco.

Baixar o PgFouine.

# wget http://pgfoundry.org/frs/download.php/2178/pgfouine-1.1.tar.gz 

Descompactar o PgFouine.

# tar -zxvf pgfouine-1.1.tar.gz 

Mover a PgFouine

# mv pgfouine-1.1 /usr/local/pgfouine 

Configurar o Postgres para gravar Log.

Eu usei esta configuração para o Postgres ( isso não quer dizer que é a mais correta )

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'stderr'              # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on                  # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'pg_log'                # directory where log files are written,
                                        # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_000000.log' # log file name pattern,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
log_truncate_on_rotation = off          # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
log_rotation_age = 1d                   # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
#log_rotation_size = 10MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#silent_mode = off                      # Run server silently.
                                        # DO NOT USE without syslog or
                                        # logging_collector
                                        # (change requires restart)
# - When to Log -
#client_min_messages = notice           # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   log
                                        #   notice
                                        #   warning
                                        #   error
log_min_messages = info                 # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic
log_min_error_statement = notice        # values in order of decreasing detail:
                                        #   debug5
                                        #   debug4
                                        #   debug3
                                        #   debug2
                                        #   debug1
                                        #   info
                                        #   notice
                                        #   warning
                                        #   error
                                        #   log
                                        #   fatal
                                        #   panic (effectively off)
log_min_duration_statement = 100  # -1 is disabled, 0 logs all statements
                                  # and their durations, > 0 logs only
                                  # statements running at least this number
                                  # of milliseconds
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
log_duration = on
log_error_verbosity = verbose           # terse, default, or verbose messages
#log_hostname = on
log_line_prefix = '%t [%p]: [%l-1] '    # special values:
                                        #   %a = application name
                                        #   %u = user name
                                        #   %d = database name
                                        #   %r = remote host and port
                                        #   %h = remote host
                                        #   %p = process ID
                                        #   %t = timestamp without milliseconds
                                        #   %m = timestamp with milliseconds
                                        #   %i = command tag
                                        #   %e = SQL state
                                        #   %c = session ID
                                        #   %l = session line number
                                        #   %s = session start timestamp
                                        #   %v = virtual transaction ID
                                        #   %x = transaction ID (0 if none)
                                        #   %q = stop here in non-session
                                        #        processes
                                        #   %% = '%'
                                        # e.g. '<%u%%%d> '
log_lock_waits = off                    # log lock waits >= deadlock_timeout
log_statement = 'all'                   # none, ddl, mod, all
#log_temp_files = -1                    # log temporary files equal or larger
                                        # than the specified size in kilobytes;
                                        # -1 disables, 0 logs all temp files
#log_timezone = '(defaults to server environment setting)'
-------------------------------------------------------------------------------

Criar a pasta analizer dentro do apache

# mkdir /var/www/analizer/

Reinicie o apache

# apachectl restart 

Exemplo para gerar relatório

# pgfouine.php -file /dados/pgsql/data/pg_log/postgresql-2012-08-10_135031.log > /var/www/analizer/relatorio_pgbench.html -debug -logtype stderr

Boa Sorte!

Já tem uma ferramenta nova e já fiz um novo post sobre o pgBadger