The PostgreSQL Ecosystem

The CLI you'll live in

psql is the official terminal client. It is fast, scriptable, and has powerful meta-commands. Learn it before learning any GUI — you'll use it for the rest of your career, on every machine, every cloud, every container.

The other essential CLI tools

pg_dump — logical backup of one database (SQL or custom format).
pg_restore — restore a custom-format dump.
pg_basebackup — physical backup of the whole cluster.
pgbench — built-in benchmark and load generator.
vacuumdb / reindexdb / clusterdb — maintenance utilities.

The most common extensions

PostGIS — full GIS database.
pg_trgm — fuzzy/typo-tolerant text search.
pgcrypto — hashing, encryption, secure random bytes.
pgvector — embeddings + ANN search.
pg_stat_statements — per-query telemetry; the first thing you turn on in production.
uuid-ossp — historically used for UUID generation; PG 18+ has built-in uuidv7().

GUIs, monitoring, hosting

GUI clients: pgAdmin (official, web), DBeaver (cross-platform), TablePlus (macOS-friendly), Postico (macOS native). Pooling: pgBouncer for transaction-mode connection pooling. HA: Patroni. Hosted: Supabase, Neon, RDS/Aurora, AlloyDB, Azure Flexible Server, Railway, Render.

Code

psql essentials·bash

# Connect
psql -h localhost -U myuser -d mydb

# Inside psql — meta commands
\dt                  -- list tables in current schema
\d+ orders           -- describe the orders table in detail
\df                  -- list functions
\du                  -- list users/roles
\timing on           -- show query execution time
\x auto              -- expanded output for wide rows
\copy orders TO 'orders.csv' WITH CSV HEADER
\i seed.sql          -- run a SQL file
\q                   -- quit

Backup and restore·bash

# Logical dump (SQL text — readable, portable)
pg_dump -h prod-db -U app mydb > mydb.sql

# Logical dump (custom format — faster, parallel restore, selective)
pg_dump -h prod-db -U app -F c -f mydb.dump mydb

# Restore the custom dump into a fresh database
createdb mydb_restore
pg_restore -h localhost -U app -d mydb_restore mydb.dump

Install a few of the headline extensions·sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS vector;

The CLI you'll live in

The other essential CLI tools

The most common extensions

GUIs, monitoring, hosting

Code

External links

Exercise

Progress

Comments 0