Run the hub on PostgreSQL as well as SQLite issue #211 closed: completed andymccall opened this · 2 comments
referenced by 6 changes on 16 Oct 2026
5d90e0e045e2 d1bf2d698777 ea14c4ec5c3f 97b18869acf5 3b1bc27fe805 4e2cad00a484
andymccall
Progress, 2026-10-16:
- c3e376df, 1b75b9af, 9d26b9d8, e91e8881: the shared SQL made portable ($n placeholders, ON CONFLICT, TRUE/FALSE, CASE counts, window functions for inbox threads, RETURNING, lower()).
- 28ccfc00: a
postgresfeature.[database] url, migrations-pg/0001_schema.sql (SQLite's 0001-0072 as one: BOOLEAN flags, CITEXT names, identity ids, tsvector search), per-engine connect/migrate, write lock, search, JSON and backup catalog. All 247 tests pass on each engine; tests/schema.rs fails when the two schemas disagree. - 98a1956a:
oxygenhub admin copy-db --from hub.dbcopies a stopped SQLite hub into an empty Postgres database in one transaction, ids kept, counts checked.
docs/postgres.md has how to build, configure, change the schema, move a hub and test.
Left: the Postgres tests in Flow (the rust image needs a PostgreSQL server), and a rehearsal of copy-db on a copy of oxygenhub.io's hub.db before the cutover.
referenced by 3 changes on 16 Oct 2026
andymccall
Board tidy (2026-10-19): done. The hub runs on PostgreSQL or SQLite (--features postgres, [database] url, migrations written twice since 0001), every test runs on both engines in CI (test-postgres), and copy-db moves a SQLite hub's data to Postgres. Running oxygenhub.io itself on Cloud SQL is the GCP move (oxygenhub-gcp issue #5, issue #6, issue #9). Closing.
andymccall closed ·
For the move to GCP (oxygenhub-gcp, decision 0003): Cloud SQL for PostgreSQL in production, SQLite kept for Earth and small installations. sqlx for PostgreSQL beside SQLite, a migration set for each, the SQL that differs (RETURNING, upserts, json, date maths) behind one layer, the test suite on both in Flow, and a one-off copy tool from SQLite to PostgreSQL for moving oxygenhub.io.