From: | Tony Wasson <ajwasson(at)gmail(dot)com> |
---|---|
To: | Tony Caduto <tony(dot)caduto(at)amsoftwaredesign(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: using PG with Syslog |
Date: | 2006-01-02 22:45:13 |
Message-ID: | 6d8daee30601021445t6a99e96dr6bd69fb96c5c3a1b@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Guy Rouillier | 2006-01-03 03:06:01 | Re: INSERT OR UPDATE |
Previous Message | SunWuKung | 2006-01-02 22:34:47 | inserting many rows |