Re: monitoring sql queries

On 17 Listopad 2011, 17:32, hubert depesz lubaczewski wrote:
> On Thu, Nov 17, 2011 at 09:29:11AM -0700, J.V. wrote:
>> I am in need of a tool or method to see each/every SQL query that
>> hits the PostgreSQL database. By query I mean the query in SQL
>> syntax with all the parameters passed.
>>
>> What I want to do is:
>> 1) see the query
>> 2) Determine how long the query takes to execute
>> 3) Possibly log both of the above to a log file
>>
>> Is there any tool internal to PostgreSQL that would allow me to do this?
>>
>> I cannot monitor it from the code that is actually connecting &
>> sending the query.
>>
>> any help or pointers would be greatly appreciated.
>
> just enable logging of queries.

As depesz mentioned, there's a log_min_duration GUC, that allows you to
log queries that exceed some time interval. If you want to log all
queries, you may set this to 0 but it may easily fill your log with
garbage.

There are two contrib modules that might help you - pg_stat_statements and
auto_explain. The former one is probably more interesting in this case.

Tomas