Lab: install PostgreSQL and connect

Install

macOS with Homebrew

This is the path we tested, on macOS with Apple Silicon and PostgreSQL 18.3.

brew install postgresql@18

Homebrew installs postgresql@18 as keg-only: the programs are not put on your PATH, so a shell cannot find psql yet. Add them:

echo "export PATH=\"$(brew --prefix postgresql@18)/bin:\$PATH\"" >> ~/.zshrc
source ~/.zshrc
psql --version

The install also created a database cluster: a data directory, already initialized, where PostgreSQL keeps everything. Start the server as a background service, so it runs now and after every restart:

brew services start postgresql@18

Linux, Windows, or Docker

Use the official instructions at postgresql.org/download, which cover each Linux distribution, the Windows installer, and other systems. We have not tested those routes in this lab, so follow the official page rather than a copy of it. Pick version 18 so your results match this field.

Connect

On macOS with Homebrew, the server already has a superuser named after your macOS account, so you can connect without a password:

psql postgres

postgres is a database every new cluster contains, meant as a default for users and tools to connect to. You should see a prompt like postgres=#. Type \q to leave.

Check what you are connected to:

SELECT current_user, current_setting('port'), current_setting('data_directory');

On our machine that returned apple | 5432 | /opt/homebrew/var/postgresql@18: our macOS username, the default port, and the data directory Homebrew created.

Load the practice database

Download shop.sql into your working folder, then:

createdb shop
psql -d shop -f shop.sql
psql shop

createdb makes a new, empty database; psql -d shop -f shop.sql runs every statement in the file against it. The first time, you will see a few NOTICE: table "…" does not exist, skipping lines: the file drops its tables before creating them, and on a fresh database there is nothing to drop. That is expected.

Now run your first query:

SELECT count(*) FROM customers;

	Ours	Yours
Customers	8
Products (SELECT count(*) FROM products;)	11
Orders	12
Order items	19

If your counts match, you have the same data this whole section uses, and every result shown on later pages should match yours exactly.

If something goes wrong

Symptom	Likely cause	Fix
psql: command not found	The PATH line did not take effect	Open a new terminal, or run source ~/.zshrc
connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory	The server is not running	brew services start postgresql@18, then brew services list
database "shop" does not exist	createdb shop was skipped	Run it, then load the file again

Check yourself

1. What does brew services start postgresql@18 do that running psql does not?

   Show answer

   It starts the PostgreSQL server, the program that owns the data, and keeps it running in the background. psql is only a client that connects to a running server.

   I could explain this without looking

2. Why can you connect on macOS without creating a user or password first?

   Show answer

   The cluster was initialized by your macOS account, and its first superuser is named after the operating-system user that created it. Homebrew’s default setup lets that local user connect.

   I could explain this without looking

3. What does psql -d shop -f shop.sql do?

   Show answer

   It connects to the shop database and runs every SQL statement in the file shop.sql.

   I could explain this without looking