Hosting Operations
Read-onlyShow 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;"Before you run this
System impact: Read-only. Low when scoped to the shown target.
When not to use it: Do not use this to terminate sessions; it only lists them.
Expected output
Rows showing PID, user, database, state, and client address.
System impact
Read-only. Nothing changes. psql prints active backend sessions.
Recovery / rollback: no state is changed.
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.
Watch this command run
Command transcript
This sanitized transcript shows the commands and output shape without exposing host details.
$ psql -X -c "select count(*) from pg_stat_activity;"
count
-------
5
(1 row)$ 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|192.0.2.10
512|app|app_prod|idle|192.0.2.10
518|migrator|app_prod|active|192.0.2.10$ psql -X -c "select state, count(*) from pg_stat_activity group by state order by state;"
state | count
--------+-------
active | 2
idle | 2
(2 rows)View commands shown
These are the commands shown in the sanitized transcript.
Commands shown
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;"
next steps
Related commands
Check PostgreSQL Lock Waits
The outage was a queue, not a crash.
psql -X -c "select pid, wait_event_type, wait_event, state, left(query, 80) as query from pg_stat_activity where wait_event_type is not null order by pid;"
Find Long-Running PostgreSQL Queries
One query can make the whole app look broken.
psql -X -c "select pid, now() - query_start as age, state, left(query, 80) as query from pg_stat_activity where query_start is not null order by age desc limit 10;"
Show PostgreSQL Database Sizes
Disk pressure starts with knowing what grew.
psql -X -c "select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database order by pg_database_size(datname) desc;"
Find Long-Running MySQL Queries
One old query explained the whole slowdown.
mysql -e "select id,user,host,db,command,time,state,left(info,80) as info from information_schema.processlist where command <> 'Sleep' order by time desc limit 10;"
Show Active MySQL Sessions
The app was waiting behind busy sessions.
mysql -e "show full processlist;"
Study mapping
Use this as independent command practice: read the notes, predict the output, then compare it with the example before using a real shell.
Useful for
- LPIC-1 style command-line practice
- LFCS style performance tasks
- Linux+ style troubleshooting review
Independent study support only. No affiliation, endorsement, exam dumps, or real exam questions.