Hello,
SQL logging is useful for database audit, but there is serveral problems:
* A large amount of logs degrade performance, especially syslog is used. * Text or CSV logs instead of syslog could
improveperformance, but DBAs favor syslog over text logs for integrated monitoring. * Error messages are buried in
SQLlogs. SQL logs will be only needed at auditing, but errors should be detected as soon as possible. * Filtering
logsin syslog is *too late* because we cannot avoid inter-process communication in syslog(), that is performance
bottleneck.
I think we'd better to support log filtering and splitting in PostgreSQL.
For the purpose, I'll submit to add "logging hook" in core, rather than
fixed features like log_min_messages-per-destination.
A logging hook and exporting logging functions
------------------------------------------------
The logging hook is very simple; just adding send_message_to_server_log_hook
in elog.c. In addition, Logging functions are exported so that logging
hook can reuse them in the hook module. For example, pg_write_textlog(),
pg_write_csvlog(), pg_write_stderr(), pg_write_syslog() etc.
A new field "message_format" is added to ErrorData structure.
It references unlocalized message formats passed to errmsg().
It is useful if we want to filter logs by log type.
i.e, we can write LOG-level messages to syslog but avoid
only SQL logs if the format matchs to a specific pattern.
A sample contrib module using logging hook
--------------------------------------------
A module using logging hook is provided as a contrib module.
Useful "fixed features" are provided by the module:
per-destination filtering level: <module>.textlog_min_messages = log <module>.csvlog_min_messages = log
<module>.syslog_min_messages = warning
per-destination line prefix: <module>.textlog_line_prefix = '[%t][%p] ' <module>.syslog_line_prefix =
''
I also have a plan to provide a feature like "log-type specific filter".
It can be used to exclude SQL logs from syslog, but I have not completed
the detailed design.
Do you think the feature is useful and acceptable?
Comments welcome.
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center