Re: Filtering queries by IP

On Mon, Jan 20, 2014 at 6:20 PM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:

> On Mon, Jan 20, 2014 at 5:21 AM, Leonardo M. Ramé <l(dot)rame(at)griensu(dot)com>
> wrote:
> > Hi, I'm trying to find the cause of slow performance on some screens of
> > an application. To do that, I would like to be able to log all the
> > queries made by an specific IP addres, is this possible?.
>
> I don't think it's possible with pure postgres. However, you can
> temporarily turn all statements logging by
>
> set log_min_duration_statement to 0;
>
> then collect enough logs and turn it back by
>
> set log_min_duration_statement to default;
>
> Also set log_line_prefix to '%t %p %u(at)%d from %h [vxid:%v txid:%x]
> [%i] ' in the config file, it will give you a lot of useful
> information including host data. And turn log_lock_waits on as it
> might be useful when your slow queries are waiting for something.
>
> And finally, this gotcha will flatten all the multi-line log records
> and filter them by a specified IP.
>
> DT='2013-11-21'
> SUB='192.168.1.12'
>
> rm tmp/filtered.log
> if [ ! -z $SUB ]; then
> cat /var/log/postgresql/postgresql-$DT.log | \
> perl -pe 's/(^\d{4}-\d{2}-\d{2} )/###$1/; s/\n/@@@/; s/###/\n/' | \
> grep -E "$SUB" | perl -pe 's/@@@/\n/g' >tmp/filtered.log
> fi
>
> --
> Kind regards,
> Sergey Konoplev
> PostgreSQL Consultant and DBA
>
> http://www.linkedin.com/in/grayhemp
> +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
> gray(dot)ru(at)gmail(dot)com
>
>
In addition to what Sergey has posted above, you could also run your logs
through PgBadger [1], using a log_line_prefix similar to what is suggested
by Sergey, and then filter by "--include-query" regex. I've never tried,
but glancing at PgBadger's docs it looks like it should work more or less.

[1] https://github.com/dalibo/pgbadger