| 1 |
# OxygenHub on PostgreSQL |
| 2 |
|
| 3 |
A hub keeps its records in SQLite (`<data>/hub.db`) unless it is built with |
| 4 |
the `postgres` feature, when it keeps them in PostgreSQL 16 or later |
| 5 |
(oxygenhub#211). The engine is chosen when the hub is built, not when it |
| 6 |
starts: one binary speaks one engine. |
| 7 |
|
| 8 |
## Build and configure |
| 9 |
|
| 10 |
```sh |
| 11 |
cargo build --release -p oxygenhub --features postgres |
| 12 |
``` |
| 13 |
|
| 14 |
```toml |
| 15 |
# <data>/hub.toml |
| 16 |
[database] |
| 17 |
url = "postgres://[email protected]/hub" # the password from PGPASSWORD or the URL |
| 18 |
schema = "" # empty: public |
| 19 |
``` |
| 20 |
|
| 21 |
`OXYHUB_DATABASE_URL` and `OXYHUB_DATABASE_SCHEMA` override the file. The |
| 22 |
hub makes the `citext` extension (in `public`) and its schema if they are |
| 23 |
missing, then applies `crates/hub/migrations-pg`. Repositories, keys and |
| 24 |
uploads stay in the data directory as before. |
| 25 |
|
| 26 |
`schema = "*"` gives each data directory a schema of its own (`hub_<dir>`), |
| 27 |
so many hubs can share one database. The tests use it. |
| 28 |
|
| 29 |
## Two schemas, one set of queries |
| 30 |
|
| 31 |
The queries are the same on both engines: `$n` placeholders, `ON CONFLICT`, |
| 32 |
`TRUE`/`FALSE`, `RETURNING`, `CASE` for counting flags, window functions, |
| 33 |
`lower()` for comparing names. Postgres is stricter than SQLite, so: |
| 34 |
|
| 35 |
- A flag column is `BOOLEAN` on Postgres and `INTEGER` on SQLite. Bind and |
| 36 |
read it as `bool`, and compare it with `TRUE`/`FALSE`, never `1`/`0`. |
| 37 |
- `SUM` of a `BIGINT` is `NUMERIC` on Postgres: `CAST(SUM(…) AS BIGINT)`. |
| 38 |
- Every column a `GROUP BY` query returns is grouped or aggregated. |
| 39 |
- `HAVING` and `WHERE` cannot name a `SELECT` alias: repeat the expression or |
| 40 |
select from a subquery. |
| 41 |
|
| 42 |
The few places that differ choose by `cfg(feature = "postgres")`: |
| 43 |
connecting and migrating (`db::open`), the write lock (`db::begin_write`: |
| 44 |
`BEGIN IMMEDIATE`, or an advisory lock), full-text search (FTS5, or a |
| 45 |
`tsvector` with `ts_headline`), JSON (`db::json_text`, `json_each`), and the |
| 46 |
catalog backups read (`backups::catalog`). A backup taken on either engine |
| 47 |
restores into either: flags are written as 0 or 1, blobs as hex. |
| 48 |
|
| 49 |
## Changing the schema |
| 50 |
|
| 51 |
`migrations-pg/0001_schema.sql` is SQLite's 0001–0072 as one. Each later |
| 52 |
change is two files with the same number, one in `migrations` (SQLite) and |
| 53 |
one in `migrations-pg`. `tests/schema.rs` fails when the two disagree about a |
| 54 |
table or column. |
| 55 |
|
| 56 |
## Moving a SQLite hub to Postgres |
| 57 |
|
| 58 |
On the new hub (built with `postgres`, `[database]` set, never yet served), |
| 59 |
with the old hub stopped and its `hub.db` copied across: |
| 60 |
|
| 61 |
```sh |
| 62 |
oxygenhub --data /srv/hub admin copy-db --from /srv/old/hub.db |
| 63 |
``` |
| 64 |
|
| 65 |
Both hubs must be the same version: the copy refuses a `hub.db` at another |
| 66 |
migration, and a database that already has rows. It copies every table in |
| 67 |
one transaction with ids kept, moves each id sequence past them, copies the |
| 68 |
search index, and checks every table's count before committing. Then move the |
| 69 |
data directory's `repos`, `wikis` and `keys` across as they are. |
| 70 |
|
| 71 |
## Testing on Postgres |
| 72 |
|
| 73 |
```sh |
| 74 |
docker run -d --name oxyhub-pg-test -e POSTGRES_USER=hub -e POSTGRES_PASSWORD=test \ |
| 75 |
-p 127.0.0.1:55432:5432 postgres:16-alpine |
| 76 |
OXYHUB_DATABASE_URL=postgres://hub:[email protected]:55432/hub OXYHUB_DATABASE_SCHEMA='*' \ |
| 77 |
cargo test -p oxygenhub --features postgres |
| 78 |
``` |
| 79 |
|
| 80 |
Each test makes its own schema; drop the `hub_%` schemas between runs. |