Appendix A — PostgreSQL Setup Guide

A consolidated, standalone setup reference (expands Chapter 2). Get PostgreSQL 15+ running on any platform, load the Mercado practice database, and verify it.

What you're installing

  • The PostgreSQL server — the DBMS that manages your data (a background process listening on port 5432).
  • psql — the official command-line client (used throughout this book).
  • Optionally a GUI (pgAdmin / DBeaver).

You want PostgreSQL 15 or newer (16/17 are great too).

Install by platform

macOS

  • Postgres.app (easiest): download from https://postgresapp.com/, drag to Applications, open, click Initialize. Add its CLI tools to PATH as instructed.
  • Homebrew: brew install postgresql@16 then brew services start postgresql@16.

Windows

  • Download the EDB installer from https://www.postgresql.org/download/windows/; run with defaults; record the postgres password you set. Use "SQL Shell (psql)" or PowerShell. Add C:\Program Files\PostgreSQL\16\bin to PATH if psql isn't found.

Linux (Debian/Ubuntu)

sudo apt update && sudo apt install postgresql postgresql-client
sudo systemctl enable --now postgresql
sudo -u postgres psql      # superuser shell

Fedora/RHEL: sudo dnf install postgresql-server postgresql, then sudo postgresql-setup --initdb, then enable the service.

docker run --name mercado-pg -e POSTGRES_PASSWORD=mercado -e POSTGRES_DB=mercado \
  -p 5432:5432 -d postgres:16
docker exec -it mercado-pg psql -U postgres -d mercado

Stop/start: docker stop|start mercado-pg. Data persists in the container volume.

Verify the install

psql --version          # expect: psql (PostgreSQL) 16.x

If "command not found," add PostgreSQL's bin to your PATH.

Connecting with psql

psql -h localhost -p 5432 -U postgres -d postgres
# or a connection string:
psql "postgresql://postgres@localhost:5432/postgres"

A client needs four things: host, port, database, user (+password).

Essential psql meta-commands

Command Does
\l list databases
\c mercado connect to a database
\dt list tables
\d customers describe a table
\di list indexes
\dn list schemas
\du list roles
\i file.sql run a SQL file
\x toggle expanded display
\timing toggle query timing
\conninfo where am I connected?
\q quit

Remember: SQL statements end with ;. A mercado-# prompt means "still waiting" — you forgot the semicolon (Ctrl+C cancels).

Load the Mercado practice database

createdb mercado
psql -d mercado -f sql/schema.sql          # 13 tables (Appendix B)
psql -d mercado -f sql/seed-sample.sql      # small, deterministic (matches the book's output)
#   for the performance chapters (23–25), use the large dataset instead:
# psql -d mercado -f sql/generate_data.sql   # ~100K orders, ~450K rows; runs ANALYZE

Verify:

\dt                                          -- expect 13 tables
SELECT count(*) FROM customers;              -- 12 (sample)

GUIs (optional)

Connect with the same host/port/database/user psql uses.

Troubleshooting

Symptom Cause / fix
psql: command not found bin not on PATH — add it
connection refused server not running — start it (brew services start / systemctl start / docker start)
password authentication failed wrong password/user; on Linux try sudo -u postgres psql
database "mercado" does not exist run createdb mercado
permission denied to create database connect as a superuser (postgres) or grant CREATEDB
\i sql/schema.sql not found path is relative to where you launched psql; use the full path
tables "missing" wrong database/server — run \conninfo (Chapter 2's mystery)

Pin your version

Decide on one PostgreSQL version (15, 16, or 17) for the whole book and stick with it. Consistency beats novelty.

See also: Chapter 2 (Setting Up), Appendix B (the Mercado schema), Appendix H (psql & tooling reference).