Tuesday, July 13 2010
PostgreSQL

Logs analysis

Nowadays to analyze logs and provide insights, the more common tool to use is pgfouine, which does an excellent job. But there has been some improvements in logs capabilities that we're not benefiting from yet, and I'm thinking about the CSV log format.

So the idea would be to turn pgfouine into a set of SQL queries against the logs themselves once imported into the database. Wait. What about having our next PostgreSQL version, which is meant (I believe) to include CSV support in SQL/MED, to directly expose its logs as a system view?

A good thing would be to expose that as a ddl-partitioned table following the log rotation scheme as setup in postgresql.conf, or maybe given in some sort of a setup, in order to support logrotate users. At least some facilities to do that would be welcome, and I'm not sure plain SQL/MED is that when it comes to source partitioning.

Then all that remains to be done is a set of SQL queries and some static or dynamic application to derive reports from there.

This is yet again an idea I have in mind but don't have currently time to explore myself, so I talk about it here in the hope that others will share the interest. Of course, now that I work at 2ndQuadrant, you can make it so that we consider the idea in more details, up to implementing and contributing it!