Operations and Deployment

This page describes how to run, deploy and operate the SIM Database. All commands are wrapped in the Makefile so they work the same way on Windows cmd.exe, Git Bash and POSIX shells.

Prerequisites

• Docker with the Compose plugin (docker compose version)
• GNU Make (Git Bash / MSYS2 / Chocolatey on Windows, Xcode CLT on macOS, apt install make on Debian)
• Node + pnpm (only for seed password hashing)

No local Java, PostgreSQL or Flyway installation is required.

Local environment setup

The first time you clone the repository:

cd sim-database
make setup      # copies .env.example to .env if missing

Fill in the values in .env:

Variable	Default	Purpose
POSTGRES_DB	sim_db	Database name
POSTGRES_USER	sim	Application role
POSTGRES_PASSWORD	admin	Local password (not for production)
POSTGRES_PORT	5432	Published port on 127.0.0.1
POSTGRES_HOST	localhost	Host used by clients

Local development workflow

Step	Command	What it does
Start database	make up	Builds/starts sim-postgres:local, waits for pg_isready
Stop database	make down	Stops the container but keeps the data volume
Restart	make restart	Restarts the PostgreSQL container
View logs	make logs	Follows container logs
Status	make status	Shows docker compose ps output
Apply migrations	make migrate	Runs Flyway against migrations/
Migration history	make migration-info	Shows flyway info
Validate migrations	make validate	Fails if any applied migration file changed
Load reference seeds	make seed-ref	Idempotent seeds/reference/ load
Load dev seeds	make seed-dev	Loads seeds/dev/ and applies bcrypt hashes
Load both	make seed	seed-ref then seed-dev
Bootstrap admin	make admin	Inserts the bootstrap ADMINISTRATEUR and hashes
Open psql	make db	Interactive shell inside the container
Run tests	make test	Migrates then runs the pgTAP suite in tests/
Backup	make backup	pg_dump -Fc to backups/sim_<ts>.dump
List backups	make backup-list	Lists backups/ contents
Restore	make restore file=sim_<ts>.dump	pg_restore --clean --if-exists (destructive)
Reset from zero	make reset	down -v, fresh up, then migrate (destructive)
Clean everything	make clean	Stops and removes the data volume (destructive)

Typical local session

make up
make migrate
make seed
make test
make db
# ... develop ...
make down

Backup and recovery

Backups are logical pg_dump -Fc custom-format dumps written to backups/ on the host. They are suitable for full rebuilds, not point-in-time recovery (PITR is not configured).

Create a backup

make backup

Restore a backup

make restore file=sim_20250908_120000.dump

Restoring drops the objects in the dump and recreates them. Data created after the backup is lost. Objects created after the backup (for example newer migrations) are left in place, so make migrate may be needed afterward.

Rebuild from migrations and seeds

make clean
make up
make migrate
make seed

This is useful when the schema has drifted or you want a clean state.

VPS deployment (shared Postgres)

The VPS stack uses a shared PostgreSQL instance already present on the host. It is intended for the dev/demo environment.

• Configuration: .env.vps (from .env.vps.example)
• Network: internal
• Trigger: push to main when migrations/**, seeds/**, config/**, scripts/bootstrap-vps.sh or .env.vps.example change, or workflow_dispatch
• Runner: vps-runner
• Workflow: .github/workflows/deploy.yml

The workflow:

1. Restores .env.vps from the ENV_VPS_B64 GitHub secret.
2. Installs Node/pnpm and dependencies.
3. Runs scripts/bootstrap-vps.sh:
   • Provisions the sim role and sim_db database idempotently.
   • Runs Flyway migrate with FLYWAY_OUT_OF_ORDER=true.
   • Loads seeds/reference, seeds/seed_admin_user.sql, seeds/dev, and seeds/generated/10_auth_passwords.sql.

Because this is a development environment, dev seeds and demo passwords are acceptable.

Production deployment (dedicated Postgres)

The production stack is a dedicated container reachable by the backend at postgres-prod:5432 on prod-network.

• Configuration: .env.prod (from .env.prod.example)
• Trigger: workflow_dispatch only, with a typed confirm and required reviewer on the production environment
• Runner: gsg-prod
• Workflow: .github/workflows/deploy-prod.yml

The workflow:

1. make prod-up — builds/starts sim-postgres-prod.
2. make prod-migrate — applies Flyway migrations.
3. make prod-seed — loads seeds/reference/, the bootstrap admin, and a bcrypt hash generated in memory from ADMIN_LOGIN/ADMIN_PASSWORD in .env.prod.
4. make prod-admin-ui-up — starts/updates pgAdmin behind Traefik (optional).

prod-seed never loads seeds/dev/. The production admin password is hashed by scripts/hash-prod-admin.mjs and piped to psql; it is never logged or committed.

Production commands

make prod-up
make prod-migrate
make prod-seed
make prod-db
make prod-backup
make prod-admin-ui-up

pgAdmin (production)

The pgadmin profile in docker-compose.prod.yml exposes pgAdmin behind Traefik at https://${PGADMIN_HOST}. It is not started by prod-up; start it explicitly with:

make prod-admin-ui-up

The pre-registered server in docker/pgadmin/servers.json connects to postgres-prod:5432 / sim_db.

CI/CD pipelines

Workflow	Trigger	Runner	Purpose
ci.yml	push: main, pull_request	ubuntu-latest	Starts the local compose stack, migrates and runs make test
deploy.yml	push: main on DB paths or workflow_dispatch	vps-runner	Bootstrap/migrate/seeds the VPS shared Postgres
deploy-prod.yml	workflow_dispatch with manual approval	gsg-prod	Deploy the dedicated production Postgres

Both deploy workflows decode the whole .env file from a base64 secret and chmod 600 it. The production workflow removes .env.prod in an always() step.

Things deliberately out of scope

• WAL/PITR backup (only wal_level=replica is preconfigured)
• Native monitoring stack
• Multi-master or read replicas

These are left for the wider sim-backend/hosting milestone.

Command walk-through

Starting from scratch

$ cd sim-database
$ make setup
Created .env from .env.example

$ make up
[+] Running 2/2
 ✔ Container sim-database-postgres-1  Started

$ make migrate
Flyway ...
Successfully applied 73 migrations.

$ make seed
-> reference seeds loaded
-> dev seeds loaded
-> bcrypt hashes applied

$ make test
000_smoke.sql ........................ PASS
010_regles_transverses.sql ........... PASS
020_regles_residence.sql ............. PASS
030_regles_market.sql ................ PASS
040_regles_pressing_restaurant_fete.sql  PASS
050_regles_rh.sql .................... PASS
060_regles_dynamiques_residence.sql .. PASS
070_regles_dynamiques_market.sql ..... PASS
080_regles_dynamiques_finances.sql ... PASS
090_regles_dynamiques_rh.sql ......... PASS
100_audit.sql ........................ PASS
110_auth.sql ......................... PASS
120_resident_portal.sql .............. PASS

Adding a new migration and testing it

# 1. Create the new migration file
$ touch migrations/residence/074_residence_demande_maintenance.sql

# 2. Write the DDL, save, then migrate
$ make migrate
Flyway ...
Current version: 73
+---------+ ...
| 074 ... | ...
+---------+ ...

# 3. Run tests to make sure nothing is broken
$ make test

# 4. Open psql to verify
$ make db
sim=# \dt residence.*

What not to do operationally

Do not	Why	Instead
Edit migrations/common/001_bootstrap_schemas.sql	It has already been applied	Add a new migration
Run make seed-dev on the production compose stack	prod-seed is the only production-safe seed command	make prod-seed
Run make restore on a live production database during business hours	It drops objects and restores the dump; downtime	Schedule a maintenance window
Commit .env or .env.prod	Secrets in Git are a security risk	Use .env.example templates and GitHub secrets
Run make clean expecting to keep data	It removes the Docker volume	Use make down to stop, make backup before make clean