PostgreSQL CLI Essentials

psql commands for incident response.

Connect

psql is the standard PostgreSQL command-line client. Knowing the essentials makes routine database investigation fast; the discipline is fluency with the common commands. Engineers who reach for psql often benefit from learning its features deeply.

What connecting looks like:

• psql -h host -U user dbname.: The basic invocation. Host, user, database name; psql connects and produces a prompt.
• Backslash help.: The \? command produces the help. New users reach for it; experienced users reference specific commands. The help is comprehensive.
• Connection string.: Beyond flags, psql accepts a connection string (postgresql://user:password@host:port/dbname). The string is convenient for scripts and complex configurations.
• .pgpass for credentials.: Storing passwords in command lines is bad practice. .pgpass holds credentials securely; psql reads them automatically; the workflow is convenient and safe.
• SSL connections.: Production PostgreSQL connections should use SSL. psql supports it via flags or connection string parameters; the discipline is using SSL for production access.

Connection is the foundation. Without efficient connection, everything else is slower than it needs to be.

Inspect

The inspection commands reveal the database's structure. \\dt and \\d are the fundamentals; experienced users add many others to the toolkit.

• \\dt lists tables.: The command shows all tables in the current database. The output is a list; the team can navigate from this overview.
• \\d table describes a table.: Detailed information about a specific table: columns, indexes, constraints, foreign keys. The describe output is comprehensive; investigation starts here.
• \\du shows users.: The list of database users and their roles. Useful for permission investigation; \\du+ shows additional detail.
• \\dn shows schemas.: The schemas in the database. Multi-schema databases benefit from this overview.
• \\l lists databases.: All databases on the server. When connecting to a server with many databases, \\l reveals the inventory.

Inspection commands produce the structural map. Knowing them turns psql from a query tool into a navigation tool.

Perf

The performance commands support query optimization. EXPLAIN ANALYZE and \\timing are the fundamentals; the data they produce drives optimization decisions.

• EXPLAIN ANALYZE for query plans.: EXPLAIN ANALYZE runs the query and shows the execution plan with actual timing. The output reveals where time was spent; optimization targets the slow nodes.
• \\timing on shows query times.: With \\timing on, every query shows its execution time. The data is automatic; the team sees query performance without explicit measurement.
• BUFFERS option.: EXPLAIN (ANALYZE, BUFFERS) shows buffer usage. The data reveals which queries hit cache vs disk; cache-cold queries are slower; the optimization may include warming.
• FORMAT JSON.: EXPLAIN can output JSON. Tooling can parse the JSON; the output is machine-readable; optimization scripts and dashboards use this.
• auto_explain extension.: Beyond ad-hoc EXPLAIN, the auto_explain extension logs plans for slow queries. The data accumulates; the team analyzes patterns; optimization is data-driven.

PostgreSQL CLI essentials are one of those database-tooling skills that pay off across many investigations. Nova AI Ops integrates with database telemetry, surfaces query patterns, and complements the CLI investigation with cluster-wide analysis.