Psql

psql

Use psql only when a repository recipe does not already cover the diagnostic. Prefer repo-local commands such as just db-shell, make db-test, migration recipes, seed scripts, or integration tests when they exist.

Safety Defaults

• Prefer a test, development, or disposable database. Never run write or destructive diagnostics against production.
• Load connection settings from the repo's environment mechanism; do not hardcode passwords, hosts, ports, or database names.
• Use read-only queries unless the user or test recipe explicitly requires writes.
• Use -X to ignore a user's ~/.psqlrc in scripted agent commands so output and settings stay predictable.
• Use -v ON_ERROR_STOP=1 for scripts so SQL errors produce psql exit status 3 instead of continuing.
• Redact secrets, tokens, and personally identifiable data from output.
• Set a short statement_timeout for exploratory diagnostics unless the repo's workflow says otherwise.

Invocation Pattern

Adapt variable names to the repo's .env, secret manager, or wrapper script:

PGPASSWORD="$DB_PASSWORD" psql \
  -X \
  -h "$DB_HOST" \
  -p "$DB_PORT" \
  -U "$DB_USER" \
  -d "$DB_NAME" \
  -v ON_ERROR_STOP=1 \
  -c "select version();"

If the repo provides DATABASE_URL, prefer the connection URI and keep the same safety flags:

psql -X "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "select current_database(), current_user;"

Use -A -t for compact machine-readable scalar output:

psql -X "$DATABASE_URL" -v ON_ERROR_STOP=1 -A -t \
  -c "select count(*) from information_schema.tables where table_schema = 'public';"

Use --csv for query output intended for spreadsheet or script consumption.

Command Shape Rules

• -c executes one SQL string or one backslash command, then exits. Do not mix SQL and psql meta-commands in the same -c; use repeated -c options or feed standard input.
• -f file.sql is preferred over shell redirection for script files because psql reports line numbers.
• -1 wraps repeated -c or -f commands in a single transaction. Pair it with -v ON_ERROR_STOP=1 when a multi-step script must be all-or-nothing.
• -w prevents password prompts and is useful for noninteractive jobs only when credentials are already supplied by .pgpass, env vars, or a service file.
• -v name=value assigns psql variables. Use :'name' for SQL string literals and :"name" for SQL identifiers in scripts; do not concatenate untrusted values into SQL text.

Quick Timing

Use psql timing for rough wall-clock feedback:

psql -X "$DATABASE_URL" -v ON_ERROR_STOP=1  0
      order by mean_exec_time desc
      limit 10;"

Reset statistics only in a disposable or explicitly approved environment:

select pg_stat_statements_reset(0, 0, 0);

auto_explain

Use auto_explain when you need plans for slow application-issued SQL or nested SQL inside functions/procedures without manually wrapping each statement. It requires server/session permissions, often superuser, and has overhead.

Safe session-level shape for an isolated test database:

load 'auto_explain';
set auto_explain.log_min_duration = '250ms';
set auto_explain.log_analyze = true;
set auto_explain.log_buffers = true;
set auto_explain.log_wal = true;
set auto_explain.log_timing = off;
set auto_explain.log_nested_statements = on;
set auto_explain.log_format = 'json';

Use log_timing = off when per-node timing overhead would distort the workload and row counts/buffer usage are enough. Turn log_nested_statements on only when function/procedure internals are the target.

Live Activity And Locks

Current running queries:

psql -X "$DATABASE_URL" -v ON_ERROR_STOP=1 -x \
  -c "select pid, usename, application_name, state, wait_event_type, wait_event,
             now() - query_start as query_age, left(query, 500) as query
      from pg_stat_activity
      where state <> 'idle'
      order by query_start nulls last;"

Blocking relationships:

psql -X "$DATABASE_URL" -v ON_ERROR_STOP=1 \
  -c "select blocked.pid as blocked_pid,
             blocking.pid as blocking_pid,
             blocked.query as blocked_query,
             blocking.query as blocking_query
      from pg_stat_activity blocked
      join pg_locks blocked_locks on blocked_locks.pid = blocked.pid and not blocked_locks.granted
      join pg_locks blocking_locks
        on blocking_locks.locktype = blocked_locks.locktype
       and blocking_locks.database is not distinct from blocked_locks.database
       and blocking_locks.relation is not distinct from blocked_locks.relation
       and blocking_locks.page is not distinct from blocked_locks.page
       and blocking_locks.tuple is not distinct from blocked_locks.tuple
       and blocking_locks.virtualxid is not distinct from blocked_locks.virtualxid
       and blocking_locks.transactionid is not distinct from blocked_locks.transactionid
       and blocking_locks.classid is not distinct from blocked_locks.classid
       and blocking_locks.objid is not distinct from blocked_locks.objid
       and blocking_locks.objsubid is not distinct from blocked_locks.objsubid
       and blocking_locks.pid <> blocked_locks.pid
      join pg_stat_activity blocking on blocking.pid = blocking_locks.pid
      where blocking_locks.granted;"

Schema Inspection

Use psql meta-commands interactively, or invoke one meta-command per -c:

psql -X "$DATABASE_URL" -c '\dt public.*'
psql -X "$DATABASE_URL" -c '\d+ public.example_table'
psql -X "$DATABASE_URL" -c '\df+ public.*'

For scriptable inspection, query catalogs or information_schema:

psql -X "$DATABASE_URL" -v ON_ERROR_STOP=1 -A -F $'\t' \
  -c "select column_name, data_type, is_nullable
      from information_schema.columns
      where table_schema = 'public' and table_name = 'example_table'
      order by ordinal_position;"

Output

Report the command shape, target environment class, result summary, profiling method, and any uncertainty about whether the diagnostic used the intended database. For profiling, include whether the evidence came from \timing, EXPLAIN, pg_stat_statements, auto_explain, or PostgreSQL statistics views.

Source Notes

This skill follows the official PostgreSQL documentation for psql, EXPLAIN, pg_stat_statements, auto_explain, and monitoring statistics views.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: zazzcode
• Source: zazzcode/zazz-skills
• License: MIT

Install and usage instructions live in the source repository linked above.