Home / server / Questions / 128284
In Process
EMP
Asked: 2010-04-01 15:06:42 +0800 CST

How to see active connections and "current activity" in PostgreSQL 8.4

  • 772

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.

postgresql

6 Answers

  1. OK, got it from someone else. This query should do the trick:

    select *
from pg_stat_activity
where datname = 'mydatabasename';
    • 195

  2. See also pg-top, 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
    • 33

  3. Reference taken from this article. 

    SELECT 
    pid
    ,datname
    ,usename
    ,application_name
    ,client_hostname
    ,client_port
    ,backend_start
    ,query_start
    ,query  
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid<>pg_backend_pid();
    • 19

  4. Two ways to monitor in Ubuntu 18.04, just for reference

    One using pg_top:

    $ sudo apt-get install ptop
$ pg_top # similar to top as others mentioned

    Two using pgAdmin4:

    $ sudo apt-get install pgadmin4 pgadmin4-apache2
# type in password and use default url
$ pgadmin4

    In the dashboard, check the total/active as

    enter image description here

    • 4

  5. PostgreSQL ASH Viewer (PASH Viewer) provides graphical view of active session history data. https://github.com/dbacvetkov/PASH-Viewer It's free and open source.

    ScreenShot

    • 2

  6. DBeaver is also showing the active/idle/total server sessions very nicely.

    • 1