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.

Improve this question
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.
    – postgresql007
    Apr 5, 2010 at 18:19

6 Answers 6

Reset to default
196

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

select *
from pg_stat_activity
where datname = 'mydatabasename';
Improve this answer
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
Improve this answer
5
  • 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.
    – Wayne Conrad
    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 at 17:38
  • @RonJohn I fixed the link. Thank you!
    – Wayne Conrad
    Mar 7 at 18:33
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();
Improve this answer
1
  • query and pid were named current_query and procpid, and state is not available in pg_stat_activity prior to PostgreSQL 9.2.
    – Uyghur Lives Matter
    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

Improve this answer
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

Improve this answer
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.