Newer
Older
gnexus-book / 30-runbooks / postgres-operations.md
@Eugene Sukhodolskiy Eugene Sukhodolskiy 2 days ago 2 KB docs: add operations and decommission runbooks

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.

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