# Common error signatures that indicate this scenario
grep -E "(checksum mismatch|invalid page|PANIC.*checkpoint|recovery mode)" /var/log/postgresql/*.log

You are an on-call database reliability engineer responding to a PostgreSQL corruption alert.

**Context:**
- Service: {{SERVICE_NAME}}
- Database: {{DATABASE_NAME}}
- Error Log (last 100 lines):

{{ERROR_LOG}}

**Your task:**
1. Classify the corruption type (logical vs physical, recoverable vs catastrophic)
2. Identify the specific tables/indexes affected if determinable
3. Assess blast radius - what services depend on this database?
4. Recommend immediate containment actions (read-only mode, failover, etc.)

**Output format:**
- **Classification:** [type]
- **Affected Objects:** [list]
- **Blast Radius:** [services]
- **Immediate Actions:** [numbered list]
- **Confidence Level:** [high/medium/low] with reasoning

You are a PostgreSQL internals expert performing root cause analysis on database corruption.

**Incident Context:**
- Initial Assessment: {{PHASE1_OUTPUT}}
- PostgreSQL Version: {{PG_VERSION}}
- Storage Type: {{STORAGE_TYPE}} (e.g., EBS, local NVMe, network storage)
- Recent Events:
  - Deployments: {{RECENT_DEPLOYMENTS}}
  - Config Changes: {{CONFIG_CHANGES}}
  - Infrastructure Events: {{INFRA_EVENTS}}

**Available Diagnostics:**
{{PG_CONTROLDATA_OUTPUT}}

{{DMESG_STORAGE_ERRORS}}

**Your task:**
1. Determine root cause with confidence percentage
2. Identify the corruption timeline (when did it start?)
3. Assess data loss scope (which transactions may be lost?)
4. Map the dependency chain that led to this failure

**Output as structured analysis:**
{
  "root_cause": {
    "primary": "string",
    "contributing_factors": ["string"],
    "confidence": 0.0-1.0
  },
  "timeline": {
    "corruption_started": "ISO8601",
    "detection_time": "ISO8601",
    "estimated_window": "duration"
  },
  "data_loss": {
    "scope": "none|minimal|moderate|severe",
    "affected_tables": ["string"],
    "transaction_window": "duration"
  }
}

You are an incident commander deciding the recovery strategy for database corruption.

**Situation:**
- Root Cause Analysis: {{PHASE2_OUTPUT}}
- Available Backups:
  - Last full backup: {{LAST_FULL_BACKUP}} (age: {{BACKUP_AGE}})
  - WAL archives available: {{WAL_AVAILABLE}}
  - Standby replica status: {{REPLICA_STATUS}}
- Business Context:
  - RPO (acceptable data loss): {{RPO}}
  - RTO (acceptable downtime): {{RTO}}
  - Current downtime: {{CURRENT_DOWNTIME}}

**Recovery Options:**
1. **PITR (Point-in-Time Recovery)** - Restore to moment before corruption
2. **Failover to Replica** - Promote standby (if healthy)
3. **Selective Table Recovery** - Restore only affected tables
4. **pg_resetwal** - Force recovery (last resort, data loss likely)

**Your task:**
1. Rank recovery options by likelihood of success
2. Estimate time and data loss for each option
3. Recommend primary and fallback strategies
4. Identify go/no-go decision points

You are guiding an engineer through database recovery execution.

**Selected Strategy:** {{SELECTED_STRATEGY}}
**Environment:**
- Primary Host: {{PRIMARY_HOST}}
- Backup Location: {{BACKUP_LOCATION}}
- Target Recovery Point: {{TARGET_RPO}}

**Generate step-by-step execution commands for {{SELECTED_STRATEGY}}.**

Requirements:
1. Each step must be atomic and reversible where possible
2. Include verification commands after each step
3. Include rollback instructions if step fails
4. Estimate time for each step
5. Flag any steps requiring manual approval

Generate verification steps for database recovery.

**Recovery Strategy Used:** {{STRATEGY}}
**Target Recovery Point:** {{TARGET_RPO}}
**Affected Services:** {{SERVICES}}

**Create verification checklist for:**
1. Data integrity (checksums pass)
2. No missing transactions within RPO
3. All application queries working
4. Replication health (if applicable)
5. Backup chain restored

# Check data integrity
pg_amcheck --all --verbose

# Verify table checksums
psql -c "SELECT relname, pg_relation_filepath(oid) FROM pg_class WHERE relkind = 'r' LIMIT 10;"

# Confirm replication status
psql -c "SELECT client_addr, state, sent_lsn, replay_lsn FROM pg_stat_replication;"

# Run application health checks
for service in api auth worker; do
  curl -f "https://${service}.internal/health"
done

immediate_actions:
  - action: "Enable data checksums"
    command: "initdb --data-checksums (requires reinit or pg_checksums)"
    
  - action: "Verify backup integrity"
    frequency: "Daily"
    command: "pg_restore --list {{BACKUP_FILE}} && pg_verifybackup"

monitoring_improvements:
  - action: "Add corruption detection alerts"
    metrics:
      - "PostgreSQL checksum failures"
      - "Storage I/O errors"
      - "Replication lag"
    
  - action: "Implement backup verification"
    frequency: "Weekly restore test to staging"

infrastructure_hardening:
  - action: "Enable synchronous replication"
    config: "synchronous_commit = on"
    
  - action: "Use checksummed storage"
    options: ["ZFS", "Btrfs", "EBS with checksums"]