Thread: Filtering queries by IP

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?.

Regards,
--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 351 6629292

On Mon, 2014-01-20 at 10:21 -0300, Leonardo M. Ramé 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?.
>

Not by default, but you can use this extension to do that:

http://pgxn.org/dist/pg_log_userqueries/1.0.0/

And I'm interested to know if it helped you or not, and how I can make
it even better.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

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