Cleaning up and backing up the Cloudera AI Workbench database manually

With the help of the script you can manually back up and clean up the Cloudera AI Workbench database.

• Configure the Workbench kubeconfig by setting it as an environment variable for the script to access. Use the following command:

  export KUBECONFIG=<your kubeconfig>
                  

  The script prompts you to specify a directory to store the backup file, define the retention time (entries older than this will be cleaned up), and optionally provide a Workbench name.

The result is an .sql file as a snapshot of the Cloudera AI database.

#!/bin/bash
            
            DB_NAME="sense"
            # Ask for database configuration
            read -p "Enter backup directory: " BACKUP_DIR
            while true; do
            read -p "Enter retention time (e.g., '30 days', '2 months'): " RETENTION_TIME
            
            # Validate format: number + space + interval unit (singular or plural)
            if [[ "$RETENTION_TIME" =~ ^[1-9][0-9]*\ (day|days|month|months|year|years|hour|hours|minute|minutes|second|seconds)$ ]]; then
            break
            else
            echo "Invalid retention time format. Please use formats like '30 days', '2 months', or '1 year'."
            fi
            done
            read -p "If on premises, enter workbench name: " INPUT_NAMESPACE
            K8S_NAMESPACE=${INPUT_NAMESPACE:-mlx}
            
            DB_POD="db-0"
            
            # Table, timestamp column, and optional extra condition
            TABLES=(
            "dashboards:created_at:id NOT IN (SELECT DISTINCT current_dashboard_id FROM applications WHERE applications.deleted_at IS NULL)"
            "dashboard_pods:created_at:status != 'running'"
            "model_deployments:created_at:stopped_at IS NOT NULL"
            "user_events:created_at:"
            )
            
            # Ensure backup directory exists
            if ! mkdir -p "$BACKUP_DIR"; then
            echo "Error: Failed to create or access backup directory '$BACKUP_DIR'. Check your permissions." >&2
            exit 1
            fi
            echo "Backup directory verified: $BACKUP_DIR"
            
            # Create a timestamped backup file
            BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_backup_$(date +"%Y%m%d%H%M%S").dump"
            echo "Backup file path: $BACKUP_FILE"
            
            # Inside the pod: create the dump file
            kubectl exec -n "$K8S_NAMESPACE" -c db "$DB_POD"-- pg_dump -d "$DB_NAME" -F c -f /tmp/db_backup.dump
            
            # Then copy it to local
            kubectl cp -c db "$K8S_NAMESPACE/$DB_POD:/tmp/db_backup.dump" "$BACKUP_FILE"
            
            if [ $? -eq 0 ]; then
            echo "Backup created successfully: $BACKUP_FILE"
            else
            echo "Backup failed!" >&2
            exit 1
            fi
            
            # Prompt the user for confirmation before cleanup
            read -p "Are you sure you want to delete old entries from the database? (yes/no): " CONFIRM
            if [[ "$CONFIRM" != "yes" ]]; then
            echo "Cleanup aborted by user."
            exit 0
            fi
            
            # Clean up old entries from multiple tables
            for ENTRY in "${TABLES[@]}"; do
            IFS=":" read -r TABLE TIMESTAMP_COLUMN EXTRA_CONDITION <<< "$ENTRY"
            
            # Build base DELETE query
            DELETE_QUERY="DELETE FROM $TABLE WHERE $TIMESTAMP_COLUMN < NOW() - INTERVAL '$RETENTION_TIME'"
            
            # Append extra condition if present
            if [ -n "$EXTRA_CONDITION" ]; then
            DELETE_QUERY+=" AND $EXTRA_CONDITION"
            fi
            
            echo "Executing cleanup query on $TABLE: $DELETE_QUERY"
            
            # Execute the cleanup query using kubectl exec
            kubectl exec -n "$K8S_NAMESPACE" -c db "$DB_POD" -- psql -d "$DB_NAME" -c "$DELETE_QUERY"
            
            if [ $? -eq 0 ]; then
            echo "Old entries deleted successfully from $TABLE."
            else
            echo "Error: Failed to delete old entries from $TABLE. Cleanup process interrupted — not all tables were processed." >&2
            exit 1
            fi
            done