Re: How to view the activity of postgresql

On Sat, Apr 30, 2016 at 3:14 PM, <richard(at)xentu(dot)com> wrote:

> On 2016-04-28 13:50, Keith wrote:
>
>> On Apr 28, 2016 2:42 AM, <richard(at)xentu(dot)com> wrote:
>>
>>>
>>> On 2016-04-28 07:18, Wei Shan wrote:
>>>
>>>>
>>>> you can try pgbadger.
>>>>
>>>> https://github.com/dalibo/pgbadger [1] [3]
>>>>
>>>>
>>>> On 28 April 2016 at 14:13, <richard(at)xentu(dot)com> wrote:
>>>>
>>>> I want to see what statements are being executed on a remote
>>>>> postgresql server, ideally in a scrolling display in some gui
>>>>>
>>>> tool.
>>
>>> In MSSQL, there is a profiler application that gives this.
>>>>>
>>>>> The best I've found so far is to set postgresql to log to a csv
>>>>>
>>>> file
>>
>>> & then use pg_read_file to periodically read the log file &
>>>>>
>>>> display
>>
>>> it to the user.
>>>>>
>>>>> I've written a little tool that does that:
>>>>> http://www.xentu.com/pgprofiler/ [2] [1]
>>>>>
>>>>>
>>>>> However, it seems a very akward way to achieve what I'm looking
>>>>>
>>>> for
>>
>>> and will probably slow the server with all the file reading &
>>>>> writing involved.
>>>>>
>>>>> Is there a more efficient way of doing this?
>>>>>
>>>>> --
>>>>> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
>>>>> To make changes to your subscription:
>>>>> http://www.postgresql.org/mailpref/pgsql-novice [3] [2]
>>>>>
>>>>
>>>>
>>>> --
>>>>
>>>> Regards,
>>>> Ang Wei Shan
>>>>
>>>>
>>> Thanks Ang,
>>>
>>> As far as I can see, this is a postmortum analysis of the log files.
>>>
>> I want to somehow see the statements as they get received by the
>> server, as if I were tailing the log file.
>>
>>>
>>> Regards
>>> Richard
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-novice [3]
>>>
>>
>> Check out pg_activity
>>
>> https://github.com/julmon/pg_activity [4]
>>
>> Keith
>>
>>
> I've taken a look at what pg_activity does.
> It periodically queries the pg_stat_activity. From the docs:
>
> 'The pg_stat_activity view will have one row per server process, showing
> information related to the current activity of that process.'
>
> So, that's not, I don't think, going to give a record of all the
> statements getting executed. If a statement gets executed quicker than the
> interval at which pg_stat_activity is getting queried, I'd miss it.
>
> In fact, pg_activity does exactly that, quickly executed statements don't
> get displayed.
>
>
> regards
>
> Richard
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

You didn't specify that you wanted a scrolling display that also keeps a
record of queries run in the past as well. The only tool that I'm aware of
that does real-time monitoring + logging is VividCortex which is a
commercial product.

https://www.vividcortex.com/

I've used it myself and it's quite useful if you need real-time inspection
into every single thing that is running on the system at all times. You can
get something close to this with the pg_stat_statements contrib module, but
that only stores parametrized counts of all queries that have run. That
plus pgbadger with increased logging from postgres itself has worked pretty
well for me when a free solution is desired.