Re: Feature request: separate logging

Le 18 nov. 2016 2:03 PM, "otheus uibk" <otheus(dot)uibk(at)gmail(dot)com> a écrit :
>
> A glaring weakness in Postgresql for production systems is that the
administrator has no way of controlling what types of logs go where. There
are at least two types of logs: errors and statement logs. (I could also
add: connection, syntax error, query duration, audit). It has becomes
increasingly important in the Linux world, with its over reliance on
systemd, that we admins be able to distinguish between these log outputs.
Systemd wants to control all the daemons and services run on a Linux host.
To do this effectively, it needs to capture the stdout and stderr or
possibly parse the external logfile of the daemon it spawns. The benefits
of systemd's journald subsystem include being able to automatically
identify daemons that are failing to start properly. (The merits of systemd
are beyond the scope of this discussion; it is now the all-but-ubiquitous
standard of linux distributions, and has become nearly intractable).
>
> The Postgresql startup process could greatly benefit from systemd. As it
is now, PGDG distributes postgresql with init scripts which really do very
little to check if postgresql bombed shortly after startup. I have improved
upon that script to do some smart detection of a failed startup, but the
result is having two different files to monitor. On the one hand, I want to
use postgresql's built-in file or CSV logging. On the other, I need to
check the output of the startup process. These logs have different formats,
especially for the smart DBA who wants to customize the log prefix with a
more usable timestamp and other fields. Logging to external files is nice
because postgresql rotates the logs for us automatically. (External log
rotation is problematic and risky because it requires a configuration
reload which may lead to undesirable side-effects. Right?)
>
> One alternative is to capture everything into the local logging system,
or to send all to stdout/stderr and capture this with journald (or runit's
svlogd or something). But then you have the following problem: if I enable
statement-level logging or set log_min_duration_statement=0, the in-memory
journald will quickly be overrun with statement logs. The journald
subsystem will then become useless to the other daemons and subsystems.
>
> One solution is proposed in these forums:
https://www.postgresql.org/message-id/flat/etPan(dot)53397e77(dot)643c9869(dot)1a0%40palos#etPan(dot)53397e77(dot)643c9869(dot)1a0(at)palos
> While pgbadger has some nice features, it doesn't really solve the
problem of allowing postgresql to be used with systemd+journald.
>
> What I do today is to configure postgresql to write csvlogs.
Stdout/stderr are captured by journald. A custom perl script with the
Text::CSV module and tail -F semantics continuously processes the csvlog
file, ignores query, dml, and detail log lines, and sends the rest via
syslog() (which journald then handles).
>
> It's not the right way.
>
> I would like to see postgresql to have the ability to
>
> 1. Write to a csvlog with one set of selectors
> 2. Write to stdout/stderr a different set of selectors (no statement,
no autovacuum, etc) using a purely line-oriented output that
>
> 2.1. has the kind of detail contained in the CSV. Currently, the
log-prefix option does not offer some of the information provided in the
CSV logs. Really, the CSV log should simply be an implementation of the
log-prefix.
> 2.2. Collapses multi-lined queries into one line (newlines and
tabs are escaped with backslashes or the x1B character).
>
> Finally, if these changes can be implemented, is it impossible to
backport them to prior versions, say 9.1 and up? If I wrote a patch, under
what conditions would the patch be accepted for inclusion in official
releases of older versions?
>

Only bug fixes are accepted in older releases. What you're proposing isn't
a bug fix.