NAVIGATION
Lesson Plan
From a DBML schema to a physical database in Docker
1
How to convert an image from dbdiagram.io into strict SQL DDL: surrogate keys with `GENERATED ALWAYS AS IDENTITY`, named `CHECK` constraints, composite foreign keys, the `ON DELETE` policy, and container auto-initialization.
2
Command-line (CLI) and graphical user interface (GUI) clients
Basic engineering tasks in psql (inspection metacommands, ON_ERROR_STOP), connecting DBeaver to the container at 127.0.0.1, and verifying the schema using a reverse ER diagram (Reverse Engineering).
3
Roles, Schemas, and Access Rights (RBAC)
Why a microservice should never run under `postgres`. Separating roles into `game_owner`, `game_app`, and `game_readonly`; protecting purchase tables from deletion; and using views.
4
Data Backup, Recovery, and Migration
A logical backup using `pg_dump -Fc`, selective restoration, and a test into an empty database using `pg_restore`, as well as bulk export and import of CSV files using the `\copy` client.