Newer
Older
gnexus-book / 30-runbooks / postgres-operations.md
@Eugene Sukhodolskiy Eugene Sukhodolskiy 7 hours ago 3 KB docs: document data ownership rollout

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

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:

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

Data ownership

Imported documents and inventory can be assigned to the first admin user:

set -a; . server/.env; set +a
cd server
.venv/bin/python -m app.storage.cli assign-owner --user-json /path/to/owner.json
.venv/bin/python -m app.storage.cli ownership

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.

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

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;"