Psql

Working with the local dev cluster via psql

Default connection: psql -h /tmp -d postgres — Unix socket at /tmp, trust auth (no password), database postgres, superuser = your macOS username. Server log is dev/data-debug/server.log (tail it with /pg-tail-log).

When to reach for what

• psql — anything interactive, anything writing, anything that needs meta commands (\d, \timing, \watch, \errverbose), anything debug-flavored (changing client_min_messages, capturing a backend PID, running EXPLAIN ANALYZE in a loop). Default tool.
• postgres-dev MCP — read-only one-shots from a planning/exploration loop (sampling rows, schema introspection inside agent reasoning). Strictly SELECT, no meta-commands, no session knobs. Treat as a convenience for agents, not a substitute for psql.

Daily-loop one-liners (assumes /pg-start already ran)

export PATH="$PWD/dev/install-debug/bin:$PATH"
export PGDATA="$PWD/dev/data-debug"

psql -h /tmp -d postgres                                     # interactive
psql -h /tmp -d postgres -c 'SELECT version();'              # one-shot
psql -h /tmp -d postgres -f /tmp/repro.sql                   # script
psql -h /tmp -d postgres -X -P pager=off -At -c '<sql>'      # script-friendly

-X skips ~/.psqlrc, -A unaligned, -t tuples-only, -P pager=off keeps pipes clean.

High-yield meta-commands for backend work

Command	What it does
\conninfo	Confirm socket, port, db, user, PID.
\d <name> / \d+ <name>	Schema for table / index / view. + adds storage params and tablespace.
\df+ <fn> / \sf <fn>	Function signature / full source — works for SQL & PL/pgSQL bodies.
\sv <view>	Source of a view.
\dn+, \dt+, \di+, \dm+	Schemas / tables / indexes / matviews with sizes.
\d+ pg_catalog.<rel>	Walk a catalog when debugging planner / cache code.
\dconfig <pattern>	All GUCs matching pattern, current values + source.
\timing on	Per-query wall time.
\watch <sec>	Re-run last query every N seconds — great for pg_stat_activity / pg_locks while reproducing.
\errverbose	After an error: print code, detail, hint, file:line of the ereport(). The file:line is the elog location in the backend source — pair with the corpus.
\gexec	Run a query whose result is itself SQL; e.g., generate DROP TABLE … from pg_tables.
\set ON_ERROR_STOP on	Make scripted runs abort on first error (default off is footgunny).
\copy table FROM '/path' WITH (FORMAT csv)	Client-side copy — bypasses server-side permissions.

Session knobs that surface backend behavior

-- Show every DEBUG2-and-louder log line in psql (no need to tail the log).
SET client_min_messages = DEBUG2;

-- Have the server log them too (so they land in dev/data-debug/server.log).
SET log_min_messages = DEBUG2;

-- Log every statement + duration + parse/plan/exec break-down.
SET log_statement = 'all';
SET log_duration = on;
SET log_min_duration_statement = 0;

-- Make EXPLAIN ANALYZE useful for buffer / WAL / IO debugging.
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS, VERBOSE) <query>;
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>;  -- machine-parseable

-- Force a specific plan shape to confirm a planner hypothesis.
-- enable_* don't HARD-disable a node type — they apply a large cost
-- penalty (disable_cost), so the planner picks the next-best plan.
-- If every alternative is also disabled, the "disabled" node still wins.
SET enable_seqscan = off;        -- and friends: enable_hashjoin / _nestloop / _indexonlyscan
SET work_mem = '4MB';            -- sort/hash spill threshold
SET jit = off;                   -- rule JIT in/out when timing things

All SET is session-scoped — exiting psql resets. Use SET LOCAL inside a transaction to scope to that txn.

Runtime introspection — what is the backend doing?

-- All sessions + their state, query, wait event, backend PID.
SELECT pid, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE backend_type = 'client backend';

-- Memory contexts of THIS backend — top consumers first.
-- Columns (PG 17+): name, ident, type, level, path int4[], total_bytes,
-- total_nblocks, free_bytes, free_chunks, used_bytes.
-- `path` is the array of ancestor context_ids from TopMemoryContext down.
SELECT name, type, level, path, total_bytes/1024 AS kb, used_bytes/1024 AS used_kb
FROM pg_backend_memory_contexts
ORDER BY total_bytes DESC LIMIT 20;

-- Memory contexts of ANOTHER backend (PG 14+).
SELECT pg_log_backend_memory_contexts(<pid>);
-- → lands in dev/data-debug/server.log; tail it.

-- Live locks + blockers.
SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks ORDER BY relation, pid;

-- Buffer cache hit ratio per relation (run after a workload).
SELECT relname,
       heap_blks_read AS read, heap_blks_hit AS hit,
       round(heap_blks_hit::numeric / nullif(heap_blks_hit + heap_blks_read,0), 3) AS ratio
FROM pg_statio_user_tables ORDER BY hit + read DESC LIMIT 10;

Capturing a backend PID for gdb/lldb

Every psql connection causes the postmaster to fork() a fresh backend; the PID you get from pg_backend_pid() is THAT backend's pid (NOT psql's client-side pid). See knowledge/architecture/process-model.md.

Quick PID grab from inside the session:

SELECT pg_backend_pid();

Then in another shell: /pg-attach <pid> (the slash command wraps lldb with breakpoints on errstart and MemoryContextStats pre-set).

Race-safe held-PID handoff — when you need to attach BEFORE a query runs (so lldb sees its execution), use the PGAPPNAME=hold + pg_sleep pattern. PGAPPNAME is a libpq env var (NOT a psql \set variable — that won't propagate):

# 1. Tag a holding backend with application_name='hold' and pin it open.
PGAPPNAME=hold psql -h /tmp -d postgres -X -c 'SELECT pg_sleep(600);' &

# 2. From a second psql, find the PID.
PID=$(psql -h /tmp -d postgres -At -c \
  "SELECT pid FROM pg_stat_activity WHERE application_name='hold'")

# 3. Attach.
/pg-attach "$PID"

# 4. From a THIRD psql session (same application_name='hold' if you
#    want to drive queries through the attached backend), run your
#    actual repro.

If the backend you want to debug doesn't exist yet (e.g., you're studying startup), use single-user mode instead — see .claude/skills/debugging/SKILL.md.

Memory-leak workflow on the debug build

The build defaults (-Ddebug=true -Dcassert=true) wire in the asserts and the clobber-freed-memory machinery. To hunt a suspected leak:

1. Note baseline (MessageContext is the per-message context, reset between client protocol messages — growth across iterations is the leak signature for a per-message leak. Other commonly-watched contexts: CacheMemoryContext, ExecutorState, PortalContext):

   SELECT name, total_bytes FROM pg_backend_memory_contexts WHERE name='MessageContext';
   

2. Run the suspect workload N times (\watch is your friend).
3. Re-check the context — growth across iterations is the leak signature.
4. To pin the leak to a callsite, attach lldb (/pg-attach <pid>) and set a breakpoint on MemoryContextAlloc filtered to the suspect context.
5. macOS-specific: MallocStackLogging=1 env on the postmaster before pg_ctl start makes leaks <pid> produce real backtraces.

Safe vs not-safe on the dev cluster

The dev cluster is disposable — dev/data-debug/ can be wiped at any time via /pg-fresh. So:

• ✅ DROP DATABASE, DROP SCHEMA CASCADE, TRUNCATE, anything destructive on the dev cluster.
• ✅ ALTER SYSTEM SET … for testing GUCs (writes postgresql.auto.conf; reset with ALTER SYSTEM RESET <guc> then SELECT pg_reload_conf()).
• ✅ CREATE EXTENSION whatever's compiled into dev/install-debug/share/extension/.
• ⚠️ Avoid editing pg_catalog.* directly — even on the dev cluster it often crashes the backend in interesting-but-not-useful ways. If you need to perturb catalogs, write a regression test instead.
• ⚠️ DELETE FROM pg_class … is exactly the wrong way to do anything.

Connection-string variants for psql / libpq tools

Built from the trust + socket defaults:

postgresql:///postgres?host=/tmp                              # the canonical one
postgresql://$USER@/postgres?host=/tmp                        # explicit user
postgresql:///postgres?host=/tmp&application_name=repro       # tag the session for pg_stat_activity
postgresql:///postgres?host=/tmp&options=-c%20client_min_messages%3DDEBUG2   # set GUC in URL

The MCP at .mcp.json uses the canonical form. If you need a different database, edit .mcp.json rather than passing flags ad-hoc.

Common gotchas

• psql -h db.acme.com … (or any hostname / managed-PG vendor) is the WRONG tool here. This skill is for the LOCAL dev cluster built from source — Unix socket /tmp, trust auth, db postgres. If the prompt names a hostname, a managed-PG vendor (RDS / Cloud SQL / Supabase / Neon / Aurora), or talks about touching prod data, stop. Use the production-PG tooling for that team, NOT this skill.
• psql: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory — the server isn't running, or unix_socket_directories isn't /tmp. Check dev/data-debug/postgresql.conf; /pg-start sets this.
• role "postgres" does not exist — initdb makes the superuser match $USER, not literally postgres. Connect as your shell user; the database called postgres does exist.
• Hung session blocks something — \watch pg_stat_activity to find the blocker PID, then SELECT pg_terminate_backend(<pid>).
• SET client_min_messages = DEBUG2 floods psql — fine for one query, painful for an interactive session. Use SET LOCAL inside BEGIN/COMMIT for scoped noise.

Cross-references

• .claude/skills/build-and-run/SKILL.md — cluster lifecycle (/pg-start, /pg-stop, /pg-restart, /pg-fresh) and PATH/PGDATA wiring this skill assumes.
• .claude/skills/debugging/SKILL.md — held-PID handoff to lldb (PGAPPNAME=hold + pg_sleep); single-user mode when the backend you want doesn't exist yet.
• .claude/skills/error-handling/SKILL.md — what \errverbose is showing (the ereport() machinery).
• .claude/skills/memory-contexts/SKILL.md — interpreting pg_backend_memory_contexts / pg_log_backend_memory_contexts output.
• knowledge/architecture/process-model.md — how a psql connection becomes a postmaster fork() + per-connection backend.
• knowledge/data-structures/snapshot-lifecycle.md — what \d+ <table> is computing under the hood when it touches catalog snapshots.
• knowledge/subsystems/storage-buffer.md — what BUFFERS in EXPLAIN (ANALYZE, BUFFERS) is counting.
• .mcp.json — the read-only postgres-dev MCP endpoint for one-shot agent queries (alternative to psql for non-interactive lookups).