Re: using PG with Syslog

On 1/1/06, Tony Caduto <tony(dot)caduto(at)amsoftwaredesign(dot)com> 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!