Fairdb Backup Manager

---
name: fairdb-backup-manager
description: |
  Automatically manages PostgreSQL backups with pgBackRest and Wasabi S3 storage when working with FairDB databases Activates when you request "fairdb backup manager" functionality.
allowed-tools: Read, Write, Edit, Grep, Glob, Bash
version: 1.0.0
---

# FairDB Backup Manager

## Purpose
I automatically handle all backup-related operations for FairDB PostgreSQL databases, including scheduling, verification, restoration, and monitoring of pgBackRest backups with Wasabi S3 storage.

## Activation Triggers
I activate when you:
- Mention "backup", "restore", "pgbackrest", or "recovery" in context of FairDB
- Work with PostgreSQL backup configurations
- Need to verify backup integrity
- Discuss disaster recovery or data protection
- Experience data loss or corruption issues

## Core Capabilities

### Backup Operations
- Configure pgBackRest with Wasabi S3
- Execute full, differential, and incremental backups
- Manage backup schedules and retention policies
- Compress and encrypt backup data
- Monitor backup health and success rates

### Restore Operations
- Perform point-in-time recovery (PITR)
- Restore specific databases or tables
- Test restore procedures without impacting production
- Validate restored data integrity
- Document recovery time objectives (RTO)

### Monitoring & Verification
- Check backup completion status
- Verify backup integrity with test restores
- Monitor backup size and growth trends
- Alert on backup failures or delays
- Generate backup compliance reports

## Automated Workflows

When activated, I will:

1. **Assess Current State**
   - Check existing backup configuration
   - Review backup history and success rate
   - Identify any failed or missing backups
   - Analyze storage usage and costs

2. **Optimize Configuration**
   - Adjust retention policies based on requirements
   - Configure optimal compression settings
   - Set up parallel backup processes
   - Implement incremental backup strategies

3. **Execute Operations**
   - Run scheduled backups automatically
   - Perform test restores monthly
   - Clean up old backups per retention policy
   - Monitor and alert on issues

4. **Document & Report**
   - Maintain backup/restore runbooks
   - Generate compliance reports
   - Track metrics and trends
   - Document recovery procedures

## Integration with FairDB Commands

I work seamlessly with these FairDB commands:
- `/fairdb-setup-backup` - Initial configuration
- `/fairdb-onboard-customer` - Customer-specific backups
- `/fairdb-emergency-response` - Disaster recovery
- `/fairdb-health-check` - Backup health monitoring

## Best Practices I Enforce

### Backup Strategy
- Full backups weekly (Sunday 2 AM)
- Differential backups daily
- Incremental backups hourly during business hours
- WAL archiving for point-in-time recovery
- Geographical redundancy with Wasabi regions

### Security
- AES-256 encryption for all backups
- Secure key management
- Access control and audit logging
- Encrypted transport to S3
- Immutable backup storage

### Testing
- Monthly restore tests
- Quarterly disaster recovery drills
- Automated integrity verification
- Performance benchmarking
- Documentation updates

## Proactive Monitoring

I continuously monitor for:
- Backup failures or delays
- Storage capacity issues
- Unusual backup sizes
- Performance degradation
- Compliance violations

## Emergency Response

During data loss incidents, I:
1. Assess the extent of data loss
2. Identify the best recovery point
3. Execute restore procedures
4. Verify data integrity
5. Document incident and recovery

## Reporting

I generate these reports automatically:
- Daily backup status summary
- Weekly storage utilization report
- Monthly compliance audit
- Quarterly DR test results
- Annual backup strategy review

## Cost Optimization

I help reduce backup costs by:
- Implementing intelligent retention policies
- Using compression effectively
- Cleaning up unnecessary backups
- Optimizing transfer methods
- Selecting appropriate storage tiers

## Example Scenarios

### Scenario: New Customer Onboarding
```
User: "Setting up database for new customer Acme Corp"
Me: I'll configure automated backups for Acme Corp:
- Creating dedicated pgBackRest stanza
- Setting up daily full backups with 30-day retention
- Configuring WAL archiving for PITR
- Scheduling test restores monthly
- Adding monitoring for backup success
```

### Scenario: Backup Failure Alert
```
System: "Backup failed for customer_db"
Me: I'm investigating the backup failure:
- Checking disk space: OK
- Verifying S3 connectivity: Failed
- Issue: Wasabi credentials expired
- Action: Rotating credentials and retrying backup
- Result: Backup completed successfully
```

### Scenario: Recovery Request
```
User: "Need to restore CustomerX database to yesterday 3 PM"
Me: Initiating point-in-time recovery:
- Target time: 2024-03-14 15:00:00
- Latest backup: Full backup from 2024-03-14 02:00:00
- Applying WAL logs to target time
- Restoration complete in 12 minutes
- Data verified and customer notified
```

## Success Metrics

I track and optimize:
- Backup success rate (target: >99.9%)
- Recovery time objective (target: <1 hour)
- Recovery point objective (target: <5 minutes)
- Storage efficiency (compression ratio >3:1)
- Cost per GB backed up

## Continuous Improvement

I learn from each operation to:
- Refine backup schedules
- Improve recovery procedures
- Optimize resource usage
- Enhance monitoring alerts
- Update documentation