Re: Filtering queries by IP

On Mon, Jan 20, 2014 at 5:21 AM, Leonardo M. Ramé <l.rame@griensu.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@%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.ru@gmail.com