OXYGENHUB
◆ v0.9.16 oxygenhub / docs / postgres.md
3.2 KiB · Markdown · last changed Postgres, step 4: admin copy-db moves a SQLite hub's records into Postgres 19 days ago renderedsource blame history raw

OxygenHub on PostgreSQL

A hub keeps its records in SQLite (<data>/hub.db) unless it is built with the postgres feature, when it keeps them in PostgreSQL 16 or later (oxygenhub#211). The engine is chosen when the hub is built, not when it starts: one binary speaks one engine.

Build and configure

cargo build --release -p oxygenhub --features postgres

# <data>/hub.toml
[database]
url = "postgres://[email protected]/hub"   # the password from PGPASSWORD or the URL
schema = ""                             # empty: public

OXYHUB_DATABASE_URL and OXYHUB_DATABASE_SCHEMA override the file. The hub makes the citext extension (in public) and its schema if they are missing, then applies crates/hub/migrations-pg. Repositories, keys and uploads stay in the data directory as before.

schema = "*" gives each data directory a schema of its own (hub_<dir>), so many hubs can share one database. The tests use it.

Two schemas, one set of queries

The queries are the same on both engines: $n placeholders, ON CONFLICT, TRUE/FALSE, RETURNING, CASE for counting flags, window functions, lower() for comparing names. Postgres is stricter than SQLite, so:

  • A flag column is BOOLEAN on Postgres and INTEGER on SQLite. Bind and read it as bool, and compare it with TRUE/FALSE, never 1/0.
  • SUM of a BIGINT is NUMERIC on Postgres: CAST(SUM(…) AS BIGINT).
  • Every column a GROUP BY query returns is grouped or aggregated.
  • HAVING and WHERE cannot name a SELECT alias: repeat the expression or select from a subquery.

The few places that differ choose by cfg(feature = "postgres"): connecting and migrating (db::open), the write lock (db::begin_write: BEGIN IMMEDIATE, or an advisory lock), full-text search (FTS5, or a tsvector with ts_headline), JSON (db::json_text, json_each), and the catalog backups read (backups::catalog). A backup taken on either engine restores into either: flags are written as 0 or 1, blobs as hex.

Changing the schema

migrations-pg/0001_schema.sql is SQLite's 0001–0072 as one. Each later change is two files with the same number, one in migrations (SQLite) and one in migrations-pg. tests/schema.rs fails when the two disagree about a table or column.

Moving a SQLite hub to Postgres

On the new hub (built with postgres, [database] set, never yet served), with the old hub stopped and its hub.db copied across:

oxygenhub --data /srv/hub admin copy-db --from /srv/old/hub.db

Both hubs must be the same version: the copy refuses a hub.db at another migration, and a database that already has rows. It copies every table in one transaction with ids kept, moves each id sequence past them, copies the search index, and checks every table's count before committing. Then move the data directory's repos, wikis and keys across as they are.

Testing on Postgres

docker run -d --name oxyhub-pg-test -e POSTGRES_USER=hub -e POSTGRES_PASSWORD=test \
  -p 127.0.0.1:55432:5432 postgres:16-alpine
OXYHUB_DATABASE_URL=postgres://hub:[email protected]:55432/hub OXYHUB_DATABASE_SCHEMA='*' \
  cargo test -p oxygenhub --features postgres

Each test makes its own schema; drop the hub_% schemas between runs.