On Mon, Jan 20, 2014 at 6:20 PM, Sergey Konoplev <gray.ru@xxxxxxxxx> wrote:
On Mon, Jan 20, 2014 at 5:21 AM, Leonardo M. Ramé <l.rame@xxxxxxxxxxx> 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@xxxxxxxxx
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
[1] https://github.com/dalibo/pgbadger
