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.
In this article, we have learnt how to list active connections in PostgreSQL using different commands.