---
owner: gmikcon
status: active
last_reviewed: 2026-11-20
review_interval: 90d
confidence: high
source_of_truth: repository
---

# PostgreSQL operations

Backups, restore drills, health checks and migrations for the database-backed
Gnexus Book.

## Backups

- Unit: `systemd/gnexus-book-backup.service`, scheduled daily by
  `gnexus-book-backup.timer` (`Persistent=true`, randomized delay up to 15 minutes).
- Script: `scripts/backup-db.sh`.
  - custom-format `pg_dump` to `GNEXUS_BOOK_BACKUP_DIR` (off-host by default);
  - `pg_restore --list` sanity check and SHA-256 checksum;
  - keeps `GNEXUS_BOOK_BACKUP_KEEP` dumps (default 14);
  - refuses to run if the off-host mount is unavailable and no explicit directory
    is configured;
    - on failure, sends a Synapse alert when `SYNAPSE_URL` and `SYNAPSE_API_KEY` are
    configured.
- Manual run:

  ```bash
  set -a; . server/.env; set +a
  ./scripts/backup-db.sh
  ```

A backup that has never been restored does not count. Run the restore drill below.

The backup timer was forced on 2026-11-20 and produced
`/mnt/NetTera8/service/backups/gnexus-book/db/target-gnbook/gnexus_book_2026-11-20_151745.dump`
with a valid `pg_restore --list` and SHA-256 file. The daily timer next runs at
00:00 UTC.

## Restore drill

```bash
set -a; . server/.env; set +a
./scripts/restore-drill.sh                 # latest dump
./scripts/restore-drill.sh /path/to.dump   # specific dump
```

The script creates a temporary database, restores the dump, runs database validation
and count checks, prints the result and drops the temporary database. It requires a
database role with `CREATEDB`.

`gnexus-book-restore-drill.timer` is enabled and runs monthly. A manual drill against
the 2026-11-20 dump passed: 76 documents, 120 inventory items, validation `ok`. The
monthly timer uses the same script and alerts through Synapse on failure.

## Health and monitoring

- `GET /health` is public, cheap and returns `status`, `service`, `version`, `storage`,
  `timestamp` and `checks.db`. It exposes no users, secrets or topology.
- `gnexus-book-healthcheck.timer` runs the local probe every five minutes.
- Monitor URL for hard-panel: `GNEXUS_BOOK_HEALTH_URL` (default
  `http://127.0.0.1:8000/health`).
- Local probe:

  ```bash
  ./scripts/healthcheck.sh
  ```

  It fails on a non-`ok` status or a failed `checks.db`, and sends a Synapse alert when
  configured. It also checks `GNEXUS_BOOK_DISK_PATH`:
  - warning at `GNEXUS_BOOK_DISK_WARN_PERCENT` (default 90%);
  - failure at `GNEXUS_BOOK_DISK_MAX_PERCENT` (default 98%);
  - NetTera8 is currently about 97% used, so the probe logs a warning.
- Register the probe in hard-panel and create the alert route in Synapse. The book
  service does not own notification channels; Synapse routing decides recipients.
- `SYNAPSE_URL`/`SYNAPSE_API_KEY` are not configured on gnbook yet, so failure paths
  currently log without delivering an alert.

## Data ownership

Current rows are assigned to the bootstrap administrator. After the real owner signs in
through production gnexus-auth, record the real `sub` and transfer ownership:

```bash
set -a; . server/.env; set +a
cd server
.venv/bin/python -m app.storage.cli assign-owner --user-json "$HOME/.config/gnexus-book/owner.json"
.venv/bin/python -m app.storage.cli ownership
```

Keep `owner.json` outside the repository (`~/.config/gnexus-book/owner.json`,
`chmod 600`). The owner JSON contains `sub`, `email`, `system_role`, `status` and
`profile` (display name, locale, timezone, avatar URL). The command creates or updates
the local user mirror, assigns only rows with no owner by default, and writes an
`ownership.assigned` audit record. `--all` transfers every document and inventory item
to the new owner.

Ownership is separate from historical authorship: imported versions and the import
changeset retain their `system`/`importer` authorship, while `owner_user_id` records
who the data belongs to.

## Migrations and deploys

`scripts/deploy.sh` sources `server/.env`; when
`GNEXUS_BOOK_STORAGE_BACKEND=db`, it runs `alembic upgrade head` before restarting the
backend and MCP services. It also refreshes the systemd user units.

The main checkout can `git pull` from origin over HTTPS. The sibling
`~/gnexus-ui-kit` checkout is private; when it is bundle-managed and has no GitBucket
credentials, run `GNEXUS_BOOK_SKIP_KIT_PULL=1 ./scripts/deploy.sh` and keep the local
kit build current through the bundle path.

## Secrets

- Runtime values live in `server/.env` (`chmod 600`, gitignored) and in gnexus-creds.
- The Synapse source key (`SYNAPSE_API_KEY`) is a `syn_*` key, never a user key.
- Never commit database URLs, OAuth client secrets, webhook secrets or API keys.

## Quick checks

```bash
systemctl --user status gnexus-book-backend.service gnexus-book-mcp.service
systemctl --user list-timers 'gnexus-book-*'
./scripts/healthcheck.sh
set -a; . server/.env; set +a
psql "${GNEXUS_BOOK_DATABASE_URL/postgresql+psycopg/postgresql}" -c \
  "select count(*) from documents;"
```
