Re: How could we make it simple to access the log as a table?

On Mon, May 28, 2012 at 11:39 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:
> Stephen Frost <sfrost@snowman.net> writes:
>> This is really where I was hoping to eventually get to with the logging
>> changes that have been discussed over the past couple of years.  We need
>> to have a mechanism to allow logging to different places, based on
>> information included in the log message and/or context, where one of
>> those places could potentially be some kind of receiver process that
>> then dumps the message into a table, either on the local system or on a
>> remote system.
>
> That looks like syslog features?
>
>> What exactly all of that looks like and how it works is a pretty big
>> discussion, but I'd definitely love to hear from others who are
>> interested in changes to our logging infrastructure.
>
> I though about exposing the log CSV files as partitions of a log table,
> each partition being a FDW able to read the file.

All interesting...

We've got several models as to how logs are likely to be captured,
which mean that it'll be difficult to have one uniform answer.

1.  An easy traditional default is to capture logs in a log directory.

An FDW might be able to get at this, with the cost of some additional
configuration.  Unfortunately, that injures the simplicity of this way
of logging.

2.  Ask Syslog

My favorite way to configure *my* PG instances (e.g. - those that I
use for testing) is for them to forward messages to syslog.  That way
they, and my Slony test instances, are all logging to one common
place, rather than the logs getting strewn in a bunch of places.

An FDW that could talk to syslog would be a nifty idea, though there
are enough different syslog implementations around to, again, injure
the simplicity of this.

[Also, mumble, mumble, syslog might be forwarding to a remote server,
further complications...]

3.  Lossy logging is desired by some doing high performance systems
where they can't afford to capture everything

http://archives.postgresql.org/pgsql-hackers/2011-11/msg01437.php

One approach that I know Theo has used has been to throw events onto a
Spread channel, and have a listener pulling and aggregating the events
on a best-efforts basis.  I'm not sure if I should treat that as a
separate answer, or as part of the same.

4.  For a while, I had my syslog set up to capture logs into a
Postgres table.  Very cool, but pretty big slowdown.

What's notably messy, right now, is that we've got a bunch of logging
targets where there's nothing resembling a uniform way of *accessing*
the logs.  It seems to me that the messiness and non-uniformity are
the tough part of the problem.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"