---
owner: gmikcon
status: active
last_reviewed: 2026-11-19
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.

## 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 installed but not enabled by default. Enable it
after the first successful manual drill:

```bash
systemctl --user enable --now gnexus-book-restore-drill.timer
```

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.
- 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.
- 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.

## 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.

## 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
psql -h 127.0.0.1 -p 5433 -U gmikcon -d gnexus_book -c \
  "select count(*) from documents;"
```
