Skip to content

Backup Recovery

Jump to bottom
Temp edited this page Oct 3, 2025 · 2 revisions

Backup & Recovery Tools

Backup planning, restore validation, and schedule optimization for mission-critical PostgreSQL databases.

📊 Overview

4 enterprise backup tools for comprehensive backup and recovery planning:

Tool Purpose Category
backup_logical Plan logical backups (pg_dump) Backup Planning
backup_physical Assess physical backup readiness Backup Planning
restore_validate Validate backup restore procedures Recovery Validation
backup_schedule_optimize Optimize backup schedules Backup Strategy

🔧 Tool Details

backup_logical

Analyze database for logical backup planning using pg_dump. Provides size estimates, recommended options, and timing predictions.

Parameters:

  • include_schema (boolean, optional): Include schema-only analysis
  • include_data (boolean, optional): Include data-only analysis
  • table_filter (string, optional): Filter by specific table pattern

Returns:

  • database_info: Database name, size, and statistics
  • size_analysis: Expected backup size and compression estimates
  • timing_estimates: Backup duration predictions
  • recommended_options: Suggested pg_dump command options
  • warnings: Potential issues to address before backup

Example:

result = backup_logical(
    include_schema=True,
    include_data=True,
    table_filter="orders%"
)
# Returns: {
#   "database_info": {
#     "database": "production_db",
#     "total_size_bytes": 50000000000,
#     "total_size_gb": 46.57,
#     "table_count": 125,
#     "total_rows": 15000000
#   },
#   "size_analysis": {
#     "estimated_uncompressed_bytes": 48000000000,
#     "estimated_uncompressed_gb": 44.7,
#     "estimated_compressed_bytes": 12000000000,
#     "estimated_compressed_gb": 11.2,
#     "compression_ratio_percent": 75.0,
#     "largest_tables": [
#       {"table": "orders", "size_mb": 12500, "row_count": 5000000},
#       {"table": "order_items", "size_mb": 8900, "row_count": 8000000}
#     ]
#   },
#   "timing_estimates": {
#     "estimated_duration_minutes": 45,
#     "estimated_start_time": "02:00",
#     "estimated_end_time": "02:45",
#     "backup_window_required_minutes": 60
#   },
#   "recommended_options": {
#     "format": "custom",
#     "compression": "9",
#     "parallel_jobs": 4,
#     "sample_command": "pg_dump -Fc -Z9 -j4 -f backup.dump production_db"
#   },
#   "warnings": [
#     {
#       "severity": "medium",
#       "issue": "Large tables detected",
#       "recommendation": "Consider parallel dumps for tables > 1GB"
#     }
#   ]
# }

Use Cases:

  • Backup size estimation
  • Backup window planning
  • pg_dump command optimization
  • Cost estimation for backup storage

backup_physical

Assess readiness for physical backups (pg_basebackup, PITR). Checks WAL archiving, replication slots, and configuration.

Parameters:

  • check_wal_archiving (boolean, optional): Check WAL archiving configuration
  • check_replication_slots (boolean, optional): Check replication slot status

Returns:

  • backup_readiness: Overall readiness assessment
  • wal_status: WAL archiving configuration and status
  • replication_slots: Active and inactive slots
  • configuration: Relevant PostgreSQL configuration
  • recommendations: Steps to improve backup readiness

Example:

result = backup_physical(
    check_wal_archiving=True,
    check_replication_slots=True
)
# Returns: {
#   "backup_readiness": {
#     "overall_status": "ready",
#     "wal_archiving": "enabled",
#     "replication_slots": "healthy",
#     "continuous_archiving": True
#   },
#   "wal_status": {
#     "wal_level": "replica",
#     "archive_mode": "on",
#     "archive_command": "cp %p /mnt/wal_archive/%f",
#     "current_wal_lsn": "0/5A2F3C0",
#     "last_archived_wal": "000000010000000000000059",
#     "archiving_lag_seconds": 2.5
#   },
#   "replication_slots": {
#     "total_count": 2,
#     "active_count": 2,
#     "inactive_count": 0,
#     "slots": [
#       {
#         "slot_name": "replica1_slot",
#         "slot_type": "physical",
#         "active": True,
#         "restart_lsn": "0/5A2F000",
#         "confirmed_flush_lsn": null
#       }
#     ]
#   },
#   "configuration": {
#     "max_wal_senders": 10,
#     "max_replication_slots": 10,
#     "wal_keep_size": "1024MB",
#     "wal_sender_timeout": "60s"
#   },
#   "recommendations": [
#     {
#       "priority": "info",
#       "recommendation": "WAL archiving properly configured",
#       "action": "None required"
#     }
#   ]
# }

Use Cases:

  • PITR setup verification
  • Replication monitoring
  • Backup infrastructure health checks
  • Disaster recovery planning

restore_validate

Validate backup restore procedures and estimate recovery times.

Parameters:

  • backup_type (string, required): Type of backup (logical or physical)
  • estimated_backup_size_gb (number, optional): Estimated backup size in GB
  • parallel_restore_jobs (integer, optional): Number of parallel jobs for restore

Returns:

  • validation_results: Restore procedure validation
  • timing_estimates: Recovery time objectives (RTO)
  • requirements: System requirements for restore
  • recommendations: Best practices for restore procedures

Example:

# Validate logical restore
result = restore_validate(
    backup_type="logical",
    estimated_backup_size_gb=45.0,
    parallel_restore_jobs=4
)
# Returns: {
#   "validation_results": {
#     "backup_type": "logical",
#     "restore_method": "pg_restore",
#     "parallel_support": True,
#     "parallel_jobs": 4
#   },
#   "timing_estimates": {
#     "estimated_restore_minutes": 60,
#     "estimated_index_rebuild_minutes": 20,
#     "total_rto_minutes": 80,
#     "total_rto_hours": 1.33,
#     "backup_size_gb": 45.0
#   },
#   "requirements": {
#     "disk_space_required_gb": 67.5,
#     "buffer_percentage": 50,
#     "temp_space_gb": 22.5,
#     "memory_recommended_gb": 8
#   },
#   "recommendations": [
#     {
#       "priority": "high",
#       "category": "performance",
#       "recommendation": "Use parallel restore with -j4 for 4-core systems",
#       "command": "pg_restore -j4 -d database backup.dump"
#     },
#     {
#       "priority": "medium",
#       "category": "disk_space",
#       "recommendation": "Ensure 67.5 GB free disk space before restore"
#     }
#   ]
# }

# Validate physical restore
result = restore_validate(
    backup_type="physical",
    estimated_backup_size_gb=120.0
)
# Returns: {
#   "validation_results": {
#     "backup_type": "physical",
#     "restore_method": "pg_basebackup + WAL replay",
#     "pitr_capable": True
#   },
#   "timing_estimates": {
#     "estimated_restore_minutes": 30,
#     "estimated_wal_replay_minutes": 10,
#     "total_rto_minutes": 40,
#     "total_rto_hours": 0.67,
#     "backup_size_gb": 120.0
#   },
#   "requirements": {
#     "disk_space_required_gb": 180.0,
#     "buffer_percentage": 50,
#     "wal_archive_space_gb": 60.0,
#     "memory_recommended_gb": 16
#   },
#   "recommendations": [...]
# }

Use Cases:

  • Disaster recovery planning
  • RTO/RPO calculation
  • Restore testing preparation
  • Infrastructure sizing

backup_schedule_optimize

Optimize backup schedules based on database activity patterns and business requirements.

Parameters:

  • full_backup_frequency_hours (integer, required): Desired full backup frequency
  • incremental_backup_frequency_hours (integer, optional): Incremental backup frequency
  • analyze_activity_patterns (boolean, optional): Analyze database activity for optimal timing

Returns:

  • recommended_schedule: Optimized backup schedule
  • activity_analysis: Database activity patterns
  • resource_impact: Expected resource usage
  • retention_recommendations: Backup retention policies

Example:

result = backup_schedule_optimize(
    full_backup_frequency_hours=24,
    incremental_backup_frequency_hours=4,
    analyze_activity_patterns=True
)
# Returns: {
#   "recommended_schedule": {
#     "full_backups": {
#       "frequency_hours": 24,
#       "recommended_start_time": "02:00",
#       "estimated_duration_minutes": 45,
#       "daily_schedule": ["02:00"]
#     },
#     "incremental_backups": {
#       "frequency_hours": 4,
#       "recommended_start_times": ["06:00", "10:00", "14:00", "18:00", "22:00"],
#       "estimated_duration_minutes": 5
#     }
#   },
#   "activity_analysis": {
#     "peak_hours": [9, 10, 11, 14, 15, 16],
#     "low_activity_hours": [1, 2, 3, 4, 5],
#     "recommended_backup_window": "02:00-04:00",
#     "transaction_rate_per_hour": {
#       "peak": 125000,
#       "average": 45000,
#       "low": 5000
#     }
#   },
#   "resource_impact": {
#     "full_backup": {
#       "cpu_impact_percent": 15,
#       "io_impact_percent": 25,
#       "network_bandwidth_mbps": 150
#     },
#     "incremental_backup": {
#       "cpu_impact_percent": 5,
#       "io_impact_percent": 8,
#       "network_bandwidth_mbps": 50
#     }
#   },
#   "retention_recommendations": {
#     "full_backups": {
#       "daily_retention_days": 7,
#       "weekly_retention_weeks": 4,
#       "monthly_retention_months": 12
#     },
#     "incremental_backups": {
#       "retention_days": 7
#     },
#     "estimated_storage_gb": {
#       "per_full_backup": 45,
#       "per_incremental": 2,
#       "total_required": 405
#     }
#   }
# }

Use Cases:

  • Backup strategy optimization
  • RPO/RTO alignment
  • Cost optimization (storage, bandwidth)
  • Compliance planning (retention policies)

🎯 Common Workflows

Initial Backup Planning

# 1. Assess physical backup readiness
physical = backup_physical(
    check_wal_archiving=True,
    check_replication_slots=True
)

# 2. Plan logical backups
logical = backup_logical(
    include_schema=True,
    include_data=True
)

# 3. Optimize schedule
schedule = backup_schedule_optimize(
    full_backup_frequency_hours=24,
    incremental_backup_frequency_hours=4,
    analyze_activity_patterns=True
)

# 4. Validate restore procedures
validate = restore_validate(
    backup_type="logical",
    estimated_backup_size_gb=logical["size_analysis"]["estimated_compressed_gb"],
    parallel_restore_jobs=4
)

Backup Health Check

# 1. Check physical backup infrastructure
physical_status = backup_physical(
    check_wal_archiving=True,
    check_replication_slots=True
)

# 2. Verify logical backup can complete
logical_status = backup_logical(
    include_schema=True,
    include_data=True
)

# 3. Test restore validation
if physical_status["backup_readiness"]["overall_status"] == "ready":
    restore_test = restore_validate(
        backup_type="physical",
        estimated_backup_size_gb=120.0
    )

Disaster Recovery Planning

# 1. Document backup sizes
logical_plan = backup_logical(include_schema=True, include_data=True)
backup_size = logical_plan["size_analysis"]["estimated_compressed_gb"]

# 2. Calculate RTO
restore_plan = restore_validate(
    backup_type="logical",
    estimated_backup_size_gb=backup_size,
    parallel_restore_jobs=4
)
rto_minutes = restore_plan["timing_estimates"]["total_rto_minutes"]

# 3. Determine backup frequency for RPO
schedule = backup_schedule_optimize(
    full_backup_frequency_hours=24,
    incremental_backup_frequency_hours=1,  # 1-hour RPO
    analyze_activity_patterns=True
)

📊 Backup Best Practices

1. The 3-2-1 Rule

  • 3 copies of your data
  • 2 different media types
  • 1 copy offsite

2. Regular Testing

# Test restores monthly
restore_validate(backup_type="logical", estimated_backup_size_gb=50)

# Verify backup integrity weekly
backup_logical(include_schema=True, include_data=True)

3. Monitoring & Alerting

# Use with Monitoring tools
from Monitoring import alert_threshold_set, monitor_real_time

# Monitor backup duration
backup_alert = alert_threshold_set(
    metric_type="transaction_age",
    warning_threshold=1800,   # 30 minutes
    critical_threshold=3600   # 60 minutes
)

4. Retention Policies

Recommended Retention:

  • Daily: 7 days
  • Weekly: 4 weeks
  • Monthly: 12 months
  • Yearly: 7 years (compliance)

5. Documentation

Keep records of:

  • Backup schedules and frequencies
  • Restore procedures (runbooks)
  • Contact information for emergencies
  • Storage locations and credentials

🚨 Troubleshooting

Backup Too Large

# Identify large tables
result = backup_logical(include_data=True)
large_tables = result["size_analysis"]["largest_tables"]

# Consider:
# - Archiving old data
# - Partitioning large tables
# - Separate backup strategy for large tables

WAL Archiving Not Working

result = backup_physical(check_wal_archiving=True)
wal_status = result["wal_status"]

# Check:
# - archive_command syntax
# - Filesystem permissions
# - Disk space on archive location

Restore Too Slow

result = restore_validate(
    backup_type="logical",
    estimated_backup_size_gb=100,
    parallel_restore_jobs=8  # Increase parallelism
)

# Consider:
# - More CPU cores for parallel restore
# - Faster storage (SSD)
# - Restore indexes separately after data

📚 Related Documentation

🔗 External Resources

See Home for more tool categories.

Clone this wiki locally