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.