HomeAudit/nextcloud_database_migration.sh

#!/bin/bash

# Nextcloud Database Migration Script
# Migrates SQLite database from MergerFS to local drive on OMV800
# Created: $(date)

set -e

echo "=== Nextcloud Database Migration Script ==="
echo "Migrating SQLite database from MergerFS to local drive on OMV800"
echo ""

# Configuration
NEXTCLOUD_DATA_DIR="/export/nextcloud/data"
LOCAL_DB_DIR="/srv/dev-disk-by-uuid-0f772f0b-917d-4337-a3c5-5cc5d3badac9/nextcloud-db"
SERVICE_NAME="nextcloud_nextcloud"
BACKUP_DIR="/export/nextcloud_backups"

echo "Available space on target drive:"
ssh root@omv800 "df -h /srv/dev-disk-by-uuid-0f772f0b-917d-4337-a3c5-5cc5d3badac9"

# Create local database directory
echo ""
echo "Step 1: Creating local database directory..."
ssh root@omv800 "mkdir -p $LOCAL_DB_DIR"
ssh root@omv800 "chown -R 33:33 $LOCAL_DB_DIR"
echo "✅ Local database directory created: $LOCAL_DB_DIR"

# Create backup
echo ""
echo "Step 2: Creating backup of current database..."
mkdir -p "$BACKUP_DIR"
BACKUP_FILE="$BACKUP_DIR/nextcloud_db_backup_$(date +%Y%m%d_%H%M%S).tar.gz"
ssh root@omv800 "cd $NEXTCLOUD_DATA_DIR && tar -czf $BACKUP_FILE ."
echo "✅ Database backup created: $BACKUP_FILE"

# Stop Nextcloud service
echo ""
echo "Step 3: Stopping Nextcloud service..."
ssh root@omv800 "docker service scale $SERVICE_NAME=0"
echo "Waiting for service to stop..."
sleep 30

# Copy database to local storage
echo ""
echo "Step 4: Copying database to local drive..."
ssh root@omv800 "cp -r $NEXTCLOUD_DATA_DIR/* $LOCAL_DB_DIR/"
ssh root@omv800 "chown -R 33:33 $LOCAL_DB_DIR"
echo "✅ Database copied to local drive"

# Update docker-compose configuration
echo ""
echo "Step 5: Updating docker-compose configuration..."
cd /home/jonathan/Coding/HomeAudit

# Create backup of current config
cp stacks/apps/nextcloud.yml stacks/apps/nextcloud.yml.db_migration_backup.$(date +%Y%m%d_%H%M%S)

# Update the volume mapping to use local database storage
# First, remove the old volume mapping
sed -i '/- \/export\/nextcloud:\/var\/www\/html$/!b;/n' stacks/apps/nextcloud.yml

# Add the new volume mappings
sed -i 's|- /export/nextcloud:/var/www/html|- /export/nextcloud:/var/www/html\n      - /srv/dev-disk-by-uuid-0f772f0b-917d-4337-a3c5-5cc5d3badac9/nextcloud-db:/var/www/html/data|g' stacks/apps/nextcloud.yml

echo "✅ Docker-compose configuration updated"

# Deploy updated service
echo ""
echo "Step 6: Deploying updated service with local database..."
ssh root@omv800 "cd /home/jonathan/Coding/HomeAudit && docker stack deploy -c stacks/apps/nextcloud.yml nextcloud"

echo "Waiting for service to start..."
sleep 60

# Check service status
echo ""
echo "Step 7: Checking service status..."
if ssh root@omv800 "docker service ls | grep -q $SERVICE_NAME"; then
    echo "✅ Service is running"
else
    echo "❌ Service failed to start"
    echo "Rolling back..."
    ssh root@omv800 "docker service scale $SERVICE_NAME=0"
    cp stacks/apps/nextcloud.yml.db_migration_backup.* stacks/apps/nextcloud.yml
    ssh root@omv800 "cd /home/jonathan/Coding/HomeAudit && docker stack deploy -c stacks/apps/nextcloud.yml nextcloud"
    exit 1
fi

# Wait for container to be ready
echo ""
echo "Step 8: Waiting for container to be ready..."
sleep 30

# Verify database connectivity
echo ""
echo "Step 9: Verifying database connectivity..."
CONTAINER_ID=$(ssh root@omv800 "docker ps -q -f name=nextcloud")

if [ -n "$CONTAINER_ID" ]; then
    # Check if Nextcloud can access the database
    ssh root@omv800 "docker exec -u 33 $CONTAINER_ID php /var/www/html/occ status"
    
    # Test database operations
    echo "Testing database operations..."
    ssh root@omv800 "docker exec -u 33 $CONTAINER_ID php /var/www/html/occ app:list" | head -5
    
    echo "✅ Database migration completed successfully"
else
    echo "❌ Container not found"
    exit 1
fi

echo ""
echo "=== MIGRATION SUMMARY ==="
echo "✅ Database backup created: $BACKUP_FILE"
echo "✅ Database migrated to local drive: $LOCAL_DB_DIR"
echo "✅ Docker-compose configuration updated"
echo "✅ Service running with local database"
echo ""
echo "Benefits of local drive database storage:"
echo "- No more MergerFS database locking issues"
echo "- Improved database performance"
echo "- Better reliability for concurrent operations"
echo "- Reduced risk of database corruption"
echo "- Database stored on dedicated local drive (sdc1)"
echo ""
echo "Storage location: /srv/dev-disk-by-uuid-0f772f0b-917d-4337-a3c5-5cc5d3badac9/nextcloud-db"
echo "Available space: 154G"
echo ""
echo "If you encounter issues, restore from backup:"
echo "tar -xzf $BACKUP_FILE -C $NEXTCLOUD_DATA_DIR/"