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