PostgeSQL can log to syslog using appropriately formated messages.
Configure your postgresql.conf file to include the line
log_destination = 'syslog'
and then you will get lines in syslog like;
2009-09-29T12:42:10-07:00 hostname facility.level postgres[20288]: [32-1] postgresql statement
then all of the syslog-ng parsing, macros and templates will work correctly.
Evan.
James Kelly wrote:
> Thanks. This is exactly what is happening. If, using a template, I
> just use $PROGRAM, I get a bunch of lines with just "insert".
>
> If I use something like the following:
>
> template t_postgres_msgs {
> template("$ISODATE $HOST $PROGRAM $MSG \n"); };
>
> I no longer see the colons. However, if something is matched by the
> filter, it still prints a blank message (hence, wasting a ton of space
> and clogging up the logs). For example, this is what I get instead of
> the matched sql statement:
>
> 2009-09-29T10:30:30-04:00 hcdb1-rep2
>
> I thought the filter would just "trash" or not record the message at
> all, not print the host and timestamp for each filtered message.
>
> Thanks a lot... I appreciate this help a lot.
> James
>
> On Tue, Sep 29, 2009 at 10:05 AM, Srinivasan Sreenivasan
> <srinivasan.srinivasan@sabre.com
> <mailto:srinivasan.srinivasan@sabre.com>> wrote:
>
> I think Syslog-ng thinks insert is a program name. Use a template
> with $PROGRAM in it to see if it prints “insert” to confirm this.
>
> Solution:
> Send a program name before your sql statement using a template.
>
>
>
> On 9/29/09 8:57 AM, "James Kelly" <james.kelly@hmsinc.com
> <http://internal.host.com> <http://internal.host.com> "); };> <mailto:james.kelly@hmsinc.com>> wrote:
>
> Hello,
>
> I am attempting to implement syslog-ng on our servers hosting
> postgresql databases. The general idea is to log "too much" at
> the database level and then, using syslog filters, reduce it to
> the information we want to actually hold in the syslog and send
> to the log server. So far I am extremely pleased with how easy
> it is to implement and well documented. However, I do have one
> problem that is making it difficult to use.
>
> The problem is for each message that the filter matches, it does
> not completely drop the message.. rather, it logs the date /
> server / and a colon:
>
> For example, this is what I get for a message that is matched by
> a filter:
>
> "Sep 29 09:43:29 hcdb1-rep2 :"
>
> I notice that even with the unmatched statements, there is a
> colon. For example, in the postgresql log, I see:
>
> insert into "public"."table"(blah,blah,blah) values
> (blah,blah,blah);
>
> but the same message once captured from syslog shows the
> following in the log and logserver:
>
> Sep 29 09:43:29 hcdb1-rep2 insert: into
> "public"."table"(blah,blah,blah) values (blah,blah,blah);
>
> *Note the colon after "insert".* I can't seem to figure out
> where this is coming from or how to avoid it. It also causes
> some filtering problems that I won't go into here so as to not
> confuse the issues, but safe to say it is also related to the colon.
>
> I have spent a lot of time trying to figure this out and am at a
> dead-end. Due to the amount of messages I need to filter out
> (below is just one of the many filters I need to put in and have
> tested with the same result), not being able to completely
> filter these out is a killer.
>
> I am using the 3.0.4 open-source edition on Ubuntu 8. Here is
> my config:
>
> ******************************************************************************************
> @version: 3.0
>
> options {
> };
>
> ######
> # sources
> source s_local {
> # message generated by Syslog-NG
> internal();
> # standard Linux log source (this is the default place for the
> syslog()
> # function to send logs to)
> unix-stream("/dev/log");
> # messages from the kernel
> file("/proc/kmsg" program_override("kernel: "));
> file("/var/log/postgresql/postgresql-8.3-main.log");
> };
>
>
> ######
> # destinations
> destination d_messages { file("/var/log/messages"); };
>
> #####
> # filters
>
> filter f_inserts2 {
> not match("_health_central" value("MESSAGE"));
> };
>
>
> destination d_logserver { tcp("internal.host.com
>--
>
>
> log {
> source(s_local);
> filter(f_inserts2);
> destination(d_messages);
> #destination(d_logserver);
> };
> ******************************************************************************************
>
> Thanks!
> James Kelly
>
> ------------------------------------------------------------------------
> ______________________________________________________________________________
> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
> Documentation:
> http://www.balabit.com/support/documentation/?product=syslog-ng
> FAQ: http://www.campin.net/syslog-ng/faq.html
>
>
> ______________________________________________________________________________
> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
> Documentation:
> http://www.balabit.com/support/documentation/?product=syslog-ng
> FAQ: http://www.campin.net/syslog-ng/faq.html
>
>
>
Evan Rempel erempel@uvic.ca
Senior Programmer Analyst 250.721.7691
Unix Services, University Systems, University of Victoria
______________________________________________________________________________
Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng
FAQ: http://www.campin.net/syslog-ng/faq.html