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 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 is per service: each service reports its own db.migration-status doctor check (GET /doctor/<service>/checks), or read <schema>.schema_migrations in the owning database. The stella system migrations-status command this section used to list was deleted on 2026-09-14 (SPRINT_20260722_021 PLT-4).

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

  1. Confirm migrations are converged (no checksum drift, no host stopped on a pending migration): read each service’s db.migration-status doctor check (GET /doctor/<service>/checks) and confirm no service container is restarting on a Migration: error (docker compose ps, docker compose logs --since 10m <svc> | grep 'Migration:').

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

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

  2. 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, restart the owning services; each one re-validates its schema_migrations checksums and converges its own schema at startup. Then re-verify schema state per service (db.migration-status doctor check, or the <schema>.schema_migrations table in the owning database).

SP-004: Migration Execution

Migrations are applied only by the owning service at host start (AddStartupMigrations, Startup band 001–099, connection from STELLAOPS_POSTGRES_<SERVICE>_CONNECTION). There is no manual migration applier and no dry-run command.

  1. Deploy the new service image and restart the service:
docker compose up -d <svc>

Expected result: the host applies its pending Startup-band migrations under an advisory lock before serving traffic.

  1. Verify convergence:
docker compose logs --since 5m <svc> | grep 'Migration:'
docker compose exec postgres psql -U stellaops -d <db> \
  -c "select migration_name, category, applied_at from <schema>.schema_migrations order by applied_at desc limit 10;"

Expected result: Migration: Database is up to date for <Module>. and the new rows in the ledger.

  1. Stop condition: the host exits with Pending manual-category migrations block startup or Checksum mismatch. A Release-category (100+ or rollback in the name) or Data-category (DM-prefixed) migration has no runtime applier and must not exist (NoManualCategoryMigrationsTests guards the tree): renumber the file into the Startup band so the owning host applies it, or apply it by hand per the migration-recovery runbook and record the ledger row. Checksum drift follows the same runbook.

Retired (SPRINT_20260722_021 PLT-4, 2026-09-14): the stella system migrations-run | migrations-status | migrations-verify commands and MigrationModuleRegistry this procedure used to be built on were deleted. admin seed-demo was removed earlier (SPRINT_20260722_026 CM-2, 2026-08-18) — it applied startup-category migrations across every module centrally, which is exactly the authority this program retired. Demo/QA data is stella qa-seed, never a migration (§2.11).

SP-005: Role session guards (idle-in-transaction) and the data-move window exemption

When: after provisioning a new service role, and once per estate to cover roles provisioned before this guard existed. Also read this before running any data-move window, because the exemption below is what keeps pg_dump | psql copies alive.

What it bounds, and what it deliberately does not. Nothing else in the estate ends a session that opened a transaction, took row locks, and then went idle:

So an incumbent idle inside a fenced transaction blocks every successor with no upper bound. The EST-6 ruling (2026-09-04, SPRINT_20260722_002) bounds this at role scope.

  1. New roles are bounded automatically — provision-service-database.sh sets it as step 5.

  2. Re-apply to roles that predate that step (idempotent, no restart, no window; --dry-run prints the statements without running them):

    bash tools/scripts/deploy/postgres/apply-role-session-guards.sh --dry-run
bash tools/scripts/deploy/postgres/apply-role-session-guards.sh

  3. Verify. This query returned zero rows for all 20 roles before EST-6, so a non-empty result is the forcing function:

    docker exec -i stellaops-postgres psql -U stellaops -d postgres -c \
  "SELECT rolname, rolconfig FROM pg_roles WHERE rolconfig IS NOT NULL ORDER BY rolname;"

    Expect one row per service role, and not stellaops.

  4. Reverse with apply-role-session-guards.sh --reset (one ALTER ROLE … RESET per role).

Check the role count. The list is derived from STELLAOPS_POSTGRES_<SVC>_CONNECTION in the estate .env — the same population probe-database-isolation.sh walks, and it carries the same under-reporting trap: a family whose connection is assembled in a compose file rather than .env is silently absent and the run still exits 0. The vulnerability hub is exactly that case. The tracked template yields 18 service roles; append vuln from its live container before running against the estate (the script header carries the exact docker inspect command).

The exemption is load-bearing. The superuser stellaops is deliberately left unbounded, because it is the role every data-move window runs its pg_dump | psql copies as: pg_dump holds one REPEATABLE READ transaction and sits genuinely idle between table COPYs while the restore side builds indexes, so bounding it would kill the copy mid-pipe. Both scripts refuse to bound it, and DatabaseOwnershipConformanceTests.RoleSessionGuards_LeaveTheWindowSuperuserRoleUnbounded fails if that changes.

A window that needs the exemption for a service role adds it to the estate’s existing PGOPTIONS idiom (precedent: docs/runbooks/authority/authority-deploy-auth8-staged-fold.md):

PGOPTIONS='-c lock_timeout=10000 -c statement_timeout=0 -c idle_in_transaction_session_timeout=0'

The cluster-wide setting stays at 0, deliberately. It would buy only platform and authority (the two connections that ride the superuser) at the cost of recreating stellaops-postgres and restarting every service database; it is exactly what would bound the window role; and it is silently defeatable — devops/compose/docker-compose.pg-tuning.override.yml defines its own command: list, and Compose replaces that list wholesale, so a -c idle_in_transaction_session_timeout=… on the base service would vanish with no error the moment the tuning override is layered.

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 startup of the owning service; there is no manual applier.
  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 secret-rotation command; stella scan secrets is secret-detection scanning, not connection-secret management.)

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