Hosting Operations

Show Active PostgreSQL Connections

PostgreSQL is slow or rejecting clients and you need to see current sessions quickly.

Command

psql -X -A -F '|' -c "select pid,usename,datname,state,client_addr from pg_stat_activity order by state, pid;"

What changed

Nothing changes. psql prints active backend sessions.

Danger

safe

When to use it

Use when apps report connection exhaustion, slow requests, or stuck database access.

When not to use it

Do not use this to terminate sessions; it only lists them.

Undo or recovery

No undo needed because this command is read-only.

Expected output

Rows showing PID, user, database, state, and client address.

demo script

Disposable terminal steps

psql -X -c "select count(*) from pg_stat_activity;"
psql -X -A -F '|' -c "select pid,usename,datname,state,client_addr from pg_stat_activity order by state, pid;"
psql -X -c "select state, count(*) from pg_stat_activity group by state order by state;"

Terminal transcript Demo JSON

simulated output

What it looks like

disposable vessel

::fixture-ready::
$ psql -X -c "select count(*) from pg_stat_activity;"
 count
-------
     5
(1 row)
::exit-code::0
$ psql -X -A -F '|' -c "select pid,usename,datname,state,client_addr from pg_stat_activity order by state, pid;"
pid|usename|datname|state|client_addr
511|app|app_prod|active|10.0.0.12
512|app|app_prod|idle|10.0.0.13
518|migrator|app_prod|active|10.0.0.20
::exit-code::0
$ psql -X -c "select state, count(*) from pg_stat_activity group by state order by state;"
 state  | count
--------+-------
 active |     2
 idle   |     2
(2 rows)
::exit-code::0

YouTube Short

List Postgres sessions fast.

When PostgreSQL feels unavailable, check whether it is out of connection room before restarting anything.

experiments

A/B tests to run

Metric: linkedin_save_rate

A: The database was not down. It was full.

B: Check sessions before restarting Postgres.