How to see active connections and “current activity” in PostgreSQL 8.4

Question

I'm investigating an issue with DB connections being left open indefinitely, causing problems on the DB server. How do I see currently open connections to a PostgreSQL server, particularly those using a specific database? Ideally I'd like to see what command is executing there as well. Basically, I'm looking for something equivalent to the "Current Activity" view in MSSQL.

Answer 1

OK, got it from someone else. This query does the trick:

select *
from pg_stat_activity
where datname = 'mydatabasename'

Answer 2

from PostgreSQL 9.0 on you will even see the name of the application causing the problem. maybe this is helpful for future checks.

Answer 3

See also [pg-top][1], which acts like top except it shows postgres activity.

• Install pg-top (in Debian, the package is called "ptop").
• Become the postgres user (e.g. sudo su postgres)
• Run pg_top