gh-jeremylongshore-claude-code-plugins-plus-plugins-devops-fairdb-operations-kit/skills/fairdb-backup-manager/SKILL.md

name, description, allowed-tools, version

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