How to List Active Connections in PostgreSQL

PostgreSQL is a popular database management system used by many websites, organizations and developers. It allows you to build high-traffic websites and can handle a large number of connections. But if these connections remain open for a long time, then they end up blocking new incoming connections, and this can slow down your database. So if your database has slowed down, it might be a good idea to check the number of active connections to it. In this article, we will learn how to list active connections in PostgreSQL.

How to List Active Connections in PostgreSQL

There are several ways to list active connections in PostgreSQL.

1. Using pg_stat_activity

pg_stat_activity is a table that stores PostgreSQL connection & activity stats. You can get a count of active connections by simply running the following SQL query in PostgreSQL.

postgres#- SELECT * FROM pg_stat_activity;

The above query will show connection stats information about all databases on your system. If you want to see open connections to a specific database, use the following query, which specifies the database name for which you want to see active connections. Replace dbname below with the name of your database.

postgres#- SELECT * FROM pg_stat_activity where datname='dbname';

2. Using pgtop

pgtop is a utility that works lip top command but shows activity of PostgreSQL. You can install it with the following command.

$ sudo apt-get install ptop

Once it is installed, you can run it with the following command.

$ sudo pg_top

3. Using pgadmin

You can also use pgAdmin tool to monitor activity of PostgreSQL. Install it with the following command.

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

pgAdmin provides a web-based interface & dashboard to help you monitor active connections and other PostgreSQL stats in real-time. Here is a sample snapshot of the pgAdmin4 dashboard.