Re: How to view the activity of postgresql - Mailing list pgsql-novice

On 2016-05-01 01:58, Keith wrote:
> On Sat, Apr 30, 2016 at 3:14 PM, <richard@xentu.com> wrote:
>
>> On 2016-04-28 13:50, Keith wrote:
>> On Apr 28, 2016 2:42 AM, <richard@xentu.com> wrote:
>>
>> On 2016-04-28 07:18, Wei Shan wrote:
>>
>> you can try pgbadger.
>>
>> https://github.com/dalibo/pgbadger [1] [1] [3]
>>
>> On 28 April 2016 at 14:13, <richard@xentu.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] [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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-novice [3] [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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-novice [3] [3]
>
> Check out pg_activity
>
> https://github.com/julmon/pg_activity [4] [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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice [3]
>
> 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/ [5]
>
> 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.
>

Thanks Keith,

I was hoping that the postgresql server itself provided some way of
getting this information. Some way that a postgresql client application
could ask the server for all received statements. Seems there is no such
functionality.

Is there any way of logging all statements, in all databases, to a
table, in addition to writing a log file? Then a client app could read
from that, deleting as it does so.

Regards
Richard