mirror of
https://github.com/ipnet-mesh/meshcore-hub.git
synced 2026-10-31 04:02:03 +01:00
Louis King
c643c50378
Remove the upgrade-compatibility items scheduled for v0.20 in the v0.19 release notes: - the `meshcore-hub db migrate-to-postgres` command and its `common/db_migrate.py` module (with its dedicated tests) - the `database_backend` settings field and its legacy-backend validator (a leftover DATABASE_BACKEND is now silently ignored) - the `[postgres]` pip extra alias (Dockerfile installs `.` now) `tests/test_main.py` is rewritten as a minimal CLI smoke test to keep entry-point coverage. Docs updated: v0.20.0 upgrade section notes the removals (SQLite deployments must upgrade through v0.19 first), README and database.md pointers reworded, stale .env.example comment dropped.
75 lines
5.3 KiB
Markdown
75 lines
5.3 KiB
Markdown
# Database
|
|
|
|
MeshCore Hub runs on **PostgreSQL** — the only supported database backend since v0.19. A bundled container ships with the stack for development (it joins the `core` compose profile via `docker-compose.dev.yml`, so a standard dev `compose up` works with zero configuration); managed/external Postgres and multiple schema-isolated instances sharing one cluster are equally supported — and are the norm in production.
|
|
|
|
> [!NOTE]
|
|
> SQLite support (the pre-v0.14 default) was **removed in v0.19**. Deployments still on SQLite must upgrade through v0.19 and run the in-place migration there (the `meshcore-hub db migrate-to-postgres` command was itself removed in v0.20) — see the [v0.19 upgrade guide](upgrading.md).
|
|
|
|
## Docker (bundled container)
|
|
|
|
Postgres ships with the stack. It is a **development convenience**: the base compose file only enables it for the `all` and standalone `postgres` profiles, and `docker-compose.dev.yml` unions it into `core` so the documented dev workflow needs no extra flags:
|
|
|
|
```bash
|
|
# Default dev stack: bundled Postgres starts alongside the app services
|
|
docker compose -f docker-compose.yml -f docker-compose.dev.yml --profile core up -d
|
|
```
|
|
|
|
Production (`docker-compose.prod.yml`) deliberately leaves it out of `core` — the bundled container's `postgres` network alias would shadow a shared instance of the same name on the same network. Start it explicitly with `--profile postgres` (or `--profile all`) if you want the bundled database on a production host.
|
|
|
|
The container derives `POSTGRES_USER` / `POSTGRES_PASSWORD` / `POSTGRES_DB` from `DATABASE_USER` / `DATABASE_PASSWORD` / `DATABASE_NAME`, and ships a **dev-only default password** (`meshcorehub`) so a default `up` initializes out of the box. **Production deployments must override `DATABASE_PASSWORD`** — the container is not published outside the compose network, but the default password is public knowledge (it is in the repo).
|
|
|
|
The `migrate` service waits for Postgres to be healthy and runs `db upgrade` before `collector` and `api` start. That dependency is optional (`required: false`): when the bundled container is not enabled (production with an external database), `migrate` simply runs against `DATABASE_HOST` directly.
|
|
|
|
## Production provisioning (role and database)
|
|
|
|
The bundled container provisions the role and database for you on first start from the `DATABASE_*` values. For a **managed or external** Postgres, create them once before pointing Hub at it. This mirrors the init script used in the [ipnet-mesh/infrastructure](https://github.com/ipnet-mesh/infrastructure/blob/main/etc/postgres/init/02_meshcorehub_db.sh) cluster:
|
|
|
|
```sql
|
|
-- Run once as a superuser/admin role on the target cluster
|
|
CREATE DATABASE meshcorehub;
|
|
CREATE ROLE meshcorehub LOGIN PASSWORD 'your-password';
|
|
GRANT ALL PRIVILEGES ON DATABASE meshcorehub TO meshcorehub;
|
|
```
|
|
|
|
The application **schema** and tables are created automatically by `db upgrade` (run by the `migrate` service on startup); the role just needs `CREATE` privilege on the database. Hub only ever connects as `DATABASE_USER` — no admin or bootstrap credentials are needed at runtime.
|
|
|
|
## Managed or external Postgres
|
|
|
|
To point Hub at an already-running Postgres (e.g. a managed cloud instance or a shared cluster on the same Docker network), set the `DATABASE_*` connection variables. With the production compose override the bundled container is not started at all — nothing extra to skip, and no risk of its `postgres` alias intercepting your `DATABASE_HOST`. (If you use the dev override against an external database, add the standalone `--profile postgres` only if you also want the bundled container running.)
|
|
|
|
```bash
|
|
DATABASE_HOST=your-managed-postgres.example.com
|
|
DATABASE_PORT=5432
|
|
DATABASE_NAME=meshcorehub
|
|
DATABASE_USER=meshcorehub
|
|
DATABASE_PASSWORD=your-password
|
|
```
|
|
|
|
For advanced cases (custom driver, extra query params), set a full SQLAlchemy URL instead — it takes precedence over all the component variables:
|
|
|
|
```bash
|
|
DATABASE_URL=postgresql+psycopg2://meshcorehub:your-password@host:5432/meshcorehub
|
|
```
|
|
|
|
Missing connection configuration (no `DATABASE_URL` and no `DATABASE_*` components) fails fast at startup with an error naming the missing variables — Hub never silently falls back to a default.
|
|
|
|
## Schema-per-instance (`search_path`)
|
|
|
|
Each Hub instance is isolated to its own Postgres **schema** via the connection's `search_path`, rather than its own database. This lets several instances (e.g. `prod`, `stg`) share **one** Postgres cluster without colliding — each gets its own tables and its own `alembic_version`.
|
|
|
|
Give every instance a distinct `DATABASE_SCHEMA`:
|
|
|
|
```bash
|
|
# Production (.env)
|
|
COMPOSE_PROJECT_NAME=hub
|
|
DATABASE_SCHEMA=meshcorehub_prod
|
|
|
|
# Staging (.env, separate directory)
|
|
COMPOSE_PROJECT_NAME=hub-beta
|
|
DATABASE_SCHEMA=meshcorehub_stg
|
|
```
|
|
|
|
The schema is created automatically on `db upgrade` if it does not exist, so no manual `CREATE SCHEMA` is required. Connect both instances to the same `DATABASE_HOST` / `DATABASE_NAME` / `DATABASE_USER`; only `DATABASE_SCHEMA` (and `COMPOSE_PROJECT_NAME`) differ.
|
|
|
|
> **Note:** This is the database-level isolation for instances sharing a Postgres cluster. For running multiple instances on the same Docker host (separate volumes, Traefik routing), see [Multi-Instance Deployments](deployment.md#multi-instance-deployments).
|