PostgreSQL interactive terminal and query tool.
high risk
psql -v ON_ERROR_STOP=1 -1 -f change.sql <db>Run a script in ONE transaction (-1) that stops at the first error.pg_dump -Fc <db> > before.dumpTake a backup before schema or bulk data changes.psql is the interactive terminal for PostgreSQL. Beyond running queries, it supports meta-commands (\d, \l, \c) for schema inspection, bulk loading with \copy, and scripting with -c and -f flags. Essential for database administration and debugging.
| Flag | Meaning |
|---|---|
-h --host | Database host |
-U --username | Database user |
-d --dbname | Database name |
-c --command | Run a single SQL command |
-f --file | Execute SQL from file |
-t --tuples-only | Print only data rows (no headers) |
psql -h localhost -U postgres -d mydb -c 'SELECT count(*) FROM users;'Run quick query from shell · explain & risk-check →psql -h db.prod -U readonly -d app -f report.sqlRun SQL script against production (read-only user) · explain & risk-check →psql -U postgres -c "SELECT pid, query, state, wait_event FROM pg_stat_activity WHERE state='active';"Check active queries · explain & risk-check →psql -U postgres -t -c 'SELECT datname FROM pg_database;'List databases (tuples-only for scripting) · explain & risk-check →Use `psql -U postgres -c 'SELECT pg_size_pretty(pg_database_size(datname)), datname FROM pg_database ORDER BY 1 DESC;'` to instantly identify bloated databases on shared hosts.
Reference text is hand-written and may be incomplete — check man psql for your version. Test destructive commands on a non-production system first.