Your first database: Postgres and psql

Getting inside

In Lesson 1, every command was a single shot: docker exec ... psql -c '...', one question, one answer, back to your terminal. That's handy in a script, and tiring by hand. Open psql and stay in it instead:

docker exec -it larkspur psql -U postgres

The new part is -it: -i keeps your keyboard connected to the program, and -t gives it a proper terminal, so the prompt and arrow keys work. Without them, psql starts, finds nobody typing, and quits.

The prompt changes to postgres=#. Everything you type now goes to psql, not to your terminal. Two kinds of thing can be typed here:

• SQL, like SELECT * FROM towns;. It isn't sent until you type a semicolon. Press Enter without one and psql just waits for more, with the prompt changed to postgres-# to show it's mid-statement. That's how you spread a long query over several lines. Forgotten the semicolon? Type it on its own line.
• Backslash commands, like \dt. These aren't SQL; they're psql's own shortcuts. They need no semicolon, and they end at the end of the line.

To leave, type \q, or press Ctrl-D. Ctrl-C cancels what you're typing (or a query that's taking too long) without leaving.

Think of psql as a phone line to the librarian. Lesson 1 was sending postcards: one question each, and wait for the reply. Now you're on the phone, and you can keep asking. The backslash commands are questions about the library itself: which rooms there are, what's on a shelf. SQL is questions about the books.

Where am I?

The first thing to know in any database is where you are. \conninfo says:

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

You're in a database called postgres, not larkspur. One Postgres server holds many databases, each with its own tables, and you're always connected to exactly one. Since this command didn't say which with -d, psql picked the one named after the user. \l lists them all:

                                                      List of databases
   Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 larkspur  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | 
 postgres  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | 
 template0 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
           |          |          |                 |            |            |            |           | postgres=CTc/postgres
(4 rows)

Four databases, and you made one of them:

• larkspur is the county, made by larkspur.sql.
• postgres is an empty one every server starts with, so there's always somewhere to connect to.
• template1 is the pattern every new database is copied from. template0 is a clean spare of it, never changed. You'll never need to touch either.

UTF8 is the text encoding: it means any language's letters can be stored. The Access privileges column is who may connect; empty means the default, and Lesson 11 is about changing it.

Move to the county with \c (connect):

You are now connected to database "larkspur" as user "postgres".

The prompt changes to larkspur=#, and \conninfo agrees:

You are connected to database "larkspur" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Watch the prompt. It always shows which database you're in. Most "my table has vanished" moments are really "I'm in the wrong database". And asking for one that doesn't exist is an error, not an empty database:

docker exec larkspur psql -U postgres -d nowhere -c "SELECT 1"

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "nowhere" does not exist

Finding your way around

What tables are here? \dt, from Lesson 1:

          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | leaders  | table | postgres
 public | meetings | table | postgres
 public | towns    | table | postgres
(3 rows)

What does a table look like? \d leaders:

               Table "public.leaders"
 Column  |  Type   | Collation | Nullable | Default 
---------+---------+-----------+----------+---------
 id      | integer |           | not null | 
 town_id | integer |           |          | 
 name    | text    |           |          | 
 role    | text    |           |          | 
 started | date    |           |          | 
 ended   | date    |           |          | 
Indexes:
    "leaders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "leaders_town_id_fkey" FOREIGN KEY (town_id) REFERENCES towns(id)

This is the other end of the link you saw from towns in Lesson 1. There, \d towns said it was Referenced by leaders. Here, leaders lists the same link under Foreign-key constraints: town_id must be a real town's id. started and ended are dates, so a term is two dates, not two bits of text.

A wide row is hard to read. Tables print one row per line, so a row with many columns wraps across the screen. \x switches to expanded display: one line per column instead.

\x
SELECT * FROM towns WHERE id = 1;

Expanded display is on.
-[ RECORD 1 ]---------------------------
id         | 1
name       | Ashford
kind       | city
population | 48210
founded    | 1851
website    | https://www.ashfordcity.gov
phone      | (555) 010-1100

Type \x again to switch it back off. It's the command I use most on CivicGrid's cities table, which is far too wide to read any other way.

How long did that take? \timing on prints how long each query took, from then on:

\timing on
SELECT count(*) FROM meetings;

Timing is on.
 count 
-------
    30
(1 row)

Time: 0.209 ms

A fifth of a millisecond, for 30 rows. Lesson 9 uses this to see what an index is worth: the same query, before and after.

What's the syntax of that again? \h gives help on any SQL command, without leaving psql:

Command:     DROP TABLE
Description: remove a table
Syntax:
DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

URL: https://www.postgresql.org/docs/16/sql-droptable.html

That's \h DROP TABLE. Square brackets mean optional, and the link goes to the full page in the manual. \h on its own lists every SQL command, and \? lists every backslash command. You don't need to memorize them: you need to remember \?.

What happens to your data

Your county is in Postgres, and Postgres is in a container. So what happens to the county when the container stops? Let's find out.

Stop it, and start it again:

docker stop larkspur
docker ps -a --filter name=larkspur --format "{{.Names}}  {{.Status}}"

larkspur
larkspur  Exited (0) Less than a second ago

docker ps -a shows stopped containers too. Exited (0) means it stopped cleanly. Start it, and count the towns:

docker start larkspur
docker exec larkspur psql -U postgres -d larkspur -c "SELECT count(*) FROM towns"

larkspur
 count 
-------
    12
(1 row)

All 12. Stopping a container is like switching off a computer: everything on its disk is still there when it comes back. (If you get a "can't connect" error straight after a start, Postgres is still waking up. Wait a few seconds and try again.)

Now delete it, and make a new one with the same command as Lesson 1:

docker rm -f larkspur
docker run -d --name larkspur -e POSTGRES_PASSWORD=practice -v "$PWD":/practice postgres:16
docker exec larkspur psql -U postgres -d larkspur -c "SELECT count(*) FROM towns"

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "larkspur" does not exist

The county is gone, and \l agrees: only postgres, template0 and template1 are left. docker rm -f stops a container and deletes it; -f (force) does both in one step.

But it wasn't really deleted. Postgres keeps everything it stores in one folder, its data directory: in this image, /var/lib/postgresql/data. Here's what the old container had attached, before it went:

docker inspect larkspur --format "{{range .Mounts}}{{.Type}}  {{.Destination}}{{println}}{{end}}"

bind  /practice
volume  /var/lib/postgresql/data

Two things were attached:

• bind /practice is your sql-practice folder, which you asked for with -v "$PWD":/practice. A bind mount is a folder on your computer, shown inside the container.
• volume /var/lib/postgresql/data you never asked for. The Postgres image asks for it: it says its data directory must live in a volume, storage that Docker keeps outside any container. Since you didn't name one, Docker made one, and named it with a long random ID.

So the data was safe, in a volume. Deleting the container didn't delete the volume: after docker rm, it was still there.

37897c587dcd67c2c098e133da3e24db30671b428a3d35cbfa310350e8da5538 still exists

But the new container didn't know that volume existed, so Docker made it another new, empty one. Your county is sitting in a volume nobody will ever find by name, taking up disk space. docker volume ls would list it among the others, with nothing to say what's in it.

Think of the container as a rented apartment, and the volume as a storage unit. Moving out of the apartment doesn't empty the unit. But if the unit has no name on the door, the next tenant can't find it, and gets a new, empty one of their own.

The fix: name the volume

Give the volume a name, and every container you make with that name gets the same data:

docker rm -f larkspur
docker run -d --name larkspur -e POSTGRES_PASSWORD=practice \
  -v larkspur-data:/var/lib/postgresql/data \
  -v "$PWD":/practice postgres:16

-v larkspur-data:/var/lib/postgresql/data looks like the bind mount, but the part before the colon is a name, not a path. Docker makes the volume larkspur-data the first time, and reuses it every time after. Load the county into it:

docker exec larkspur psql -U postgres -q -f /practice/larkspur.sql

psql:/practice/larkspur.sql:6: NOTICE:  database "larkspur" does not exist, skipping

-q (quiet) hides the CREATE TABLE and INSERT lines, so you only see the notice from Lesson 1. Now the real test: delete the container, and make a new one with the same volume.

docker rm -f larkspur
docker run -d --name larkspur -e POSTGRES_PASSWORD=practice \
  -v larkspur-data:/var/lib/postgresql/data \
  -v "$PWD":/practice postgres:16
docker exec larkspur psql -U postgres -d larkspur -c "SELECT count(*) FROM towns"

 count 
-------
    12
(1 row)

A brand new container, and all 12 towns. The volume is listed by its name now:

docker volume ls --filter name=larkspur

DRIVER    VOLUME NAME
local     larkspur-data

This is how to start the practice database from now on. If you followed Lesson 1, switch over now. Find the old unnamed volume first, so you can remove it:

docker inspect larkspur --format "{{range .Mounts}}{{.Name}} {{end}}"

That prints the volume's long ID (and nothing for the bind mount, which has no name). Then docker rm -f larkspur, docker volume rm followed by that ID, then the docker run above, and load the county. Remove volumes only by the ID you found. docker volume prune would also remove volumes from your other projects.

Spot the problem

Work out what's wrong before you read the explanation.

The upgrade. Someone has a named volume full of Postgres 16 data, and wants Postgres 17. They run docker rm -f, then the same docker run, but with postgres:17. Same volume, newer version: what could go wrong? The new server refuses to start. Each major version of Postgres stores its data directory differently, and Postgres won't open files written by another major version, rather than risk misreading them. The data is safe, but the new container won't use it. The usual way across is to dump the data out of the old version as SQL, and load it into the new one, which is what Lesson 13 covers. (Minor versions, like 16.14 to 16.15, are fine: those only fix bugs.)

Where you are now

You can work inside psql, tell which database you're in, find your way around it, and you know where your data really lives: in a volume, which now has a name. Lesson 3 starts asking real questions: SELECT, WHERE and ORDER BY, including the fix for Dunmore sorting first.

Check yourself

Key terms

• psql: Postgres's command-line client. Inside it, SQL ends with a semicolon, and backslash commands end at the end of the line.
• Backslash command: a psql shortcut, not SQL, like \dt or \q.
• Database (on a server): one of many on the same Postgres server, each with its own tables. You're connected to one at a time.
• Expanded display: \x, showing one line per column, for wide rows.
• Data directory: the folder where Postgres keeps everything it stores.
• Bind mount: a folder on your computer, shown inside a container.
• Volume: storage Docker keeps outside any container, so it outlives one. A named volume can be found again by name.

Go deeper

• The psql page of the manual: psql (opens in a new tab). Long, but the list of backslash commands is worth skimming once.
• Inside psql, try \d+ towns. What does the + add?
• Inside psql, try \x auto: psql then switches to expanded display by itself, only when a row is too wide for the screen.
• The Docker track's Data and settings lesson, for volumes and bind mounts in more depth.