PostgreSQL and your first database

Why databases belong in this path

A healthy container can still serve stale data, wait on a lock, or run out of database connections. This module introduces the SQL and PostgreSQL fundamentals you’ll use to operate MicroBank on Kubernetes. Start after the system-design review. No prior SQL is required.

We use PostgreSQL 15 to match the existing MicroBank lab's major version. Version 15 is the compatibility baseline for these exercises. Record the actual patch version and image digest. The examples use a deliberately separate teaching database; they do not represent MicroBank's schema or a complete banking ledger.

Name each boundary

A client authenticates, selects a database, and sends SQL. PostgreSQL parses, plans, and executes queries. A connection accepting SQL proves less than a correct application transaction. Architectural fundamentals↗ explains the client/server boundary.

Start one local fixture

Use your local OrbStack/Docker engine. Stop the MicroBank Compose profile or your disposable learning cluster first using its own documented procedure; keep any data you need. Do not run all profiles together on a 16 GB host. Check docker context show points to your local engine.

Inspect the names below first. If either already exists, identify its owner and resume your own previous exercise or choose a fresh pair of names consistently. Do not overwrite someone else's data. In an empty lab directory:

docker ps -a --filter name=learnwithsk-postgres
docker volume ls --filter name=learnwithsk-postgres-data
docker run -d --name learnwithsk-postgres \
  --network none --memory 512m --cpus 1 \
  -e POSTGRES_DB=study -e POSTGRES_PASSWORD=local-study-only \
  --mount type=volume,src=learnwithsk-postgres-data,dst=/var/lib/postgresql/data \
  postgres:15-bookworm
docker exec learnwithsk-postgres pg_isready -U postgres -d study
docker image inspect postgres:15-bookworm --format '{{json .RepoDigests}}'
docker stats --no-stream learnwithsk-postgres

Wait for pg_isready to report accepting connections; inspect container logs if it does not. The memory and CPU limits keep this small exercise contained; choose production allocations from measured requirements. No host port is published and container networking is disabled. Commands use the container's local Unix socket. The visible password is only for this isolated fixture. Use a separate secret-management approach for application or production credentials. Image initialization settings apply only to a new, empty data volume. See the official image documentation↗.

Open an interactive session:

docker exec -it learnwithsk-postgres psql -X -U postgres -d study

Inside psql:

\conninfo
SELECT version(), current_database(), current_user;
SHOW search_path;
\dn
\q

Backslash commands belong to psql; they are not SQL sent by an application. SQL statements end with a semicolon. -X ignores a local psql startup file. In scripts, use -v ON_ERROR_STOP=1 so a failed statement stops the script rather than hiding an incomplete setup. psql reference↗.

Checkpoint

Record your image digest, server version, database, role, and container allocation. Explain why localhost in another container would not address this database. Continue to the schema bite to create the fixture once. Keep it for the whole module; the operations lab includes cleanup.

Check your first session

You should be able to point to three different names: container learnwithsk-postgres, database study, and a schema that will be created inside it next. The database and future schema intentionally share a name but are different objects. current_user should show the fixture administrator; it does not identify a Linux login on your Mac.

The other-container localhost answer is its own network namespace, not this server. Here no network route is provided at all: docker exec starts the client inside the database container, and psql uses its Unix socket. If \dn does not yet show a study schema, that is expected before the next bite creates it. Keep the server and use the same database throughout the next seven bites.