Installing PostgreSQL and connecting with psql

Let us get a PostgreSQL server running and connect to it. Choose one of the options below.

Option 1: Docker

If you have Docker installed, this starts a server with a password and persistent storage:

docker volume create pgdata
docker run --name bookshop-db \
  -e POSTGRES_PASSWORD=devpassword \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql \
  -d postgres:18

Use a strong password for anything beyond your own machine. The volume keeps your data if the container is removed. If you prefer version 17, change the image tag to postgres:17; note that the data directory layout in the official image has changed between major versions, so check the image documentation if you mount a volume from an older setup.

Connect with the psql client inside the container:

docker exec -it bookshop-db psql -U postgres

Option 2: native installers

• macOS: Postgres.app is a simple way to run a server, or use Homebrew with brew install postgresql@18.
• Windows: use the installer linked from postgresql.org, which includes psql and pgAdmin.
• Linux: install from your distribution, or from the PostgreSQL project's own repositories for the latest versions.

Your first psql session

Once connected, you see a prompt like postgres=#. psql accepts SQL statements, which end with a semicolon, and backslash meta commands:

\l              list databases
\c bookshop     connect to the bookshop database
\dt             list tables
\d books        describe the books table
\x              toggle expanded display for wide rows
\timing         show how long each query takes
\q              quit

Create a database for the course:

CREATE DATABASE bookshop;

Then connect to it with \c bookshop. The prompt changes to bookshop=#.

Running a script

Most of the time you will write SQL in files and run them. From the shell:

psql -U postgres -d bookshop -f schema.sql

Or from inside psql with \i schema.sql. Keeping schema and seed data in files makes it easy to rebuild your database from scratch, which you will do often while learning.

Graphical tools

pgAdmin, DBeaver and many editors with database plugins can connect using host localhost, port 5432, user postgres and your password. They are useful for browsing data. Still, learn psql: it is available on every server, and its meta commands are fast once familiar.

Create the bookshop database, connect to it and run SELECT version(); to confirm your PostgreSQL version.