Linux CLI for DBAs Basics

Q: Install extra tools on prod database host?

Prefer bastion with sysstat (iostat), htop. Minimal prod image: use vmstat, ps, df always present.

Q: kubectl exec vs SSH?

Kubernetes: kubectl exec -it pod -- bash then same commands. PGDATA path from env or psql -c 'SHOW data_directory;'.

10 examples for disk and memory signals during query storms on PostgreSQL 18.4 Linux hosts - bash triage before you change GUCs or fail over.

Prerequisites

SSH to bastion or kubectl exec into database pod (read-only role for juniors).
Know PGDATA path: /var/lib/postgresql/18/main or provider-specific mount.
psql in PATH with read-only prod credentials policy.

export PGDATA=/var/lib/postgresql/18/main
export DATABASE_URL="postgresql://readonly@localhost:5432/myapp"

Basic Examples

1. Disk Space on PGDATA

df -h "$PGDATA"
df -i "$PGDATA"   # inode exhaustion looks like disk full
du -sh "$PGDATA/pg_wal" "$PGDATA/log" 2>/dev/null || true

WAL growth without archive success fills disk before data files
100% disk → Postgres may stop accepting writes with PANIC or FATAL

2. Memory Pressure Signals

free -h
vmstat 1 5
dmesg -T | tail -30 | grep -i -E 'oom|kill|postgres'

SELECT name, setting, unit FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','maintenance_work_mem','effective_cache_size');

OOM killer targets postgres worker with high RSS during sort/hash storms
work_mem times concurrent sorts can exceed RAM without connection pool limits

3. Who Is Running What

SELECT pid, usename, application_name, client_addr, state, wait_event_type, wait_event,
       now() - query_start AS duration, left(query, 100) AS query
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid()
ORDER BY duration DESC NULLS LAST
LIMIT 20;

wait_event_type = IO during checkpoint or seq scan storm
idle in transaction holds locks and bloats vacuum

4. Lock Waits Quick Count

SELECT count(*) FILTER (WHERE NOT granted) AS waiting,
       count(*) FILTER (WHERE granted) AS granted
FROM pg_locks;

# One-liner from shell via psql
psql "$DATABASE_URL" -t -c "SELECT count(*) FROM pg_locks WHERE NOT granted;"

5. Postgres Service Health (systemd)

sudo systemctl status postgresql@18-main
sudo journalctl -u postgresql@18-main -p err --since "30 min ago" --no-pager

Restart loop often correlates with OOM or corrupt config after edit
Journal shows FATAL: could not write lock file on full disk

6. Find Postgres Processes

ps aux | grep '[p]ostgres'
pgrep -a postgres

# Top postgres workers by RSS
ps -eo pid,rss,cmd --sort=-rss | grep postgres | head -10

Many postgres: worker lines normal; one giant RSS worker may be sort/hash node

7. Network Connections to Port 5432

ss -tnp | grep ':5432'
ss -s   # summary: established, timewait

Connection storm from app without pooler shows thousands of ESTAB
Compare with max_connections in SQL

Intermediate Examples

8. Incident Bundle Script

#!/usr/bin/env bash
set -euo pipefail
OUT="incident-$(date +%Y%m%d-%H%M%S)"
mkdir -p "$OUT"
df -h > "$OUT/df.txt"
free -h > "$OUT/free.txt"
vmstat 1 5 > "$OUT/vmstat.txt"
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "SELECT version();" > "$OUT/version.txt"
psql "$DATABASE_URL" -c "SELECT * FROM pg_stat_activity;" > "$OUT/activity.csv" --csv
psql "$DATABASE_URL" -c "SELECT * FROM pg_locks WHERE NOT granted;" > "$OUT/locks.csv" --csv
tar czf "$OUT.tgz" "$OUT"
echo "bundle: $OUT.tgz"

Attach bundle to incident ticket within first 15 minutes
Scribe runs script; incident commander interprets

9. Correlate Host IO with Query Storm

iostat -xz 1 5

SELECT queryid, calls, mean_exec_time, shared_blks_read
FROM pg_stat_statements
ORDER BY shared_blks_read DESC NULLS LAST
LIMIT 10;

Rising %util on data volume plus seq scans in top statements → index or cache issue
See IO & Memory Tools

10. Safe Postgres Stop vs Kill

# Graceful (systemd)
sudo systemctl stop postgresql@18-main
 
# Emergency only after stop failed
sudo kill -TERM "$(head -1 "$PGDATA/postmaster.pid")"
sleep 10
# kill -9 last resort - risks incomplete checkpoint

SIGKILL on postmaster risks recovery on next start; document in post-mortem

FAQs

Install extra tools on prod database host?

Prefer bastion with sysstat (iostat), htop. Minimal prod image: use vmstat, ps, df always present.

kubectl exec vs SSH?

Kubernetes: kubectl exec -it pod -- bash then same commands. PGDATA path from env or psql -c 'SHOW data_directory;'.

When to escalate to failover?

Hardware dead, corruption PANIC, or unrecoverable disk - not for every lock storm. See Incident Triage Skill.

IO & Memory Tools - iostat, vmstat, pidstat
CLI Best Practices - 25-item summary
Incident Triage Skill - decision tree
Disk Full on PGDATA - WAL runbook
Lock Storm Triage - blocker queries

Stack versions: This page was written for PostgreSQL 18.4 on Linux (systemd, Debian/RHEL layouts).

Prerequisites

SSH to bastion or kubectl exec into database pod (read-only role for juniors).
Know PGDATA path: /var/lib/postgresql/18/main or provider-specific mount.
psql in PATH with read-only prod credentials policy.

export PGDATA=/var/lib/postgresql/18/main
export DATABASE_URL="postgresql://readonly@localhost:5432/myapp"

Basic Examples

1. Disk Space on PGDATA

df -h "$PGDATA"
df -i "$PGDATA"   # inode exhaustion looks like disk full
du -sh "$PGDATA/pg_wal" "$PGDATA/log" 2>/dev/null || true

WAL growth without archive success fills disk before data files
100% disk → Postgres may stop accepting writes with PANIC or FATAL

2. Memory Pressure Signals

free -h
vmstat 1 5
dmesg -T | tail -30 | grep -i -E 'oom|kill|postgres'

SELECT name, setting, unit FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','maintenance_work_mem','effective_cache_size');

OOM killer targets postgres worker with high RSS during sort/hash storms
work_mem times concurrent sorts can exceed RAM without connection pool limits

3. Who Is Running What

SELECT pid, usename, application_name, client_addr, state, wait_event_type, wait_event,
       now() - query_start AS duration, left(query, 100) AS query
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid()
ORDER BY duration DESC NULLS LAST
LIMIT 20;

wait_event_type = IO during checkpoint or seq scan storm
idle in transaction holds locks and bloats vacuum

4. Lock Waits Quick Count

SELECT count(*) FILTER (WHERE NOT granted) AS waiting,
       count(*) FILTER (WHERE granted) AS granted
FROM pg_locks;

# One-liner from shell via psql
psql "$DATABASE_URL" -t -c "SELECT count(*) FROM pg_locks WHERE NOT granted;"

5. Postgres Service Health (systemd)

sudo systemctl status postgresql@18-main
sudo journalctl -u postgresql@18-main -p err --since "30 min ago" --no-pager

Restart loop often correlates with OOM or corrupt config after edit
Journal shows FATAL: could not write lock file on full disk

6. Find Postgres Processes

ps aux | grep '[p]ostgres'
pgrep -a postgres

# Top postgres workers by RSS
ps -eo pid,rss,cmd --sort=-rss | grep postgres | head -10

Many postgres: worker lines normal; one giant RSS worker may be sort/hash node

7. Network Connections to Port 5432

ss -tnp | grep ':5432'
ss -s   # summary: established, timewait

Connection storm from app without pooler shows thousands of ESTAB
Compare with max_connections in SQL

Intermediate Examples

8. Incident Bundle Script

#!/usr/bin/env bash
set -euo pipefail
OUT="incident-$(date +%Y%m%d-%H%M%S)"
mkdir -p "$OUT"
df -h > "$OUT/df.txt"
free -h > "$OUT/free.txt"
vmstat 1 5 > "$OUT/vmstat.txt"
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "SELECT version();" > "$OUT/version.txt"
psql "$DATABASE_URL" -c "SELECT * FROM pg_stat_activity;" > "$OUT/activity.csv" --csv
psql "$DATABASE_URL" -c "SELECT * FROM pg_locks WHERE NOT granted;" > "$OUT/locks.csv" --csv
tar czf "$OUT.tgz" "$OUT"
echo "bundle: $OUT.tgz"

Attach bundle to incident ticket within first 15 minutes
Scribe runs script; incident commander interprets

9. Correlate Host IO with Query Storm

iostat -xz 1 5

SELECT queryid, calls, mean_exec_time, shared_blks_read
FROM pg_stat_statements
ORDER BY shared_blks_read DESC NULLS LAST
LIMIT 10;

Rising %util on data volume plus seq scans in top statements → index or cache issue
See IO & Memory Tools

10. Safe Postgres Stop vs Kill

# Graceful (systemd)
sudo systemctl stop postgresql@18-main
 
# Emergency only after stop failed
sudo kill -TERM "$(head -1 "$PGDATA/postmaster.pid")"
sleep 10
# kill -9 last resort - risks incomplete checkpoint

SIGKILL on postmaster risks recovery on next start; document in post-mortem

FAQs

Install extra tools on prod database host?

Prefer bastion with sysstat (iostat), htop. Minimal prod image: use vmstat, ps, df always present.

kubectl exec vs SSH?

Kubernetes: kubectl exec -it pod -- bash then same commands. PGDATA path from env or psql -c 'SHOW data_directory;'.

When to escalate to failover?

Hardware dead, corruption PANIC, or unrecoverable disk - not for every lock storm. See Incident Triage Skill.

IO & Memory Tools - iostat, vmstat, pidstat
CLI Best Practices - 25-item summary
Incident Triage Skill - decision tree
Disk Full on PGDATA - WAL runbook
Lock Storm Triage - blocker queries

Stack versions: This page was written for PostgreSQL 18.4 on Linux (systemd, Debian/RHEL layouts).

Linux CLI for DBAs Basics

Search Documentation

Linux CLI for DBAs Basics