# 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

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

```toml
# <data>/hub.toml
[database]
url = "postgres://hub@10.20.0.3/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:

```sh
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

```sh
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:test@127.0.0.1:55432/hub OXYHUB_DATABASE_SCHEMA='*' \
  cargo test -p oxygenhub --features postgres
```

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