PostgreSQL Database Runbook

This runbook is the operator reference for PostgreSQL health diagnostics, migrations, and incident handling across Stella Ops deployments. It is written for Platform on-call and the Database team, and every command, metric, and check below is grounded in source so it can be trusted in an incident.

Status: Reconciled against source — CLI surface in src/Cli/StellaOps.Cli, migration host in src/__Libraries/StellaOps.Infrastructure.Postgres, and Doctor Postgres checks in src/Doctor/__Plugins/StellaOps.Doctor.Plugin.Postgres.

Reconciliation note (doc ↔ code): an earlier revision of this runbook documented a large stella db / stella backup / stella service / stella network / stella secrets rotate CLI surface and a set of stella_postgres_* metrics + StellaPostgres* Prometheus alerts. None of those commands, metrics, or alerts exist in the codebase. They have been replaced below with the commands and telemetry that are actually implemented, and the aspirational items are collected — clearly marked — under “Roadmap / not yet implemented” so the operator intent is not lost.

Scope

PostgreSQL database operations including health diagnostics, migrations, and common incident handling for Stella Ops deployments.

Persistence model invariant (see repo CLAUDE.md §2.7 and ADR-004): every service that owns a PostgreSQL schema auto-migrates its embedded SQL on startup via AddStartupMigrations(...) (StellaOps.Infrastructure.Postgres.Migrations.StartupMigrationHost). Startup (idempotent schema) migrations run automatically and block startup if pending release/data migrations exist; seed migrations remain operator-invoked. There is no manual init step in steady state — postgres-init/ scripts are first-run bootstrap fallbacks only.

Pre-flight Checklist

Required scopes

The diagnostic commands below are gated by Authority Doctor scopes (src/Authority/StellaOps.Authority/StellaOps.Auth.Abstractions/StellaOpsScopes.cs):

There is no dedicated database or backup scope in the catalog.

Environment Verification

# Connectivity + schema + pool, in one check (PostgreSQL Doctor plugin)
stella doctor --check check.postgres.connectivity
stella doctor --check check.postgres.pool

# Migration status for every module (uses STELLAOPS_POSTGRES_* env vars)
stella system migrations-status --module all

The Doctor --check option takes a single check ID. Run it once per check (or use --category database to run all database checks together) — comma-separated lists are not parsed.

Metrics to Watch

The stella-ops-performance Grafana dashboard (devops/telemetry/dashboards/stella-ops-performance.json) charts connection-pool / query telemetry under the stella_db_* prefix:

Verify before relying on these panels. As of this writing the stella_db_* names appear only in the dashboard JSON — no Stella service in src/ registers a meter that emits them (the only DB-connection gauge actually emitted in source is ledger_db_connections_active, scoped to Findings.Ledger). Treat the four stella_db_* series as the dashboard’s intended contract: the panels will read empty unless an exporter publishing those names is wired into your deployment. Confirm with curl <service>/metrics | grep stella_db_ against a running service before treating an empty panel as an incident.

The earlier stella_postgres_connections_active, stella_postgres_query_duration_seconds, and stella_postgres_pool_waiting metric names are not referenced anywhere in the codebase (not even in the dashboard). There is no exported gauge for “connections waiting for the pool”.

Standard Procedures

SP-001: Daily Health Check

Frequency: Daily or on-demand Duration: ~5 minutes

  1. Run all database-category diagnostic checks and capture the report:

    stella doctor --category database --format json --output /tmp/db-health-$(date +%Y%m%d).json

    (--output writes the formatted report to a file; the formatter honours text|json|markdown.)

  2. Confirm migrations are converged (no pending release/data migrations, no checksum drift):

    stella system migrations-status --module all --verbose

  3. Review platform dependency readiness (includes PostgreSQL among required dependencies):

    stella admin diagnostics health --detail

Replication status, slow-query reports, and backup status do not have CLI commands today. See “Roadmap / not yet implemented” below.

SP-002: Connection Pool Tuning

When: Pool saturation observed in stella_db_connections_active approaching stella_db_connections_max.

  1. Inspect pool state via the Doctor pool check:

    stella doctor --check check.postgres.pool --verbose

  2. Watch pool/connection metrics on the stella-ops-performance Grafana dashboard (Connection pool utilization panel: stella_db_connections_active / _idle / _max).

  3. Adjust pool size by changing the owning service’s PostgreSQL options (StellaOps.Infrastructure.Postgres.Options.PostgresOptions) through the service’s configuration/environment, then redeploy/restart the service through your normal compose/orchestration tooling.

There is no stella config get/set Database:MaxPoolSize, no stella db pool ..., and no stella service restart command. stella config exposes show/list plus the notify/integrations/feeds/registry/sources/signals/identity-providers subgroups — it does not set arbitrary key/value config.

SP-003: Backup and Restore

Backup/restore is not driven by a Stella CLI command today (no stella backup command group exists). The shipped backup/restore mechanism is a set of DevOps shell scripts plus the product-native stellaops-backup-crypto encryptor — see the companion Backup and Restore Operations runbook for the canonical procedure. Recovery from a bad migration is by PostgreSQL snapshot restore — see the migration-recovery runbook — consistent with the forward-only migration policy (ADR-004).

Operationally, take and restore backups with native PostgreSQL tooling (pg_dump / pg_basebackup / volume snapshots) against the database your deployment uses. After a restore, re-verify schema state:

stella system migrations-status --module all
stella system migrations-verify --module all

SP-004: Migration Execution

Startup migrations apply automatically when services boot. To drive migrations manually (for example during a controlled upgrade), use the stella system migration commands. These read the connection from STELLAOPS_POSTGRES_* environment variables unless --connection is supplied.

  1. Preview pending migrations (dry run):

    stella system migrations-run --module all --dry-run --verbose

  2. Apply startup migrations:

    stella system migrations-run --module all --category startup

  3. Apply release migrations (gated — requires explicit approval):

    # Release migrations refuse to run without --dry-run or --force:
stella system migrations-run --module <module> --category release --dry-run
stella system migrations-run --module <module> --category release --force

  4. Verify migration success:

    stella system migrations-status --module all
stella system migrations-verify --module all   # checksum verification
stella doctor --check check.postgres.migrations

Module names come from MigrationModuleRegistry; pass --module all for every module or a single module name. stella admin seed-demo --confirm applies demo seed-category data (development/demo only).

Incident Procedures

INC-001: Connection Pool Saturation

Symptoms:

Investigation:

# Pool health check (active/idle/max, with remediation hints)
stella doctor --check check.postgres.pool --verbose

# Confirm the database itself is reachable
stella doctor --check check.postgres.connectivity

Cross-reference the Connection-pool and Query-latency panels in the stella-ops-performance Grafana dashboard.

Resolution:

  1. Identify load source — use server-side PostgreSQL inspection (pg_stat_activity) to find long-running or stuck queries; terminate with pg_terminate_backend(pid) on the database server. (There is no stella db queries / stella db query terminate CLI command.)

  2. Scale the pool — raise the pool ceiling via the owning service’s PostgresOptions configuration/environment, then redeploy the service.

  3. Fix leaks — review application logs for unclosed connections and deploy a fix to the affected service.

INC-002: Slow Query Performance

Symptoms:

Investigation: Use server-side PostgreSQL tooling — pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS) ..., and pg_stat_user_tables for bloat/dead-tuple counts. The CLI does not expose slow-query reports, EXPLAIN, index suggestions, or table statistics.

Resolution:

  1. Index optimization — add/adjust indexes via a forward-only migration in the owning service’s persistence library (embedded SQL; CREATE INDEX IF NOT EXISTS ..., and prefer CONCURRENTLY for large tables). Migrations apply on the next service startup or via stella system migrations-run.
  2. Vacuum / analyze — run VACUUM/ANALYZE on the database server.
  3. Query optimization — review and rewrite the offending query.

INC-003: Database Connectivity Loss

Symptoms:

Investigation:

# Targeted connectivity check (reports reason code + remediation)
stella doctor --check check.postgres.connectivity --verbose

# Platform dependency readiness (PostgreSQL appears as a required dependency)
stella admin diagnostics health --detail

For DNS/firewall/network reachability, use standard tooling (Test-NetConnection, nc, psql) against the database host/port — there is no stella network command group.

Resolution:

  1. Network issue — verify firewall rules, host routing, and DNS resolution of the database host.
  2. Database server issue — check the PostgreSQL service status, server logs, and disk space.
  3. Credential issue — verify the STELLAOPS_POSTGRES_* connection settings the services use. (There is no stella db verify-credentials or stella secrets rotate command; stella secrets manages secret-detection rule bundles, not connection secrets.)

INC-004: Disk Space Pressure

Symptoms:

Investigation: Inspect on the database server with native tooling: pg_database_size(), the pg_total_relation_size() of the largest tables, and pg_stat_user_tables dead-tuple counts. The CLI does not expose disk-usage, table-size, or bloat reports.

Resolution:

  1. Immediate relief — VACUUM (and VACUUM FULL on a maintenance window) to reclaim space on the server.
  2. Retention — apply any module-level data-retention/pruning that the owning service supports through its own configuration (there is no generic stella db prune / stella db archive).
  3. Expand storage — grow the underlying volume per your deployment’s storage procedure and resize the filesystem.

Diagnostics & Evidence Capture

Generate a support bundle (Doctor report + optional logs + config snapshot):

stella doctor export --output /tmp/db-diag-$(date +%Y%m%dT%H%M%S).zip
# Optional: --include-logs (default true), --log-duration 4h, --no-config

Apply non-destructive fixes suggested by a Doctor report (dry-run by default; only commands whose first token is stella/./stella/stella.exe and that contain no {PLACEHOLDER} tokens are classed “safe” and executed when --apply is passed — see IsStellaCommand/ContainsPlaceholders in DoctorCommandGroup):

stella doctor fix --from /tmp/db-diag-report.json          # preview
stella doctor fix --from /tmp/db-diag-report.json --apply  # execute safe fixes

Caveat for the PostgreSQL checks: PostgresConnectionPoolCheck emits remediation steps that call stella db queries …, stella db pool stats …, stella db config set …, and stella db pool reset …. Those stella db sub-commands are not implemented (see “Roadmap” — the registered stella db only triggers Concelier jobs). They have no placeholders, so doctor fix --apply will classify them “safe” and attempt them — each will fail with an unknown-command error rather than fix anything. Until that command tree ships, treat the pool check’s remediation block as advisory text, not executable fixes.

List the database checks available in this build:

stella doctor list --category database --verbose

Monitoring Dashboard

Grafana dashboard: devops/telemetry/dashboards/stella-ops-performance.json (“Stella Ops Performance”).

Relevant panels:

These panels query the stella_db_* series, which are not emitted by any service in src/ today (see “Metrics to Watch” above). Until an exporter publishing those metric names is wired into the deployment, the panels render empty — this is a telemetry gap, not a database fault.

There is also no dedicated PostgreSQL alert ruleset under devops/telemetry/alerts/. The StellaPostgresPoolExhausted / StellaPostgresQueryLatencyHigh / StellaPostgresConnectionFailed / StellaPostgresDiskSpaceWarning alerts referenced in earlier revisions of this runbook are not implemented as Prometheus rules. See “Roadmap” below.

Roadmap / not yet implemented

The following operator conveniences are commonly requested but do not exist in the codebase today. They are recorded here so the intent is preserved; do not reference them in procedures as if they ship:

Escalation Path

  1. L1 (On-call): Run Doctor checks, generate a support bundle, restart services via the deployment’s orchestration tooling.
  2. L2 (Database team): Query optimization, migration authoring, schema changes.
  3. L3 (Platform/infra support): Storage/host/network issues.

Source-reconciled. Verify the live CLI surface with stella --help, stella doctor list, and stella system --help before relying on any command above.