146

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.

1
  • from PostgreSQL 9.0 on you will even see the name of the application causing the problem. maybe this is helpful for future checks. Apr 5, 2010 at 18:19

6 Answers 6

196

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

select *
from pg_stat_activity
where datname = 'mydatabasename';
34

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
4
  • So hard to find a windows executable. You (almost) never have the full GCC suite or Mingw on a Windows server (not my choice, don't blame me).
    – oligofren
    May 22, 2019 at 15:29
  • @oligofren Running Postgresql on Windows is a bit of an adventure, if for no other reason than that so many of the utilities and tools are written for Linux. May 22, 2019 at 15:45
  • 3
    On buster, the debian package is called pgtop
    – phihag
    Mar 7, 2020 at 12:28
  • The link is dead.
    – RonJohn
    Mar 7, 2023 at 17:38
19

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();
1
  • query and pid were named current_query and procpid, and state is not available in pg_stat_activity prior to PostgreSQL 9.2.
    – ohmu
    May 3, 2016 at 3:27
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

2

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

1

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .