Обсуждение: How to view the activity of postgresql
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/ 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?
you can try pgbadger.
https://github.com/dalibo/pgbadger
--
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/
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
Regards,
Ang Wei Shan
Ang Wei Shan
On 2016-04-28 07:18, Wei Shan wrote: > you can try pgbadger. > > https://github.com/dalibo/pgbadger [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/ [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 [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
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 [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/ [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 [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
Check out pg_activity
https://github.com/julmon/pg_activity
Keith
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] [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] [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] [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] > > Check out pg_activity > > https://github.com/julmon/pg_activity [4] > > Keith > Thanks Keith, Have you actually got pg_activity working? I gave up trying to install it on xp, but have done so on my Ubuntu machine, but when I try to connect to a remote postgresql server, I get an error: $ pg_activity -U postgres -h myserver.com FATAL: password authentication failed for user "postgres" I'm offered no opportunity to enter the postgres user's password and there seems to be no command line option to supply it. From this location, I can certainly connect to the remote server as postgres, in fact I've got pgAdmin installed on this machine that is set up to do just that. workstation: Ubuntu 14.04 pg_activity 1.1.1 server: Windows Server 2012 R2. postgresql 9.4.5 Richard
On 2016-04-29 09:27, richard@xentu.com wrote: > $ pg_activity -U postgres -h myserver.com > FATAL: password authentication failed for user "postgres" > > I'm offered no opportunity to enter the postgres user's password and > there seems to be no command line option to supply it. Apologies, I've got it! It expects a PGPASSWORD environment variable. I'll have a play with it to see if it does what I'm needing.
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] [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] [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] [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] > > 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
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:tool.
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@xentu.com> wrote:I want to see what statements are being executed on a remote
postgresql server, ideally in a scrolling display in some guifileIn MSSQL, there is a profiler application that gives this.
The best I've found so far is to set postgresql to log to a csvdisplay& then use pg_read_file to periodically read the log file &forit 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 lookingI want to somehow see the statements as they get received by theand 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] [2]
--
Regards,
Ang Wei Shan
Thanks Ang,
As far as I can see, this is a postmortum analysis of the log files.
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]
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@postgresql.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.
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
On Mon, May 2, 2016 at 11:05 AM, <richard@xentu.com> wrote:
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:tool.
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 guiIn MSSQL, there is a profiler application that gives this.file
The best I've found so far is to set postgresql to log to a csv& then use pg_read_file to periodically read the log file &displayit to the user.for
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 lookingand 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
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
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
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