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 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.
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: