If you are a Postgres database administrator or developer, you must be able to monitor and manage active connections on your database server. Knowing which connections are active can assist you in identifying potential problems, optimizing performance, and ensuring the smooth operation of your database.

This Postgres tutorial will guide you on how to find the list of active connections in PostgreSQL through practical demonstration. So, let’s start!

How to List Sessions/Active Connections in PostgreSQL?

In Postgres, finding the list of sessions/connections currently active on your PostgreSQL database server can be accomplished via the “pg_stat_activity” and pgAdmin’s “Server Activity panel”.

1) Using pg_stat_activity View

The view “pg_stat_activity” provides information about currently executing queries and other details, such as the user and client host for each session. The details about the pg_stat_activity view are listed below:

pid: the process ID of the backend process managing the session.
usename: user’s name connected to the current session.
datname: the database’s name the backend process is linked to.
state: the session's current state (e.g., idle, active).
backend_start: the timestamp of when this session was started.
xact_start: the timestamp at which the current transaction was started(if any).
query_start: the timestamp of when the current query was started (if any).
client_addr: the client address for the session.
client_hostname: the client hostname (if available).
client_port: the client port for the connection.
query: the current query being executed (if any).
waiting: a boolean indicating whether the session is waiting on a lock (true) or not (false).

Let's look at how the pg_stat_activity view works via practical examples.

Example: How to List Sessions/Active Connections in Postgres Via pg_stat_activity View?

Executing the below-provided command will retrieve the list of the currently active connections/sessions in your database:

SELECT * FROM pg_stat_activity;

Move the slider to the right to see more details regarding currently active connections. The pg_stat_activity view retrieves all the columns, indicating the connection’s details. However, using the WHERE clause, you can also filter the results of pg_stat_activity. For instance, the below-provided query will return the name of the process, user, and current state:

SELECT pid, usename, state
FROM pg_stat_activity;

This is how the “pg_stat_activity” view works in Postgres.

2) Using Server Activity Panel

Another GUI-based alternate method for finding the active connections is using pgAdmin’s “Server Activity panel”. For that, select the database from the browser pane and then click on the “Dashboard” tab:

Now, in the server activity, you can see all the details regarding the active connections:

This is how you can check the active connections in Postgres.

Conclusion

Postgres allows you to find the list of active sessions/connections on your PostgreSQL database server via the "pg_stat_activity" and pgAdmin's "Server Activity panel”. Both these approaches provide information about currently executing queries and other details, such as the user and client host for each session. This blog explained how to check the active connections in Postgres using different methods.