120 lines
6.9 KiB
Markdown
120 lines
6.9 KiB
Markdown
# Cutover: SQLite → PostgreSQL 18 (containerized stack)
|
|
|
|
The app now runs as a docker-compose stack (`app` + `db`) and standardizes on
|
|
PostgreSQL. Locally you need nothing but Docker; `make up` builds and starts both.
|
|
This doc is the **one-time production cutover** from the old bare-systemd/SQLite
|
|
deploy to the compose stack, migrating the authoritative accounts data.
|
|
|
|
## What moved
|
|
|
|
- **accounts** (users/subscriptions/notifications/…): SQLite → Postgres **durable**
|
|
tables. Managed by Alembic (`backend/alembic/`); `create_all` on a fresh DB.
|
|
- **derived cache** (`store.py`) and **metrics** (`metrics.py`): now Postgres
|
|
**UNLOGGED** tables (fast, non-durable — same throwaway semantics). No data to
|
|
migrate — the cache rebuilds, metrics start empty.
|
|
- **climate record** (`climate.py`): the raw daily archive + recent/forecast bundle
|
|
moved from per-cell **parquet** files into **TimescaleDB hypertables**
|
|
(`climate_history` / `climate_recent` / `climate_sync`, managed by Alembic). The
|
|
app reads/writes them via `data/climate_store.py`. Backfilled from the existing
|
|
parquet cache with `migrate_cache_to_pg.py` (see below).
|
|
- The app selects Postgres when `THERMOGRAPH_DATABASE_URL` is a `postgresql+asyncpg`
|
|
URL; unset ⇒ the old SQLite/parquet behavior (this is how the **test suite** stays
|
|
Postgres-free — no Postgres needed in CI, and climate falls back to parquet).
|
|
- `db` runs the stock **`timescale/timescaledb:latest-pg18`** image (genuine PG18 +
|
|
TimescaleDB). The earlier pg_duckdb / `read_parquet('/parquet/…')` capability is
|
|
gone — the climate record is real tables now, queryable directly (see
|
|
`deploy/db/README.md`).
|
|
|
|
## Connection model
|
|
|
|
Per worker (4 workers): a read-write asyncpg engine + a read-only asyncpg engine
|
|
(`default_transaction_read_only=on`, used by the pure-GET endpoints), plus one sync
|
|
psycopg engine for the notifier thread. Single Postgres primary — the RO engine is
|
|
a guardrail, not a replica.
|
|
|
|
## Prod cutover steps (maintenance window)
|
|
|
|
Prereq: the deploy user can run `docker`. Secrets in `/etc/thermograph.env` must
|
|
include `POSTGRES_PASSWORD`, and pinned `THERMOGRAPH_AUTH_SECRET`,
|
|
`THERMOGRAPH_VAPID_PRIVATE_KEY`/`_PUBLIC_KEY`, `THERMOGRAPH_COOKIE_SECURE=1`.
|
|
|
|
1. **Ship the stack, don't cut over yet.** Deploy the branch; `docker compose build`.
|
|
Bring up only the DB: `docker compose up -d db`. Add a nightly `pg_dump` backup.
|
|
2. **Freeze + back up.** Stop the old app (hard stop — no writes). Back up the live
|
|
`data/accounts.sqlite` + `-wal`/`-shm` (this is the rollback artifact).
|
|
3. **Build the schema.** `docker compose run --rm backend alembic upgrade head`
|
|
(or let the `app` entrypoint do it on first start — but do the data copy before
|
|
real traffic).
|
|
4. **Copy the accounts data:**
|
|
```
|
|
docker compose run --rm \
|
|
-e THERMOGRAPH_DATABASE_URL="postgresql+asyncpg://thermograph:$POSTGRES_PASSWORD@db:5432/thermograph" \
|
|
-v /opt/thermograph/data/accounts.sqlite:/src.sqlite:ro \
|
|
backend python migrate_accounts_to_pg.py --sqlite /src.sqlite
|
|
```
|
|
It copies user → subscription/push_subscription/pending_digest → notification,
|
|
**preserving PKs**, **skips access_token** (everyone re-logins once), and
|
|
**resets the integer-PK sequences** (skipping that = a runtime PK collision).
|
|
Verify the printed per-table counts against the old DB.
|
|
5. **Start serving:** `docker compose up -d` (Caddy already proxies `127.0.0.1:8137`).
|
|
6. **Smoke test:** login, list/create/delete a subscription, one notifier tick, a
|
|
cached calendar/day request (derived store on PG), the metrics dashboard.
|
|
7. Keep the frozen `accounts.sqlite` as rollback for a few days; keep PG backed up.
|
|
Enable pg_duckdb on the (already-initialized) volume once, by hand:
|
|
`docker compose exec db psql -U thermograph -d thermograph -c 'CREATE EXTENSION IF NOT EXISTS pg_duckdb;'`
|
|
|
|
## Rollback
|
|
|
|
Redeploy the previous SQLite release (or point `THERMOGRAPH_DATABASE_URL` back to
|
|
unset/SQLite and restart). **Clean only during/immediately after the window** — once
|
|
real users write to Postgres, those writes are lost on rollback (the copy is
|
|
one-way), so keep the window short and writes frozen during the copy.
|
|
|
|
## TimescaleDB cutover (from the earlier pg_duckdb PG18 stack)
|
|
|
|
Swapping the `db` image from `pgduckdb/pgduckdb:18` to
|
|
`timescale/timescaledb:latest-pg18` means a **fresh PGDATA volume** — and that
|
|
volume also holds the durable **accounts** data. So this cutover is not a plain
|
|
`docker compose pull`; it re-seeds both accounts and the climate record.
|
|
|
|
1. **Back up first.** `pg_dump` the accounts tables from the *old* db container
|
|
(`docker compose exec db pg_dump -U thermograph -t user -t subscription \
|
|
-t notification -t push_subscription -t pending_digest thermograph > accounts.sql`),
|
|
or keep the pre-Postgres `accounts.sqlite` as the source of truth.
|
|
2. **Replace the DB.** Deploy this branch (compose now points at the stock
|
|
TimescaleDB image, no `build:`). Bring down the stack and remove the old volume
|
|
so PGDATA re-inits on the new image: `docker compose down && docker volume rm
|
|
<project>_pgdata`.
|
|
3. **Build the schema.** `docker compose up -d db`, then
|
|
`docker compose run --rm backend alembic upgrade head` — this creates the accounts
|
|
tables *and* the climate hypertables, and `CREATE EXTENSION timescaledb`.
|
|
4. **Restore accounts.** Either `psql < accounts.sql`, or re-run
|
|
`migrate_accounts_to_pg.py --sqlite <accounts.sqlite>` (§ the SQLite cutover
|
|
above). Verify per-table counts.
|
|
5. **Backfill the climate record** from the parquet cache (mount it and run the
|
|
backfill; idempotent, resumable):
|
|
```
|
|
docker compose run --rm \
|
|
-v /opt/thermograph/data/cache:/app/data/cache:ro \
|
|
backend python migrate_cache_to_pg.py
|
|
```
|
|
It loads `climate_history` + `climate_recent` and sets each cell's
|
|
`climate_sync` to the parquet **file mtime**, so `recent_stamp` (hence every
|
|
derived-payload token) is unchanged across the cutover. Cells without a
|
|
schema-complete parquet record are skipped and refetch lazily on first request.
|
|
6. **Start serving:** `docker compose up -d`. Smoke test: login, a subscription,
|
|
a cached calendar/day request (served from `climate_history`), the metrics
|
|
dashboard.
|
|
7. Keep the pre-cutover DB backup (accounts dump + parquet cache) for a few days.
|
|
The climate record is now **durable** — make sure `pg_dump`/PITR covers it.
|
|
|
|
## Notes
|
|
|
|
- The two `deploy/migrations/*.sql` files are superseded by Alembic on Postgres
|
|
(they were SQLite-specific `ALTER TABLE` column-adds for the old prod DB).
|
|
- The TimescaleDB image's data dir is `/var/lib/postgresql/data`; the compose
|
|
volume is mounted at the parent `/var/lib/postgresql` so it persists without the
|
|
initdb permission issue that pinning `PGDATA` to the mountpoint can trigger.
|
|
- Climate falls back to the parquet cache whenever `THERMOGRAPH_DATABASE_URL` is
|
|
not a Postgres URL (dev, tests, offline tooling) — the same dialect switch as the
|
|
accounts DB, so a DB outage in prod degrades to upstream refetch, never a 500.
|