MADSci Database Backup and Migration Guide

import sys
from pathlib import Path
from datetime import datetime
from pydantic import AnyUrl

# Import MADSci backup tools
from madsci.common.backup_tools import PostgreSQLBackupTool, MongoDBBackupTool
from madsci.common.types.backup_types import (
    PostgreSQLBackupSettings,
    MongoDBBackupSettings,
)

print("✓ MADSci backup tools imported successfully")

# Configuration
BACKUP_BASE_DIR = Path("../../backups/notebook_examples")
BACKUP_BASE_DIR.mkdir(parents=True, exist_ok=True)

# Database connection strings
POSTGRES_URL = "postgresql://madsci:madsci@localhost:5432/resources"
MONGODB_URL = AnyUrl("mongodb://localhost:27017")

print(f"Backup directory: {BACKUP_BASE_DIR.absolute()}")
print(f"PostgreSQL URL: {POSTGRES_URL}")
print(f"MongoDB URL: {MONGODB_URL}")

# Test PostgreSQL connection
from sqlmodel import create_engine, text

try:
    engine = create_engine(POSTGRES_URL, pool_pre_ping=True)
    with engine.connect() as conn:
        result = conn.execute(text("SELECT version()"))
        version = result.fetchone()[0]

        result = conn.execute(text("SELECT pg_size_pretty(pg_database_size(current_database()))"))
        size = result.fetchone()[0]

    print("✓ PostgreSQL Connection Successful")
    print(f"  Version: {version.split(' ')[1]}")
    print(f"  Database Size: {size}")
except Exception as e:
    print(f"✗ PostgreSQL Connection Failed: {e}")

# Test MongoDB connection
from pymongo import MongoClient

databases = ["events", "data", "experiments", "workflows"]

for db_name in databases:
    try:
        client = MongoClient(str(MONGODB_URL), serverSelectionTimeoutMS=5000)
        client.admin.command("ping")

        db = client[db_name]
        stats = db.command("dbStats")

        print(f"✓ MongoDB '{db_name}' Connection Successful")
        print(f"  Collections: {stats['collections']}")
        print(f"  Size: {stats['dataSize'] / (1024 * 1024):.2f} MB")

        client.close()
    except Exception as e:
        print(f"✗ MongoDB '{db_name}' Connection Failed: {e}")

# Configure PostgreSQL backup settings
pg_settings = PostgreSQLBackupSettings(
    db_url=POSTGRES_URL,
    backup_dir=BACKUP_BASE_DIR / "postgres",
    max_backups=5,  # Keep last 5 backups
    validate_integrity=True,  # Validate with SHA256
    backup_format="custom",  # Compressed custom format
)

print("PostgreSQL Backup Configuration:")
print(f"  Backup directory: {pg_settings.backup_dir}")
print(f"  Max backups: {pg_settings.max_backups}")
print(f"  Validation: {pg_settings.validate_integrity}")
print(f"  Format: {pg_settings.backup_format}")

# Create PostgreSQL backup tool
pg_backup_tool = PostgreSQLBackupTool(pg_settings)

# Create backup
print("Creating PostgreSQL backup...")
try:
    backup_path = pg_backup_tool.create_backup("notebook_demo")
    print(f"✓ Backup created successfully: {backup_path}")
    print(f"  Size: {backup_path.stat().st_size / 1024:.2f} KB")
except Exception as e:
    print(f"✗ Backup failed: {e}")

# Configure MongoDB backup settings
mongo_settings = MongoDBBackupSettings(
    mongo_db_url=MONGODB_URL,
    database="madsci_events",
    backup_dir=BACKUP_BASE_DIR / "mongodb" / "madsci_events",
    max_backups=5,
    validate_integrity=True,
    collections=None,  # Backup all collections
)

print("MongoDB Backup Configuration:")
print(f"  Database: {mongo_settings.database}")
print(f"  Backup directory: {mongo_settings.backup_dir}")
print(f"  Collections: {'All' if mongo_settings.collections is None else ', '.join(mongo_settings.collections)}")

# Create MongoDB backup tool
mongo_backup_tool = MongoDBBackupTool(mongo_settings)

# Create backup
print("Creating MongoDB backup...")
try:
    backup_path = mongo_backup_tool.create_backup("notebook_demo")
    print(f"✓ Backup created successfully: {backup_path}")

    # Get backup size
    import subprocess
    size_output = subprocess.check_output(["du", "-sh", str(backup_path)]).decode()
    print(f"  Size: {size_output.split()[0]}")
except FileNotFoundError:
    print("⚠️  mongodump not found. See troubleshooting section.")
except Exception as e:
    print(f"✗ Backup failed: {e}")

# PostgreSQL CLI backup
!madsci-backup create --db-url postgresql://madsci:madsci@localhost:5432/resources \
    --backup-dir ../../backups/cli_examples --name cli_demo 2>&1 | tail -20

# MongoDB CLI backup
!madsci-backup create --db-url mongodb://localhost:27017/madsci_events \
    --backup-dir ../../backups/cli_examples --name cli_demo 2>&1 | tail -20

# List available PostgreSQL backups
print("Available PostgreSQL Backups:")
print("=" * 60)

backups = pg_backup_tool.list_available_backups()
for i, backup in enumerate(backups, 1):
    print(f"\n{i}. {backup.backup_path.name}")
    print(f"   Created: {backup.created_at}")
    print(f"   Size: {backup.backup_size / 1024:.2f} KB")
    print(f"   Checksum: {backup.checksum[:16]}...")
    print(f"   Valid: {'✓' if backup.is_valid else '✗'}")

if not backups:
    print("No backups found")

# Validate specific backup
if backups:
    latest_backup = backups[0]
    print(f"Validating backup: {latest_backup.backup_path.name}")

    is_valid = pg_backup_tool.validate_backup_integrity(latest_backup.backup_path)

    if is_valid:
        print("✓ Backup integrity verified!")
        print("  - SHA256 checksum matches")
        print("  - Metadata is valid")
        print("  - File is not corrupted")
    else:
        print("✗ Backup validation failed!")
        print("  - Do not use this backup for restore")
        print("  - Create a new backup")

print("Validating All PostgreSQL Backups:")
print("=" * 60)

valid_count = 0
invalid_count = 0

for backup in backups:
    is_valid = pg_backup_tool.validate_backup_integrity(backup.backup_path)
    status = "✓ Valid" if is_valid else "✗ Invalid"
    print(f"{status}: {backup.backup_path.name}")

    if is_valid:
        valid_count += 1
    else:
        invalid_count += 1

print("\nSummary:")
print(f"  Valid backups: {valid_count}")
print(f"  Invalid backups: {invalid_count}")

if invalid_count > 0:
    print("\n⚠️  Warning: Some backups are invalid. Delete and recreate them.")

# Check current database version
from sqlmodel import create_engine, text

engine = create_engine(POSTGRES_URL)

try:
    with engine.connect() as conn:
        # Check if schema_version table exists
        result = conn.execute(text("""
            SELECT EXISTS (
                SELECT FROM information_schema.tables
                WHERE table_name = 'madsci_schema_version'
            )
        """))
        table_exists = result.fetchone()[0]

        if table_exists:
            result = conn.execute(text("SELECT version, applied_at FROM madsci_schema_version ORDER BY applied_at DESC LIMIT 1"))
            row = result.fetchone()
            if row:
                print(f"Current Schema Version: {row[0]}")
                print(f"Last Updated: {row[1]}")
            else:
                print("Schema version table exists but is empty")
        else:
            print("Schema version table does not exist (fresh database)")
except Exception as e:
    print(f"Error checking schema version: {e}")

import subprocess

print("Pre-Migration Checklist")
print("=" * 60)

# 1. Check database connectivity
try:
    engine = create_engine(POSTGRES_URL)
    with engine.connect() as conn:
        conn.execute(text("SELECT 1"))
    print("✓ 1. Database connectivity verified")
except Exception as e:
    print(f"✗ 1. Database connectivity failed: {e}")

# 2. Check available backups
backups = pg_backup_tool.list_available_backups()
if backups and backups[0].is_valid:
    latest = backups[0]
    time_diff = datetime.now() - latest.created_at
    print(f"✓ 2. Recent valid backup exists ({time_diff.seconds // 60} minutes old)")
else:
    print("✗ 2. No recent valid backup found - create one before migrating!")

# 3. Check disk space
try:
    import shutil
    stats = shutil.disk_usage(BACKUP_BASE_DIR)
    free_gb = stats.free / (1024**3)
    if free_gb > 1.0:
        print(f"✓ 3. Sufficient disk space available ({free_gb:.2f} GB free)")
    else:
        print(f"⚠️  3. Low disk space: {free_gb:.2f} GB free")
except Exception as e:
    print(f"✗ 3. Could not check disk space: {e}")

# 4. Check for active connections
try:
    with engine.connect() as conn:
        result = conn.execute(text("""
            SELECT count(*)
            FROM pg_stat_activity
            WHERE datname = current_database()
            AND pid != pg_backend_pid()
        """))
        active_conns = result.fetchone()[0]
        if active_conns == 0:
            print("✓ 4. No active database connections (safe to migrate)")
        else:
            print(f"⚠️  4. {active_conns} active connections detected")
except Exception as e:
    print(f"✗ 4. Could not check active connections: {e}")

print("\n" + "=" * 60)
print("Migration readiness: Check all items above before proceeding")