Maintenance Procedures

This guide covers the essential maintenance procedures for keeping your Chirp deployment running smoothly. These tasks focus on the highest-impact, lowest-risk maintenance activities.

Recommended Maintenance Schedule

Weekly Tasks (High Value, Zero Risk)

• Database Vacuum & Analyze: Reclaims space and optimizes queries
• Log Rotation: Prevents disk space exhaustion
• Health Checks: Basic system verification

Monthly Tasks (Optional)

• Database Reindex: Only if you notice slow queries
• File Cleanup: Remove temporary files and old exports

Essential Maintenance Scripts

Weekly Safe Maintenance

#!/bin/bash
# scripts/weekly-safe-maintenance.sh

set -euo pipefail

LOG_FILE="/var/log/chirp-maintenance.log"
DATE=$(date '+%Y-%m-%d %H:%M:%S')

log() {
    echo "[$DATE] $1" | tee -a "$LOG_FILE"
}

# Database vacuum and analyze (completely safe)
perform_database_maintenance() {
    log "Starting safe database maintenance..."

    # Update table statistics for better query performance
    sudo -u postgres psql chirp -c "ANALYZE;"

    # Vacuum to reclaim space from deleted/updated rows
    sudo -u postgres psql chirp -c "VACUUM ANALYZE;"

    log "Database maintenance completed"
}

# Rotate logs to prevent disk space issues
rotate_logs() {
    log "Rotating application logs..."

    # Compress logs older than 7 days
    find /opt/chirp/logs -name "*.log" -type f -mtime +7 -exec gzip {} \; 2>/dev/null || true

    # Remove compressed logs older than 30 days
    find /opt/chirp/logs -name "*.log.gz" -type f -mtime +30 -delete 2>/dev/null || true

    log "Log rotation completed"
}

# Basic health checks
perform_health_checks() {
    log "Performing health checks..."

    # Check disk space
    DISK_USAGE=$(df -h / | awk 'NR==2 {print $5}' | sed 's/%//')
    if [ "$DISK_USAGE" -gt 85 ]; then
        log "WARNING: High disk usage: ${DISK_USAGE}%"
    fi

    # Check if Chirp service is running
    if ! systemctl is-active --quiet chirp 2>/dev/null; then
        log "WARNING: Chirp service is not running"
    fi

    # Check database connectivity
    if ! pg_isready -h localhost -p 5432 >/dev/null 2>&1; then
        log "WARNING: Database not accessible"
    fi

    log "Health checks completed"
}

# Clean up old temporary files
cleanup_temp_files() {
    log "Cleaning up temporary files..."

    # Remove temp files older than 24 hours
    find /tmp -name "chirp-*" -type f -mtime +0 -delete 2>/dev/null || true

    # Clean up old cache files
    find /opt/chirp/cache -name "*.tmp" -type f -mtime +1 -delete 2>/dev/null || true

    log "Temporary file cleanup completed"
}

# Main execution
main() {
    log "=== Weekly Safe Maintenance Started ==="

    perform_database_maintenance
    rotate_logs
    perform_health_checks
    cleanup_temp_files

    log "=== Weekly Safe Maintenance Completed ==="
}

main "$@"

Optional Monthly Reindex (Use Only If Needed)

#!/bin/bash
# scripts/monthly-reindex.sh

set -euo pipefail

LOG_FILE="/var/log/chirp-maintenance.log"
DATE=$(date '+%Y-%m-%d %H:%M:%S')

log() {
    echo "[$DATE] $1" | tee -a "$LOG_FILE"
}

# Only run if you have performance issues
perform_conditional_reindex() {
    log "Checking if reindex is needed..."

    # Check for index bloat (simplified check)
    BLOAT_CHECK=$(sudo -u postgres psql chirp -t -c "
        SELECT
            CASE
                WHEN (pg_stat_get_dead_tuples(c.oid)::float /
                      (pg_stat_get_live_tuples(c.oid) + pg_stat_get_dead_tuples(c.oid))) > 0.2
                THEN 'REINDEX_NEEDED'
                ELSE 'OK'
            END as status
        FROM pg_class c
        WHERE c.relname = 'messages';
    " 2>/dev/null | xargs || echo "OK")

    if [ "$BLOAT_CHECK" = "REINDEX_NEEDED" ]; then
        log "Index bloat detected, performing reindex..."

        # Reindex only the most important tables
        sudo -u postgres psql chirp -c "REINDEX TABLE messages;"
        sudo -u postgres psql chirp -c "REINDEX TABLE users;"

        log "Reindex completed"
    else
        log "No reindex needed at this time"
    fi
}

# Clean up old database exports
cleanup_old_exports() {
    log "Cleaning up old database exports..."

    # Remove exports older than 7 days
    find /opt/chirp/exports -name "*.sql" -type f -mtime +7 -delete 2>/dev/null || true
    find /opt/chirp/exports -name "*.csv" -type f -mtime +7 -delete 2>/dev/null || true

    log "Export cleanup completed"
}

# Generate simple status report
generate_status_report() {
    log "Generating status report..."

    REPORT_DATE=$(date +%Y-%m-%d)
    REPORT_DIR="/opt/chirp/reports"
    mkdir -p "$REPORT_DIR"

    cat > "$REPORT_DIR/status_$REPORT_DATE.txt" << EOF
=== Chirp Status Report - $REPORT_DATE ===

System Resources:
- Disk Usage: $(df -h / | awk 'NR==2 {print $3 "/" $2 " (" $5 ")"}')
- Memory Usage: $(free -h | grep Mem | awk '{print $3 "/" $2}')
- Uptime: $(uptime -p 2>/dev/null || echo "N/A")

Database Status:
- Database Size: $(sudo -u postgres psql chirp -t -c "SELECT pg_size_pretty(pg_database_size('chirp'));" 2>/dev/null | xargs || echo "N/A")
- Connections: $(sudo -u postgres psql chirp -t -c "SELECT COUNT(*) FROM pg_stat_activity;" 2>/dev/null | xargs || echo "N/A")

File Counts:
- Users: $(sudo -u postgres psql chirp -t -c "SELECT COUNT(*) FROM users;" 2>/dev/null | xargs || echo "N/A")
- Messages: $(sudo -u postgres psql chirp -t -c "SELECT COUNT(*) FROM messages;" 2>/dev/null | xargs || echo "N/A")
- Servers: $(sudo -u postgres psql chirp -t -c "SELECT COUNT(*) FROM servers;" 2>/dev/null | xargs || echo "N/A")

Service Status:
- Chirp Service: $(systemctl is-active chirp 2>/dev/null || echo "unknown")
- Database: $(pg_isready -h localhost -p 5432 >/dev/null 2>&1 && echo "ready" || echo "not ready")
EOF

    log "Status report generated"
}

# Main execution
main() {
    log "=== Monthly Maintenance Started ==="

    perform_conditional_reindex
    cleanup_old_exports
    generate_status_report

    log "=== Monthly Maintenance Completed ==="
}

main "$@"

Setting Up Automated Maintenance

Cron Configuration

# Edit crontab with: crontab -e

# Weekly safe maintenance - Every Sunday at 2 AM
0 2 * * 0 /opt/chirp/scripts/weekly-safe-maintenance.sh

# Monthly maintenance - First day of month at 3 AM
0 3 1 * * /opt/chirp/scripts/monthly-reindex.sh

# Optional: Daily backup check - Every day at 1 AM
0 1 * * * /opt/chirp/scripts/check-backups.sh

Log Rotation Configuration

# Create logrotate config: /etc/logrotate.d/chirp

/opt/chirp/logs/*.log {
    daily
    missingok
    rotate 30
    compress
    delaycompress
    notifempty
    create 644 chirp chirp
}

/var/log/chirp-maintenance.log {
    weekly
    missingok
    rotate 12
    compress
    delaycompress
    notifempty
    create 644 root root
}

Manual Database Queries (Safe Only)

Check Database Health

-- Check table sizes and bloat
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Check for dead tuples (indicator of needed vacuum)
SELECT
    relname,
    n_dead_tup,
    n_live_tup,
    round((n_dead_tup::float / (n_dead_tup + n_live_tup)) * 100, 2) as dead_percentage
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY dead_percentage DESC;

Manual Vacuum (If Needed)

-- Update table statistics only
ANALYZE;

-- Standard vacuum (non-locking, safe to run anytime)
VACUUM;

-- Vacuum with analyze (recommended for maintenance)
VACUUM ANALYZE;

When to Run Reindex

Signs you might need reindexing:

• Message loading is getting progressively slower
• Database queries that used to be fast are now slow
• High message deletion/editing activity
• You notice dead_percentage above 20% in the dead tuples query

Before running reindex:

1. Always create a backup first
2. Run during low-usage hours (e.g., weekend nights)
3. The process may lock tables temporarily

Simple Health Monitoring

Create a simple monitoring script:

#!/bin/bash
# scripts/quick-health-check.sh

DISK_USAGE=$(df -h / | awk 'NR==2 {print $5}' | sed 's/%//')
SERVICE_STATUS=$(systemctl is-active chirp 2>/dev/null || echo "unknown")
DB_STATUS=$(pg_isready -h localhost -p 5432 >/dev/null 2>&1 && echo "ready" || echo "not ready")

echo "=== Quick Health Check ==="
echo "Disk Usage: ${DISK_USAGE}%"
echo "Chirp Service: $SERVICE_STATUS"
echo "Database: $DB_STATUS"

if [ "$DISK_USAGE" -gt 85 ]; then
    echo "⚠️  WARNING: High disk usage"
fi

if [ "$SERVICE_STATUS" != "active" ]; then
    echo "❌ ERROR: Chirp service not running"
fi

if [ "$DB_STATUS" != "ready" ]; then
    echo "❌ ERROR: Database not accessible"
fi

Benefits of These Tasks

Weekly Vacuum & Analyze ✅

• Reclaims disk space from deleted messages/updates
• Updates query optimizer statistics for better performance
• Completely safe to run anytime
• Minimal performance impact

Log Rotation ✅

• Prevents disk space exhaustion
• Makes log management easier
• Zero risk to application

Health Checks ✅

• Early detection of issues
• Prevents small problems from becoming big ones
• Helps with capacity planning

Reindex ⚠️ (Optional)

• Benefit: Fixes index fragmentation, improves query performance
• Risk: Can temporarily lock tables during reindexing
• Reality: Most instances won't need this frequently
• Recommendation: Only run if you notice performance issues

For most Chirp deployments, the weekly safe maintenance script provides all the benefits you need with zero risk.