thermograph/infra/deploy/POSTGRES-MIGRATION.md

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.