Thread: get number and names of processes connected to postgresql
Basically, I got bunch of local processes connecting to postgresql, need to aggregate some sort of report about number of connections and its origin every so often. pg version is 8.3 Any ideas if there's tools to gather that info on linux ? Netstat is the only one I know, but I have to parse/awk its output to get something meaningful out of it. Ideas are welcomed. -- GJ
On Fri, Sep 23, 2011 at 13:34, Gregg Jaskiewicz <gryzman@gmail.com> wrote: > Basically, I got bunch of local processes connecting to postgresql, > need to aggregate some sort of report about number of connections and > its origin every so often. The pg_stat_activity system view gives you the database name (datname) and user name (usename) they're connected with. client_addr tells you what IP address they're connecting from. Generally you would use different usernames and databases for different applications, so that should give you a good enough picture? You can even run aggregate queries over this view so such a report is just a matter of writing SQL :) PostgreSQL 9.0 also adds a "application_name" variable that each connection can set that's visible in pg_stat_activity. Regards, Marti
My apps share same databases, so no good in that. And I am very well aware of the new feature in 9.0 - but we're stuck in the 8.3 land for now. So far I managed to hack together a netstat+awk+other command line tools to get that information. (in your face - windows "server" developers/admins :P)
On Sun, Sep 25, 2011 at 00:07, Gregg Jaskiewicz <gryzman@gmail.com> wrote: > My apps share same databases, so no good in that. How about different users? You can create a separate user for each application, and then GRANT them access to a single role. Regards, Marti
On 09/24/11 2:07 PM, Gregg Jaskiewicz wrote: > My apps share same databases, so no good in that. And I am very well > aware of the new feature in 9.0 - but we're stuck in the 8.3 land for > now. you can still give the various apps different user accounts (roles) even if they all have to have the same permissions (perhaps by being members of the role that has the actual permissions). We're usually more specific about our roles. and, do note, the pg_stat_activity table has the process ID as well as the client port. netstat etc won't do you any good at all if any of the connections are remote. -- john r pierce N 37, W 122 santa cruz ca mid-left coast