Debugging postgres: List all connections

Sometimes want a list of all connections to your postgres database. Postgres maintains this list in an internal datastructure that you simply query like this:

SELECT * FROM pg_stat_activity;

This will give you something like this (I have removed many columns to make it more readable here):

In this example, there are two client connections: one from PyCharm, one from my test-application showing up as “test.trader.PersistentQueue(data)”. The other rows are postgres internals that I don’t know about.

Notice the columns state, and application_name. The state will be either of seven values (directly quoted from the postgres documentation):

• active: The backend is executing a query.
• idle: The backend is waiting for a new client command.
• idle in transaction: The backend is in a transaction, but is not currently executing a query.
• idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error.
• fastpath function call: The backend is executing a fast-path function.
• disabled: This state is reported if track_activities is disabled in this backend.
• NULL: Ordinary users can only see all the information about their own sessions (sessions belonging to a role that they are a member of). In rows about other sessions, many columns will be null. Note, however, that the existence of a session and its general properties such as its sessions user and database are visible to all users. Superusers and members of the built-in role pg_read_all_stats (see also Section 21.5 can see all the information about all sessions.

Set your application_name

The application_name is a name that you can set yourself when you create the connection. The example shows test.datafeed.PersistentQueue\(data) as the application name. I used psycopg2’s ThreadedConnectionPool to set that name as follows:

1
2
3
4
5
6
7
8

from psycopg2.pool import ThreadedConnectionPool

connection_pool = ThreadedConnectionPool(
  MIN_CONNECTIONS,
  MAX_CONNECTIONS,
  DATABASE_URL,
  application_name="test.datafeed.PersistentQueue(data)",
)

See also

• Django 4.0 released
• Updated: How to extract a date from a PDF file using Python
• A new remote workflow for PyCharm (and other Jetbrains IDEs)
• How to extract a date from a PDF file using Python
• rxPY3: Always implement on_error and on_completed