Fix a finding

From finding to reviewed, reversible change: review, plan, apply, revert.

pgdba check prints remediation SQL for every finding. Two paths turn that SQL into a change — direct per-action apply, or a generated fix plan with recorded reverts — and both stay dry-run until you approve.

Review the finding

Findings are ranked by severity in the terminal plus pgdba-report.md / pgdba-report.json. Drill into one before touching anything:

pgdba why <finding-id>   # plain-language explainer from the last report

Plan the change

pgdba fix <finding-id> reads the finding from the last report, builds an ordered plan with a recorded revert for every statement, and writes three artifacts:

pgdba check --dsn "$DATABASE_URL"
pgdba fix index.advisor
# fix plan for index.advisor: 1 statement(s), 1 with recorded reverts
# scripts: pgdba-fix-index.advisor.sql, pgdba-fix-index.advisor.revert.sql

• pgdba-fix-<id>.sql — the executable statements in order, with preconditions (locks, CONCURRENTLY notes) and blast-radius notes as comments.
• pgdba-fix-<id>.revert.sql — the mapped reverts in reverse order, with MANUAL STEP notes where no mechanical revert exists.
• pgdba-fix-<id>.json — the machine-readable plan (statements, revert map, checksum for resume).

Without flags nothing executes — not even with a --dsn. Findings whose remediation is prose (not mechanically executable SQL) produce a guidance-only plan labeled manual fix, and --apply refuses them.

Apply it

Direct path — execute what you approve, per action:

pgdba check --apply                          # per-action prompts
pgdba check --apply --yes                    # blanket approval — requires --apply
pgdba check --apply --only connection.max_connections

Plan path — execute a reviewed fix plan:

pgdba fix index.advisor --apply --yes --dsn "$DATABASE_URL"
# fix applied 1 statement(s) for index.advisor

The gates, in order:

1. Findings are ranked by severity; you select which to apply (or approve all with --yes).
2. A blast-radius warning shows the action count plus every HIGH-risk action’s remediation line before anything runs.
3. Per-action confirm unless --yes. On a non-TTY run, --apply without --yes is refused — the report is still written (the refusal is recorded in the audit log), and the exit is 1.
4. Every action is audited: the report’s audit log records applied, skipped, failed, and retrying rows with attempts, in execution order.

The plan path runs through the same safety layer (per-statement gating, pooled-target direct-DSN bypass, audit rows) and records applied tags in the audit journal. Without --yes, a TTY gets per-action confirms and a non-TTY aborts. Interrupted applies resume: already-applied statements are skipped on re-run.

Config-class findings (configuration duty) execute through the same shared layer — there is no separate config-apply path — with reload notes (SELECT pg_reload_conf() vs restart) in the preconditions; cross-check pgdba config proposals before changing values by hand.

Hard limits

• RDS/replica targets refuse writes per finding (reported in the audit log).
• Multi-statement remediation stays dry-run for manual review.
• Deadlocked or serialization-failed statements retry up to 2 times.
• Nothing writes without --apply; cron must never pass it.

Revert it

pgdba fix index.advisor --revert --yes --dsn "$DATABASE_URL"
# fix reverted 1 statement(s) for index.advisor

Reverts run the mapped .revert.sql statements in reverse order. Check the file for MANUAL STEP notes first — those need your hands, not the tool’s.