Re: How to view the activity of postgresql

On Mon, May 2, 2016 at 11:05 AM, <richard(at)xentu(dot)com> wrote:

> On 2016-05-01 01:58, Keith wrote:
>
>> 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] [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] [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] [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] [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(at)postgresql(dot)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
>
>
>
> --
> 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
>

The pg_stat_statements contrib module is the closest thing you will get
that is built in, but as I said, that only logs a parametrized version.
Recommend reading up on it in case it provides enough for your needs.

http://www.postgresql.org/docs/9.5/static/pgstatstatements.html

Another alternative would be to use the file_fdw that comes with 9.1+.
Would have to change the log output to something more easily parsed (syslog
or csv). Would also need to have a process that would set up a new foreign
table whenever a new log file is generated.

http://www.postgresql.org/docs/current/static/file-fdw.html

Honestly, though, it seems Vividcortex is doing exactly what you're looking
for, so I'd recommend giving them a try. What you're asking for takes a lot
of work and nothing wrong with paying for something that gives you exactly
what you want. SQL Server itself isn't free, which provided the feature you
are looking for now. :)

Keith