Thomas' Tech Tips

See current connections in PostgreSQL

3 March 2022 - Thomas Damgaard

Today’s tip is how to see current connections in PostgreSQL.

The query is quite simple:

SELECT * FROM pg_stat_activity;

Selecting * yields too much output to show here.

Typically, these are the ones I want: datname, usename, client_addr, state, backend_type and maybe query.

Example:

postgres=# SELECT datname, usename, client_addr, state, backend_type FROM pg_stat_activity;
    datname    | usename  | client_addr |        state        |         backend_type
---------------+----------+-------------+---------------------+------------------------------
               |          |             |                     | autovacuum launcher
               | postgres |             |                     | logical replication launcher
 tracker       | tracker  | 127.0.0.1   | idle in transaction | client backend
 tracker       | tracker  | 127.0.0.1   | idle                | client backend
 postgres      | postgres |             | idle                | client backend
 postgres      | postgres |             | active              | client backend
 appdev        | postgres |             | idle                | client backend
               |          |             |                     | background writer
               |          |             |                     | checkpointer
               |          |             |                     | walwriter
(10 rows)

postgres=#

Notice the one row that says idle in transaction in state? This means that this connection has started a transaction (ie. it has issued BEGIN) and is now idle in that transaction. This could be a user that has connected interactively. If so, it is likely not a problem. However, if this is an application connection, this is something to avoid.

Filed under: postgresql, tips

Back to article list