Appendix H — psql and Tooling Reference

A reference for psql (the command-line client), useful system catalogs/views, and the GUI/ecosystem tools.

Connecting

psql -h host -p 5432 -U user -d dbname            # flags
psql "postgresql://user:pw@host:5432/dbname"      # connection string
psql -d mercado                                    # local defaults

psql meta-commands (the essentials)

Navigation / info | Command | Does | |---|---| | \l | list databases | | \c dbname | connect to a database | | \conninfo | current connection (db, user, host, port) | | \dn | list schemas | | \dt | list tables | | \d name | describe table/view/index (columns, types, keys, constraints) | | \d+ name | describe with more detail (size, storage, triggers) | | \di / \dv / \dm | list indexes / views / materialized views | | \df | list functions | | \du | list roles | | \dp / \z | table privileges |

Running SQL & files | Command | Does | |---|---| | \i file.sql | execute a SQL file (server-relative paths) | | \e | edit the last query in your editor | | \copy t FROM 'file.csv' WITH (FORMAT csv, HEADER true) | client-side bulk load (Ch. 31) | | \copy (SELECT ...) TO 'out.csv' WITH (FORMAT csv, HEADER true) | client-side export | | \g | re-run the last query |

Display & session | Command | Does | |---|---| | \x | toggle expanded (vertical) display — great for wide rows | | \timing | toggle per-query timing | | \pset null '∅' | show NULLs visibly | | \set AUTOCOMMIT off | require explicit COMMIT (safer for risky changes) | | \set ON_ERROR_ROLLBACK on | continue a transaction after an error (interactive) | | \watch 5 | re-run the last query every 5 seconds | | \h SELECT | SQL syntax help; \? meta-command help | | \q | quit |

Reminder: SQL ends with ;. A dbname-# prompt = unfinished statement (forgot ;); Ctrl+C cancels.

Useful system catalogs & views

Monitoring (Ch. 38) - pg_stat_activity — currently-running queries, states, durations, waits. - pg_stat_statements (extension) — aggregated query stats (top by total time). Enable: CREATE EXTENSION pg_stat_statements; (+ config). - pg_stat_user_tables — per-table stats incl. n_live_tup, n_dead_tup, last (auto)vacuum/analyze. - pg_stat_user_indexes — index usage (idx_scan); find unused indexes (idx_scan = 0). - pg_locks — current locks (deadlock/contention diagnosis).

Sizes & introspection

SELECT pg_size_pretty(pg_database_size('mercado'));            -- database size
SELECT pg_size_pretty(pg_total_relation_size('orders'));       -- table + indexes
SELECT current_database(), current_user, version();            -- who/where/what

Sequences (identity)

SELECT setval(pg_get_serial_sequence('t','id'), (SELECT max(id) FROM t));   -- reset after manual inserts

GUI & ecosystem tools

Handy one-liners

-- biggest tables
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- long-running queries (> 1 min)
SELECT pid, now()-query_start AS dur, state, query
FROM pg_stat_activity WHERE state <> 'idle' AND now()-query_start > interval '1 min'
ORDER BY dur DESC;

-- cancel / terminate a backend
SELECT pg_cancel_backend(pid);     -- cancel current query
SELECT pg_terminate_backend(pid);  -- end the connection

See also: Chapter 2 (setup/psql), Chapter 38 (administration), Appendix A (setup), Appendix G (EXPLAIN/tuning).