Home / server / Questions / 935288
In Process
Massimo
Asked: 2018-10-13 08:00:53 +0800 CST

postgres log workflow : filter the log events with specific working hours

  • 772

To analyze postgres logs, our workflow is:

  1. postgres logs via syslog
  2. rsyslog writes to one text file
  3. at midnight we stop postgres
  4. pgbadger reads and parses the daily logfile (about 2 Gbyte)

We want to esclude some outliers, for examples when the application starts, because for us they are useless data and just pollute the logfile.

To accomplish this: how/where can I specify a working hours (from..to) filter ?

The ideal solution is to set the filter in postgres.

Less than ideal, filter at rsyslog.

Last choice is to filter at pgbadger: the html output will be trimmed, but our servers keep writing fat logfiles.

current rsyslog configuration:

:msg, contains, "connection authorized: user=root database=root" ~
:msg, contains, "FATAL:  database \"root\" does not exist" ~
local0.*    -/var/log/postgresql.log
postgresql

1 Answers

  1. You can specify a filter using the time in rsyslog.conf using the system properties holding the current time, like hour and minute. For example,

    if $$hour >= '10' and $$hour <='17' then /var/log/output

    As I understand it, the hour and minute values are 2 character strings with leading zero.

    You can combine this with a test for the facility (local0 and so on), for example:

    if ( $syslogfacility-text=='local0' ) and ( $$hour <= '10' or $$hour>='20' ) then stop

    (where stop is the newer way of saying ~), or if you prefer

    if ( $syslogfacility-text=='local0' ) and ( $$hour > '10' and $$hour<'20' ) then /var/log/output

    Note that these filters must be on one line starting in the first column.

    • 1