Filtering queries by IP

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Jan 20, 2014 at 6:20 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:

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

In addition to what Sergey has posted above, you could also run your logs
through PgBadger [1]https://github.com/dalibo/pgbadger, 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