3

I want to trace all the queries done from Web Application to PostgreSQL server during a specific time interval. I have searched all over the internet and found below 3 approaches:

  1. pg_stat_activity which traces only current running queries
  2. pg_stat_statements which traces all the queries(including queries running internally on postgresql server). Below is the snapshot of pg_stat_activity. I am hardly getting anything useful from this

enter image description here

  1. Database logs (i think it will log all the queries)

But I want to trace the queries done from my web application.

how can i do that?

YogeshR
  • 1,606
  • 2
  • 22
  • 43
  • http://dalibo.github.io/pgbadger/ and https://wiki.postgresql.org/wiki/Monitoring –  Mar 02 '17 at 12:05
  • 2
    See this question http://stackoverflow.com/questions/722221/how-to-log-postgresql-queries. For you case `SET LOCAL log_statement = 'all'` before running queries should do what you need. – Roman-Stop RU aggression in UA Mar 03 '17 at 00:44
  • @RomanKonoval I enabled logging. But log file is full of internal system queries like this: 2017-03-03 13:07:04 IST LOG: statement: SELECT (SELECT sum(blks_read) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = 12066)) AS "Reads", (SELECT sum(blks_hit) FROM pg_stat_database WHERE datname = (SELECT datname FROM pg_database WHERE oid = 12066)) AS "Hits" How can I log queries done from the web application? – YogeshR Mar 03 '17 at 07:29
  • Default settings in postgresql.conf should be to not log queries `log_statement = 'none'` and then you should enable logging in your application by executing `set local log_statement = 'all'` on a connection before you execute queries – Roman-Stop RU aggression in UA Mar 03 '17 at 08:22
  • @RomanKonoval this won't require PostgreSQL restart.....right? – YogeshR Mar 03 '17 at 09:27
  • 1
    `SET LOCAL` changes configuration setting till the end of current transaction so of cause it does not require restart. – Roman-Stop RU aggression in UA Mar 03 '17 at 11:21

0 Answers0