Re: How to view the activity of postgresql

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