Re: Monitoring Connections

On 9/20/06, Tomeh, Husam <htomeh(at)firstam(dot)com> wrote:
>
> Make that: http://www.postgresql.org/docs/8.1/static/monitoring.html
> 8.1 docs rather.. :)
>
> --
> Husam
> http://firstdba.googlepages.com
>
> ------------------------------
> *From:* pgsql-admin-owner(at)postgresql(dot)org [mailto:
> pgsql-admin-owner(at)postgresql(dot)org] *On Behalf Of *Tomeh, Husam
> *Sent:* Wednesday, September 20, 2006 11:22 AM
> *To:* Aaron Bono; pgsql admin
> *Subject:* Re: [ADMIN] Monitoring Connections
>
> Query the pg_stat_activity.
> For more detailed stats activities, you may query the pg_stat_* and
> pg_statio_* system views.
>
> http://www.postgresql.org/docs/8.0/static/monitoring.html
>
> --
> Husam
> http://firstdba.googlepages.com
>
>
> ------------------------------
> *From:* pgsql-admin-owner(at)postgresql(dot)org [mailto:
> pgsql-admin-owner(at)postgresql(dot)org] *On Behalf Of *Aaron Bono
> *Sent:* Wednesday, September 20, 2006 10:48 AM
> *To:* pgsql admin
> *Subject:* [ADMIN] Monitoring Connections
>
> Is there a way to get diagnostics on current connections to the database?
> Specifically I am looking for:
>
> 1. How many current connections are there?
> 2. Who is connected (user name, database and from what IP address)?
> 3. When was the last time the connection was used?
> 4. When was the connection established?
>
> I can get most of this information from the ps command but am having
> problem with getting the information on #3.
>
> My problem is that I have a number of web sites running on the server all
> using different databases and/or user log ins and also using connection
> pooling. Whenever I redeploy an application, the "old" connections do not
> appear to disconnect but new connections are established. I fear that,
> since the application server is not actually restarting but just reloading
> the application, it is keeping old connections open when they really should
> be closed. This is something I will need to fix in the application but I
> need to do some digging before I know this is indeed the problem.
>
> I am using PostgreSQL 8.1.3 on CentOS.
>
> Thanks!
>

I checked the pg_stat_activity table and it doesn't give any more
information than I got from the ps command.

Is there any way to find out the last time the connection was used?

Thanks,
Aaron

==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================