Newer
Older
gnexus-book / 30-runbooks / postgres-operations.md

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

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

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

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