Re: using PG with Syslog

[Tony Wasson <ajwasson@xxxxxxxxx>]

On 1/1/06, Tony Caduto <tony.caduto@xxxxxxxxxxxxxxxxxxxx> wrote:
> Hi,
> Does anyone know of a good resource on how to use Postgresql as a
> destination for Syslogd messages?
>
> I am interested in putting all postfix logs to a table rather than a file.

I set this up by using syslog-ng, djb's supervise and psql. I
originally started here, but I recall using 3 or 4 formulas before I
found something that worked and that I liked.

http://www.campin.net/syslog-ng/faq.html

Here's what I did:

1) Get syslog-ng running

2) Make sure you have a source in syslog-ng.conf:

source src { udp(); unix-stream ("/dev/log"); internal(); };

3) Add a destination in syslog-ng.conf file like so:

destination d_pgsql {
   pipe("/var/run/syslog2pg.pipe"
      template("INSERT INTO logs (host, facility, priority, level, tag, date,
                time, program, msg) VALUES ( '$HOST', '$FACILITY',
'$PRIORITY', '$LEVEL', '$TAG',
               '$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );\n"
              )
      template-escape(yes)
      );
};

4) Make a filter in syslog-ng.conf to match exactly what you want in
the database. In this case you just want email, so it is easy.

filter f_filter4     { facility(mail); };

5) Add a log line in syslog-ng.cong to link the source, filter and destination.

log { source(src); filter(f_filter4); destination(d_pgsql); };

6) Make your postgresql database. I also made an insert only user: logfeed.

CREATE DATABASE syslog;
\c syslog

CREATE TABLE logs (
    facility character varying(10),
    priority character varying(10),
    "level" character varying(10),
    tag character varying(10),
    date date,
    "time" time without time zone,
    program character varying(15),
    msg text,
    seq serial NOT NULL,
    host inet
);

CREATE USER logfeed;
GRANT INSERT ON logs to logfeed;

7) Make a file called /usr/local/bin/syslog-db.sh. This creates
/var/run/syslog2pg.pipe.

#!/bin/bash
# Can't remember where I got this from.... -- Tony
PIPE="/var/run/syslog2pg.pipe";
LOG="/var/log/syslog2pg.log";
if [ -e ${PIPE} ]; then
  while [ -e ${PIPE} ]
  do
    # Customize your path, username and database name
     /usr/local/pgsql/bin/psql -q -U logfeed syslog < ${PIPE} > $LOG 2>&1
  done
else
   # Recreate the fifo if it gets the wrong permissions, etc
    mkfifo /var/run/syslog2pg.pipe
    chmod 660 /var/run/syslog2pg.pipe
    chown logfeed.logfeed /var/run/syslog2pg.pipe
  echo "ERROR: fifo not created in ${PIPE}. Please create."
  exit 1
fi

8) I setup /usr/local/bin/syslog-db.sh to be supervised by
daemontools. I made a logfeed user and put a file called "run" for
supervise like so:

#!/bin/bash
exec /usr/local/bin/syslog-db.sh >> /var/log/syslog2pg.log 2>&1

9) Once supervise is running, your process should just work...

Hope this helps!