# 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://[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:

```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:[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.