Connecting with psql
It is 2 a.m., the app is down, and the only tool on the server is a terminal. psql, PostgreSQL's command-line client, is always there. A DBA who is fluent in it can connect, look around and fix things in seconds.
The rule
Host, port, user, database — then backslash commands to look around.
Every connection needs four facts. psql takes them as flags, as a URI, or from environment variables:
psql -h db1.pedal.internal -p 5432 -U pedal_admin -d pedal
psql "postgresql://pedal_admin@db1.pedal.internal:5432/pedal"
export PGHOST=db1.pedal.internal PGUSER=pedal_admin PGDATABASE=pedal
psqlAnything you leave out has a default: the host is the local Unix socket, the port is 5432, and both the user and the database default to your operating-system user name. That is why sudo -u postgres psql works on a fresh server: you become the postgres OS user, psql connects as role postgres to database postgres over the local socket.
To avoid typing passwords, put them in ~/.pgpass (host:port:database:user:password, one per line) and make it private with chmod 600, or psql will ignore it.
Inside psql, lines starting with a backslash are meta-commands handled by psql itself, not SQL:
| Command | Shows |
|---|---|
\conninfo |
who and where you are connected |
\l |
databases |
\c reports |
switch to another database |
\dn |
schemas |
\dt |
tables in the search path |
\d rentals |
columns, indexes and constraints of a table |
\du |
roles |
\x |
toggle expanded (vertical) output |
\timing |
show how long each query takes |
\? / \h ALTER TABLE |
help for meta-commands / for SQL |
\q |
quit |
SQL statements run only when psql sees a semicolon. The prompt changes from pedal=> to pedal-> while a statement is unfinished, and a # instead of > means you are a superuser.
The shortcut
When a connection fails, read the error for which of the four facts is wrong. The messages map neatly:
"Connection refused"
Nothing is listening at that host and port: wrong port, server down, or
listen_addressesdoes not include this interface.
The trap is running a destructive command on the wrong server. Make \conninfo a habit before any DROP or DELETE.
Drills
Answer fast — trust the rule.
You run psql as OS user asha with no flags or variables. Which database does it try?
Which psql command lists the columns and indexes of rentals?
The error is "Connection refused". Which fact is most likely wrong?
psql ignores the passwords in your ~/.pgpass. What is the usual cause?
Remember
- Host, port, user, database — then backslash commands to look around.
- Match the error to the fact: refused = address, authentication = login, does not exist = database.
- Run
\conninfobefore anything destructive.