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



--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice