Re: postgresql log parsing to report on user/db access

On 04/25/2012 06:36 AM, Larry J Prikockis wrote:
> Is anyone aware of a quick solution for producing user/db access reports
> from pgsql syslog format logs?
>
> in other words, I have a bunch of lines such as:
>
>> Apr 22 06:39:04 147283-db3 postgres[13252]: [1800-1] user=database1_remote,db=sqm_remote_database1 LOG:  connection
authorized:user=database1_remote database=sqm_remote_database1... 
> and I'd like to be able to generate weekly reports that provide rolled
> up statistics on which user connected to which db, how many times, etc.
>
What is the etc? You should be able to parse it pretty quickly with awk
but if you want additional stats like length of time a user connected it
will involve additional work.

Have you considered logging to the csv format?
http://www.postgresql.org/docs/9.1/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG

This may be an easier approach as you can just pull the data into a
table and report from there. Of course you can pre-filter the CSV with
grep or similar to avoid importing uninteresting lines.

Cheers,
Steve