30+ Commands

PostgreSQL CLI (psql) & Admin Commands Matrix

Production PostgreSQL administration reference: psql shortcuts (\l, \dt+), database sizes, terminating stuck queries, active locks, vacuum analyze, and pg_dump backup with 1-click copy.

psql Meta-Commands (10 commands)

psql Meta-Commands

psql -U <user> -d <database> -h <host> -p 5432

Connects to a PostgreSQL database instance via terminal CLI.

PRO TIP Add -W to force password prompt if not stored in ~/.pgpass.

psql Meta-Commands

\l

Lists all databases on the PostgreSQL cluster along with owner and encoding.

PRO TIP Add + (\l+) to display database disk sizes on filesystem.

psql Meta-Commands

\c <database_name>

Switches connection context to a different database.

PRO TIP Can also switch user: \c .

psql Meta-Commands

\dt

Lists all tables in the current schema.

PRO TIP Add + (\dt+) to inspect table disk size and description comments.

psql Meta-Commands

\d <table_name>

Describes table schema: columns, data types, nullability, defaults, and indexes.

PRO TIP Add + (\d+ ) to view storage types and foreign key relationships.

psql Meta-Commands

\dn

Lists all schemas within the current database.

PRO TIP Default user schema is "public".

psql Meta-Commands

\di

Lists all indexes across tables in the current schema.

PRO TIP \di+ shows physical index disk footprints.

psql Meta-Commands

\timing

Toggles query execution time measurement on/off in psql.

PRO TIP Crucial for benchmarking query execution speed directly in terminal.

psql Meta-Commands

\x

Toggles expanded table output mode (displays rows vertically as key: value).

PRO TIP Essential when inspecting tables with dozens of wide columns.

psql Meta-Commands

\q

Quits / exits the psql interactive command prompt.

PRO TIP Ctrl+D also exits cleanly.

Database & Role Management (3 commands)

Database & Role Management

CREATE DATABASE app_db WITH OWNER app_user ENCODING 'UTF8';

Creates a new database with specified owner and UTF-8 encoding.

PRO TIP Always ensure UTF-8 encoding for international character support.

Database & Role Management

CREATE USER app_user WITH ENCRYPTED PASSWORD 'strong_secret';

Creates a new PostgreSQL role with login credentials.

PRO TIP Grant minimal necessary permissions rather than granting SUPERUSER.

Database & Role Management

GRANT ALL PRIVILEGES ON DATABASE app_db TO app_user;

Grants database-level privileges to role.

PRO TIP In Postgres 15+, also grant schema privileges: GRANT ALL ON SCHEMA public TO app_user.