PostgreSQL field reference

psql

psql 'postgresql://user@host:5432/database?sslmode=verify-full'
psql -X --set ON_ERROR_STOP=on --file migration.sql "$DATABASE_URL"

Command	Purpose
`\conninfo`	Current connection
`\l`	Databases
`\dn`	Schemas
`\dt app.*`	Tables
`\d+ app.orders`	Object definition and storage detail
`\du`	Roles
`\dx`	Extensions
`\timing on`	Client-observed duration
`\x auto`	Expanded output for wide results
`\gdesc`	Describe result columns without displaying rows
`\q`	Quit

Scripts use -X to ignore a user's .psqlrc and ON_ERROR_STOP to exit at the first error.

Current context

SELECT
  version(),
  current_database(),
  current_user,
  session_user,
  current_schema(),
  current_setting('TimeZone') AS timezone,
  inet_server_addr(),
  inet_server_port();

Object size

SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total,
  pg_size_pretty(pg_relation_size(relid)) AS heap,
  pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Active sessions and old transactions

SELECT
  pid, usename, application_name, state,
  now() - xact_start AS xact_age,
  wait_event_type, wait_event,
  left(query, 160) AS query
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST;

Blocking graph

SELECT
  blocked.pid AS blocked_pid,
  blocker.pid AS blocker_pid,
  now() - blocked.query_start AS blocked_for,
  left(blocked.query, 120) AS blocked_query,
  left(blocker.query, 120) AS blocker_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity AS blocker ON blocker.pid = b.pid;

Do not immediately call pg_terminate_backend on a blocker. Identify the workload, transaction, retry behavior, and termination impact first.

Safe session settings

BEGIN;
SET LOCAL statement_timeout = '10s';
SET LOCAL lock_timeout = '2s';
SET LOCAL search_path = app, pg_catalog;
-- work
COMMIT;

Diagnostic order

Confirm target and role → record SQLSTATE → inspect transaction state → inspect waits/blockers → capture plan and statistics → reproduce safely → run a verification query after the fix.

Before a session exists, use PostgreSQL connection troubleshooting. For SQL failures after connection, use the error and SQLSTATE fieldbook.

For upgrade boundaries around extensions, maintenance tools, and open-source components, use the PostgreSQL extensions and ecosystem guide.

See PostgreSQL index and storage access methods for storage concepts, and PostgreSQL lineage and compatible databases for forks and protocol compatibility.