Hosting Operations
Read-onlyShow PostgreSQL Database Sizes
Disk usage is rising and you need a quick database-level size breakdown.
Command
psql -X -c "select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database order by pg_database_size(datname) desc;"Before you run this
System impact: Read-only. Low when scoped to the shown target.
When not to use it: Do not use database size alone to identify the exact table or index responsible.
Expected output
A sorted list of database names and human-readable sizes.
System impact
Read-only. Nothing changes. PostgreSQL prints database names and sizes.
Recovery / rollback: no state is changed.
When to use it
Use when storage alerts fire or backups suddenly take longer.
When not to use it
Do not use database size alone to identify the exact table or index responsible.
Watch this command run
Command transcript
This sanitized transcript shows the commands and output shape without exposing host details.
$ df -h /var/lib/postgresql
Filesystem Size Used Avail Use% Mounted on
/dev/vda1 25G 19G 5G 80% /work$ psql -X -c "select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database order by pg_database_size(datname) desc;"
datname | size
----------+--------
app_prod | 384 MB
app_test | 42 MB
postgres | 11 MB
(3 rows)$ psql -X -c "select datname from pg_database order by datname;"
datname
----------
app_prod
app_test
postgres
(3 rows)View commands shown
These are the commands shown in the sanitized transcript.
Commands shown
df -h /var/lib/postgresqlpsql -X -c "select datname, pg_size_pretty(pg_database_size(datname)) as size from pg_database order by pg_database_size(datname) desc;"psql -X -c "select datname from pg_database order by datname;"
next steps
Related commands
Show MySQL Database Sizes
The storage alert needed a database name.
mysql -e "select table_schema, round(sum(data_length + index_length)/1024/1024, 1) as mb from information_schema.tables group by table_schema order by mb desc;"
Show Active PostgreSQL Connections
The database was not down. It was full.
psql -X -A -F '|' -c "select pid,usename,datname,state,client_addr from pg_stat_activity order by state, 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;"
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;"
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;"
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.