Postgres – psql and the Tools You Actually Use

psql ships with Postgres, is present on every server you will ever have to debug, and is what every answer you find online assumes you are using. A GUI is a nice place to read data; psql is where you work.

This post is the subset worth memorising, plus the settings that make it bearable.

Connecting

psql "postgresql://stayhub:stayhub@localhost:5433/stayhub_lab"

# or as separate flags, with PGPASSWORD in the environment
psql -h localhost -p 5433 -U stayhub -d stayhub_lab

Once connected, the prompt tells you which database you are in — which matters more than it sounds the first time you run a DELETE in the wrong window.

Against anything that is not on your own machine, add sslmode and mean it. The default, prefer, will silently fall back to an unencrypted connection if the server does not offer TLS, which is exactly backwards for a production database:

psql "postgresql://app@db.example.com:5432/app?sslmode=verify-full&sslrootcert=/etc/ssl/rds-ca.pem"

require encrypts but does not check who you are talking to. verify-full checks the certificate and the hostname, and is the only setting that actually stops an interception.

The meta-commands worth knowing

Anything starting with a backslash is handled by psql itself, not sent to the server.

\d on a table is the one you will run most. It answers most schema questions without a single query against the catalog:

stayhub_lab=# \d bookings
                                           Table "public.bookings"
       Column        |           Type           | Collation | Nullable |               Default
---------------------+--------------------------+-----------+----------+--------------------------------------
 id                  | integer                  |           | not null | nextval('bookings_id_seq'::regclass)
 property_id         | integer                  |           | not null |
 check_in            | date                     |           | not null |
 check_out           | date                     |           | not null |
 total               | numeric(10,2)            |           | not null |
 status              | character varying(20)    |           | not null |
 created_at          | timestamp with time zone |           | not null | now()
Indexes:
    "pk_bookings" PRIMARY KEY, btree (id)
    "ix_bookings_property_dates" btree (property_id, check_in, check_out)
    "ix_bookings_status" btree (status)
    "no_overlapping_bookings" EXCLUDE USING gist (property_id WITH =, ...)
Check constraints:
    "ck_bookings_checkout_after_checkin" CHECK (check_out > check_in)
Foreign-key constraints:
    "fk_bookings_property_id_properties" FOREIGN KEY (property_id) REFERENCES properties(id)

Note what is in there beyond the columns: every index, every constraint and every foreign key. Reading that before you write a query saves you writing the wrong one.

Sizes, without remembering catalog queries

\di+ answers "which indexes are expensive" directly:

stayhub_lab=# \di+ ix_bookings*
 Schema |            Name            | Table    | Access method |  Size
--------+----------------------------+----------+---------------+---------
 public | ix_bookings_guest_id       | bookings | btree         | 4632 kB
 public | ix_bookings_property_dates | bookings | btree         | 17 MB
 public | ix_bookings_property_id    | bookings | btree         | 3968 kB
 public | ix_bookings_public_id      | bookings | btree         | 16 MB
 public | ix_bookings_status         | bookings | btree         | 2560 kB

Two things jump out that no schema file would tell you: a three-column index costs four times one on a single integer, and the UUID index costs nearly as much again — because a random UUID gives a b-tree no locality at all. Both facts come back in the indexes post.

Running a file

This is the flag that matters, and leaving it out is how a migration half-applies:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f migration.sql

By default psql keeps going after an error, so a failed statement in the middle of a script scrolls past and the script reports success. ON_ERROR_STOP=1 makes it exit non-zero on the first failure — which is what any script running it needs.

Wrapping the file in a transaction makes the whole thing atomic, so a failure leaves nothing behind:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 --single-transaction -f migration.sql

Getting data out

# CSV, straight to a file on YOUR machine, not the server's
psql "$DATABASE_URL" -c "\copy (SELECT id, city FROM properties LIMIT 100) TO 'out.csv' CSV HEADER"

# quiet, unaligned, no headers — for shell scripts
psql "$DATABASE_URL" -At -c 'SELECT count(*) FROM bookings'
# 400000

\copy and COPY are not the same thing. COPY is SQL, runs on the server, writes to the server's filesystem and needs superuser rights. \copy is psql, streams through your connection, and writes where you are sitting. For a developer it is almost always \copy.

Four interactive habits

These are the ones that change how the session feels rather than what it can do.

SELECT * FROM properties WHERE id = 7 \gx   -- run, and show expanded, once

SELECT count(*) FROM bookings WHERE status = 'PENDING' \watch 2

\e                                          -- edit the last query in $EDITOR
\i schema.sql                               -- run a file from inside the session

• \gx ends a statement and shows it expanded, one column per line, without leaving \x on for everything afterwards. The right way to look at one wide row.
• \watch 2 re-runs the statement every two seconds. This is the whole of your monitoring during a migration or a backfill — watch the count move.
• \e opens the last query in your editor. Multi-line SQL is miserable to fix at a prompt and pleasant to fix in vim.
• Ctrl-C cancels the running query without dropping the connection. It is safe: the statement rolls back.

When the query you need to stop is in somebody else's session, cancel it from yours:

SELECT pid, state, now() - query_start AS running_for, query
FROM   pg_stat_activity
WHERE  state = 'active' AND backend_type = 'client backend'
ORDER  BY running_for DESC;

SELECT pg_cancel_backend(12345);      -- ask it to stop; the transaction rolls back
SELECT pg_terminate_backend(12345);   -- drop the whole connection, if it ignores the first

Reach for pg_cancel_backend first. pg_terminate_backend kills the connection, and an application that was mid-transaction gets an error it may not handle well.

A .psqlrc worth having

Read once at startup, from your home directory:

# ~/.psqlrc
\set QUIET 1
\timing on
\set ON_ERROR_ROLLBACK interactive
\pset null '(null)'
\set COMP_KEYWORD_CASE upper
\set HISTFILE ~/.psql_history-:DBNAME
\unset QUIET

When a GUI is the right tool

pgAdmin, DBeaver, DataGrip and TablePlus all connect with the same URI. They earn their place for browsing an unfamiliar schema, editing a wide row by hand, and drawing a plan as a tree instead of indented text.

They are the wrong tool for anything you need to repeat, anything you need to review, and anything running on a server you reached through SSH. Learn psql first; a GUI is easy to add later, and the reverse is not true.