Installing PostgreSQL and connecting to it

Twenty minutes, and you have a PostgreSQL server running and a table with rows in it. Everything after this module assumes you can get to a psql prompt.

Two routes: Docker, which is the same on every machine and leaves nothing behind, and a native install, which is what you would run on a server. Do Docker if you have it.

Route 1: Docker

docker run -d \
  --name pg-course \
  -e POSTGRES_PASSWORD=learning \
  -p 5432:5432 \
  -v pg-course-data:/var/lib/postgresql/data \
  postgres:16-alpine

Each line earns its place:

-d runs it in the background.
--name pg-course so you can say docker stop pg-course later.
-e POSTGRES_PASSWORD=... is required — the image refuses to start without it.
-p 5432:5432 maps the container's port to yours, so localhost:5432 reaches it.
-v pg-course-data:/var/lib/postgresql/data keeps the data in a named volume, so removing the container does not delete your database. Leave this out and everything vanishes when the container does, which is a surprise people only have once.
postgres:16-alpine pins the version. Alpine is the small image.

Check it started:

docker ps
docker logs pg-course | tail -5

You are looking for database system is ready to accept connections. If the container exited, docker logs says why — usually the password was missing or port 5432 is already in use.

Then get a prompt:

docker exec -it pg-course psql -U postgres

exec -it runs a command inside the running container, interactively. -U postgres is the default superuser the image creates.

Stopping and starting:

docker stop pg-course
docker start pg-course

The data survives both, because of the volume.

Route 2: a native install

macOS — the simplest route is Postgres.app: download, drag to Applications, open, click Initialize. It puts a server on port 5432 and gives you a menu bar icon. Or with Homebrew:

brew install postgresql@16
brew services start postgresql@16

Windows — the EnterpriseDB installer from postgresql.org/download/windows. It includes pgAdmin. Note the password you set for postgres; you will need it. It also adds psql to your PATH, which the installer asks about — say yes.

Linux (Debian or Ubuntu):

sudo apt install postgresql-16
sudo systemctl start postgresql
sudo -u postgres psql

sudo -u postgres matters: on Linux the default authentication is "peer", which means the operating system user must match the database user.

The prompt

psql (16.4)
Type "help" for help.

postgres=#

postgres=# is the database you are in, and # means superuser (> means an ordinary user).

The commands starting with a backslash are psql's own, not SQL:

\l          list databases
\c name     connect to a database
\dt         list tables
\d books    describe the table "books"
\dn         list schemas
\du         list users
\x          toggle expanded output — invaluable for wide rows
\timing     toggle showing how long each query took
\?          all the backslash commands
\h SELECT   SQL help for a specific statement
\q          quit

Learn \d, \dt, \x and \timing now. \x in particular turns an unreadable 40-column row into a readable list, and \timing is how you will measure everything in module 7.

Semicolons. SQL statements end with ;. Without one, psql shows a continuation prompt postgres-# and waits. If you are stuck at one, type ; and press Enter — or \r to discard the line entirely. Everybody hits this on day one.

Make a database

Do not work in postgres. Make your own:

CREATE DATABASE library;
\c library

You are now connected to database "library" as user "postgres".
library=#

Names are case-insensitive unless you quote them. CREATE DATABASE Library gives you a database called library. Quoting makes them case-sensitive and permanently annoying, so use lowercase with underscores and never quote.

A table, and rows

CREATE TABLE books (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title     text NOT NULL,
  author    text NOT NULL,
  published date,
  available boolean NOT NULL DEFAULT true
);

INSERT INTO books (title, author, published) VALUES
  ('Malgudi Days',      'R K Narayan',     DATE '1943-01-01'),
  ('Train to Pakistan', 'Khushwant Singh', DATE '1956-01-01'),
  ('Godaan',            'Munshi Premchand', DATE '1936-01-01');

SELECT * FROM books;

 id |       title       |      author      | published  | available
----+-------------------+------------------+------------+-----------
  1 | Malgudi Days      | R K Narayan      | 1943-01-01 | t
  2 | Train to Pakistan | Khushwant Singh  | 1956-01-01 | t
  3 | Godaan            | Munshi Premchand | 1936-01-01 | t
(3 rows)

That is a working database. Module 2 starts here.

GENERATED ALWAYS AS IDENTITY is the modern way to get an auto-incrementing key, and it is preferred over the older serial — module 4 explains why.

Prove the three promises

Worth doing now, while the table is small, because it makes the last two lessons concrete.

The type system refuses nonsense:

INSERT INTO books (title, author, published)
VALUES ('A Book', 'Someone', DATE '2026-02-30');

ERROR:  date/time field value out of range: "2026-02-30"

There is no 30th of February, and the database knows.

NOT NULL refuses missing data:

INSERT INTO books (author) VALUES ('Someone');

ERROR:  null value in column "title" of relation "books" violates not-null constraint

Atomicity — all or nothing:

BEGIN;
INSERT INTO books (title, author) VALUES ('First', 'A');
INSERT INTO books (title, author) VALUES (NULL, 'B');   -- fails
COMMIT;
SELECT count(*) FROM books;

The count is still 3. The second statement failed, which aborted the transaction, so the first was rolled back too. Neither insert happened.

Note what psql says after the failure:

ERROR:  current transaction is aborted, commands ignored until end of transaction block

That message confuses everybody once. It means: this transaction is dead, and nothing more will run until you COMMIT (which becomes a rollback) or ROLLBACK.

A graphical client, optional

psql is what this course uses, and it is what is on a server at 2am when nothing else is. Learn it.

A GUI is genuinely useful alongside it for browsing a schema you do not know:

DBeaver — free, every database, every platform.
pgAdmin — PostgreSQL's own, bundled with the Windows installer.
TablePlus — paid, pleasant, macOS and Windows.

Connect any of them with: host localhost, port 5432, user postgres, password whatever you set, database library.

When it will not connect

The four causes, in the order to check them.

connection refused — the server is not running, or not on that port. docker ps, or brew services list, or systemctl status postgresql.

password authentication failed — wrong password, or wrong user. With Docker it is whatever you passed as POSTGRES_PASSWORD.

role "you" does not exist — psql defaulted to your OS username. Add -U postgres.

port is already allocated — something else has 5432, frequently an earlier PostgreSQL. Either stop it, or map a different port: -p 5433:5432, then connect with -p 5433.

Loading the course data

Each module has a .sql file of sample data. Load one with:

docker exec -i pg-course psql -U postgres -d library < module-02.sql

Note -i, not -it — there is no terminal, you are piping a file in. This is the most common mistake with that command.

Natively:

psql -U postgres -d library -f module-02.sql

Check your work

Why the Docker volume matters. Without it the data disappears with the container.

What the image refuses to start without. POSTGRES_PASSWORD.

What docker logs tells you. Why a container that exited did so.

What postgres=# means. The database you are in, and that you are a superuser.

The four psql commands to learn first. \d, \dt, \x, \timing.

Why you are stuck at postgres-#. A missing semicolon. Type ; or \r.

Why not to quote identifiers. Quoting makes them case-sensitive forever.

The modern auto-increment. GENERATED ALWAYS AS IDENTITY, not serial.

What "current transaction is aborted" means. The transaction is dead until you commit or roll back.

Why the count stayed at 3. One failed statement aborted the transaction, rolling back the successful one too.

The four connection failures. Not running, wrong password, wrong user, port taken.

-i versus -it when loading a file. There is no terminal when piping.

Practice

Start PostgreSQL by either route and reach a psql prompt.
Run \l, \du and \dn. Note what is already there.
Create the library database and connect to it.
Create the books table and insert the three rows.
Run \d books and read every line of the output.
Turn on \x and select a row. Turn it off. Note when each is better.
Turn on \timing and run a query.
Type a query without a semicolon and get out of the continuation prompt both ways.
Insert the 30th of February. Read the error.
Insert a book with no title. Read that error.
Run the BEGIN block above and confirm the count is still 3.
Inside a failed transaction, try another SELECT and read the message.
Stop the container and start it again. Confirm the rows survived.
Remove the container entirely with docker rm, recreate it with the same volume, and confirm the data is still there.
Then do it without the volume and watch the data vanish. Worth doing once.
Connect with DBeaver or pgAdmin and find your table.

Official documentation

PostgreSQL — Installation — Every platform, with the official packages.
PostgreSQL — Getting started tutorial — Creating a database and a first table, from the source.
PostgreSQL — psql reference — Every backslash command and every flag. Long, but \? covers the daily ones.
Docker Hub — postgres image — Every environment variable the image accepts, including how to seed a database on first run.
PostgreSQL — Client authentication — What pg_hba.conf does, for when "peer" authentication surprises you on Linux.

You now know what a database is, why a file is not one, what the seven families are for, what the relational model actually claims, what ACID and CAP really mean, and you have a server running.

Next module: getting data back out, which is most of what you will ever do.