docker run -d \
  --name ollama \
  --gpus all \
  -v ollama-data:/root/.ollama \
  -p 11434:11434 \
  ollama/ollama

docker exec -it ollama ollama pull qwen2.5:7b

curl http://localhost:11434/api/chat -d '{
  "model": "qwen2.5:7b",
  "messages": [{"role": "user", "content": "hello"}],
  "stream": false
}'

#!/bin/bash
# Collect key PostgreSQL metrics

TIMESTAMP=$(date '+%Y-%m-%d %H:%M:%S')

# Connection statistics
CONNECTIONS=$(psql -U postgres -t -c "SELECT count(*) FROM pg_stat_activity;")
MAX_CONNECTIONS=$(psql -U postgres -t -c "SHOW max_connections;")

# Cache hit ratio
CACHE_HIT=$(psql -U postgres -t -c "
  SELECT round(100.0 * (
    SUM(heap_blks_hit) / NULLIF(SUM(heap_blks_hit + heap_blks_read), 0)
  , 2) FROM pg_statio_user_tables;")

# Table bloat ratio
TABLE_BLOAT=$(psql -U postgres -t -c "
  SELECT schemaname, relname, 
    round(100.0 * (n_dead_tup::numeric / GREATEST(n_live_tup + n_dead_tup, 1)), 1) as dead_ratio
  FROM pg_stat_user_tables 
  ORDER BY n_dead_tup DESC LIMIT 5;")

# Disk usage
DISK_USAGE=$(df -h /var/lib/postgresql | tail -1 | awk '{print $5}')

echo "${TIMESTAMP}|Connections: ${CONNECTIONS}/${MAX_CONNECTIONS}|Cache Hit: ${CACHE_HIT}%|Disk: ${DISK_USAGE}|Bloat: ${TABLE_BLOAT}"

#!/bin/bash
# Send database health data to local LLM for tuning recommendations

HEALTH_DATA=$1
PROMPT="You are a senior Database Administrator (DBA). Analyze the following PostgreSQL database health status on a VPS and provide optimization recommendations.

Database health:
${HEALTH_DATA}

Please respond in this format:
1. [Risk Level] High / Medium / Low
2. [Key Issues] Brief description of main problems
3. [Immediate Actions] Specific SQL or config change commands
4. [Long-term Advice] Architectural optimization suggestions
5. [Expected Impact] Degree of improvement after executing"

RESPONSE=$(docker run --rm -v .:/data \
  --network host \
  ollama/ollama run qwen2.5:7b "$PROMPT" 2>/dev/null)

echo "$RESPONSE"
echo "$RESPONSE" >> /var/log/db-ai-analysis.log

# Run automatically every hour
0 * * * * /root/scripts/ai-optimize.sh "$(bash /root/scripts/db-health-check.sh)" >> /var/log/db-daily-report.log

#!/bin/bash
# Auto-recommend PostgreSQL config based on VPS resources

TOTAL_RAM=$(free -m | awk '/^Mem:/{print $2}')
CPU_CORES=$(nproc)
POSTGRES_RAM=$((TOTAL_RAM * 25 / 100))  # 25% of RAM for PostgreSQL
SHARED_BUFFERS=$((POSTGRES_RAM / 4))
EFFECTIVE_CACHE=$((TOTAL_RAM * 75 / 100))
MAINTENANCE_WORK=$((TOTAL_RAM * 5 / 100))

cat <<EOF
# AI-recommended PostgreSQL config (${TOTAL_RAM}MB RAM, ${CPU_CORES} cores)
shared_buffers = ${SHARED_BUFFERS}MB
effective_cache_size = ${EFFECTIVE_CACHE}MB
work_mem = $((POSTGRES_RAM / CPU_CORES / 4))MB
maintenance_work_mem = ${MAINTENANCE_WORK}MB
max_connections = 100
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
EOF

-- PostgreSQL
ALTER SYSTEM SET log_min_duration_statement = 500;  -- Log queries over 500ms
ALTER SYSTEM SET log_parameter = on;
ALTER SYSTEM SET log_statement = 'none';
SELECT pg_reload_conf();

-- MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';

#!/bin/bash

# Extract slow queries from the last hour
SLOW_QUERIES=$(
  psql -U postgres -c "
    SELECT now() - query_start as duration, 
           state, 
           left(query, 500) as query_snippet,
          usename
    FROM pg_stat_activity 
    WHERE state != 'idle' 
      AND now() - query_start > interval '30 seconds'
      AND query NOT LIKE '%pg_stat%'
    ORDER BY duration DESC;
  " 2>/dev/null
)

if [ -z "$SLOW_QUERIES" ]; then
  echo "✅ No slow or long-running queries detected"
  exit 0
fi

PROMPT="You are a PostgreSQL optimization expert. Analyze the following slow queries found on a VPS and provide optimization plans.

Currently running slow queries:
${SLOW_QUERIES}

Please provide:
1. Performance bottleneck analysis for each query
2. Recommended indexes (including full CREATE INDEX statements)
3. SQL rewrites for better performance
4. Whether table restructuring is recommended"

RESPONSE=$(docker run --rm --network host \
  ollama/ollama run qwen2.5:7b "$PROMPT" 2>/dev/null)

echo "$RESPONSE"
echo "$RESPONSE" >> /var/log/slow-query-analysis.log

#!/bin/bash
# collect-db-metrics.sh — Collect key database metrics

cat <<EOF
timestamp: $(date +%s)
postgres_connections: $(pg_isready -t 1 > /dev/null 2>&1 && echo 1 || echo 0)
postgres_uptime: $(pg_isready -t 1 > /dev/null 2>&1 && psql -U postgres -t -c "SELECT round(EXTRACT(EPOCH FROM (now() - pg_postmaster_start_time)))" 2>/dev/null || echo 0)
disk_usage_pct: $(df -P /var/lib/postgresql 2>/dev/null | tail -1 | awk '{print $5}' | tr -d '%' || echo 0)
memory_usage_pct: $(free | awk '/^Mem:/ {printf "%.0f", $3/$2 * 100}')
replication_lag: $(psql -U postgres -t -c "SELECT EXTRACT(EPOCH FROM replay_lag)::int FROM pg_stat_replication LIMIT 1" 2>/dev/null | tr -d ' ' || echo 0)
deadlocks: $(psql -U postgres -t -c "SELECT deadlocks FROM pg_stat_database WHERE datname = 'postgres'" 2>/dev/null | tr -d ' ' || echo 0)
EOF

#!/bin/bash
# ai-anomaly-detect.sh — Let AI determine if current state is abnormal

METRICS=$1
HISTORY=$2  # Historical metrics from last 7 days

PROMPT="You are a database operations expert. Analyze the following metrics and determine if any anomalies exist.

Current metrics:
${METRICS}

Historical comparison:
${HISTORY}

Please assess:
1. Which metrics deviate from normal ranges?
2. What are the possible causes of anomalies?
3. Does immediate intervention need? (Yes/No)
4. If yes, provide specific troubleshooting steps"

docker run --rm --network host \
  ollama/ollama run qwen2.5:7b "$PROMPT" 2>/dev/null

#!/bin/bash
# send-alert.sh — Send alert notifications

ALERT_MSG=$1
SEVERITY=$2  # critical / warning / info

# Optional: Send to Telegram / Slack / Email
TELEGRAM_BOT_TOKEN="YOUR_BOT_TOKEN"
TELEGRAM_CHAT_ID="YOUR_CHAT_ID"

if [ "$SEVERITY" = "critical" ] || [ "$SEVERITY" = "warning" ]; then
  curl -s -X POST \
    "https://api.telegram.org/bot${TELEGRAM_BOT_TOKEN}/sendMessage" \
    -d "chat_id=${TELEGRAM_CHAT_ID}" \
    -d "text=🚨 VPS Database Alert

${ALERT_MSG}" \
    -d "parse_mode=HTML"
fi

# Log to file
echo "[$(date)] [${SEVERITY}] ${ALERT_MSG}" >> /var/log/db-alerts.log

#!/bin/bash
# smart-backup.sh — AI-assisted intelligent backup

DB_NAME="myapp"
BACKUP_DIR="/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.sql.gz"

# Pre-backup: check disk space
DISK_AVAIL=$(df -P "$BACKUP_DIR" | tail -1 | awk '{print $4}')
if [ "$DISK_AVAIL" -lt 1048576 ]; then
  echo "⚠️ Insufficient disk space, skipping backup"
  exit 1
fi

# Execute backup
pg_dump -U postgres "$DB_NAME" | gzip > "$BACKUP_FILE"

# Verify backup integrity
if gunzip -t "$BACKUP_FILE" 2>/dev/null; then
  FILE_SIZE=$(stat -c%s "$BACKUP_FILE")
  echo "✅ Backup successful: ${BACKUP_FILE} (${FILE_SIZE} bytes)"
else
  echo "❌ Backup file is corrupted!"
  exit 1
fi

# AI validation: analyze backup file structure
ANALYSIS=$(docker run --rm --network host \
  ollama/ollama run qwen2.5:7b "Below is information about a PostgreSQL backup file. Please analyze:
Filename: $(basename "$BACKUP_FILE")
File size: $((FILE_SIZE / 1024 / 1024)) MB
Please assess whether this backup file is trustworthy, and outline the general steps for restoring it to another environment.")

echo "$ANALYSIS" >> /var/log/backup-validation.log

# Clean up old backups older than 7 days
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +7 -delete

# Upload to object storage (optional)
# aws s3 cp "$BACKUP_FILE" "s3://my-backup-bucket/postgresql/"

#!/bin/bash
# restore-test.sh — Automated restore drill

LATEST_BACKUP=$(ls -t /backups/postgresql/*.sql.gz | head -1)

# Test restore in temporary container
docker run --rm -v "${LATEST_BACKUP}:/backup.sql.gz" \
  --network host postgres:16 \
  bash -c "zcat /backup.sql.gz | psql -U postgres -tc 'SELECT 1' 2>&1"

if [ $? -eq 0 ]; then
  echo "✅ Backup restore test passed"
else
  echo "❌ Backup restore test FAILED! Immediate action required"
  bash /root/scripts/send-alert.sh "Backup restore test failed: ${LATEST_BACKUP}" "critical"
fi

#!/bin/bash
# auto-heal.sh — Automated fixes for common database issues

DIAGNOSE() {
  local ISSUE="$1"
  docker run --rm --network host \
    ollama/ollama run qwen2.5:7b "The VPS PostgreSQL database is facing this issue. Provide specific repair commands:
Issue: ${ISSUE}
Output only executable shell/SQL commands, one per line, with no explanation." 2>/dev/null
}

# Scenario 1: Connection pool exhausted
CONNECTION_COUNT=$(psql -U postgres -t -c "SELECT count(*) FROM pg_stat_activity;" 2>/dev/null)
MAX_CONNECTIONS=$(psql -U postgres -t -c "SHOW max_connections;" 2>/dev/null)
if [ "${CONNECTION_COUNT:-0}" -gt "$((MAX_CONNECTIONS * 85 / 100))" ]; then
  echo "🔧 Connection count too high, performing emergency cleanup..."
  # Terminate idle connections over 10 minutes
  psql -U postgres -c "
    SELECT pg_terminate_backend(pid) 
    FROM pg_stat_activity 
    WHERE state = 'idle' 
      AND state_change < now() - interval '10 minutes'
      AND pid != pg_backend_pid();
  " 2>/dev/null
  bash /root/scripts/send-alert.sh "Connection count too high — idle connections cleaned" "warning"
fi

# Scenario 2: Severe table bloat
DEAD_TUP_RATIO=$(psql -U postgres -t -c "
  SELECT max(n_dead_tup::numeric / GREATEST(n_live_tup + n_dead_tup, 1)) * 100
  FROM pg_stat_user_tables;
" 2>/dev/null | tr -d ' ')
if [ "${DEAD_TUP_RATIO:-0}" -gt 30 ]; then
  echo "🔧 Severe table bloat, running VACUUM ANALYZE..."
  psql -U postgres -c "VACUUM ANALYZE;" 2>/dev/null
  bash /root/scripts/send-alert.sh "Table bloat auto-repaired with VACUUM" "info"
fi

# Scenario 3: Low disk space (>90% used)
DISK_USAGE=$(df -P /var/lib/postgresql 2>/dev/null | tail -1 | awk '{print $5}' | tr -d '%')
if [ "${DISK_USAGE:-0}" -gt 90 ]; then
  echo "🔧 Low disk space, cleaning old WAL files and logs..."
  find /var/log/postgresql -name "*.log" -mtime +7 -delete 2>/dev/null
  psql -U postgres -c "CHECKPOINT;" 2>/dev/null
  bash /root/scripts/send-alert.sh "Disk space alert: ${DISK_USAGE}% used" "critical"
fi

version: '3.8'

services:
  # Monitoring & metrics collection
  db-monitor:
    image: python:3.11-slim
    volumes:
      - ./scripts:/scripts
      - ./logs:/logs
    command: bash -c "pip install requests && while true; do 
      bash /scripts/collect-db-metrics.sh >> /logs/metrics.log; 
      sleep 300; done"
    restart: unless-stopped

  # LLM inference engine
  llm-analyzer:
    image: ollama/ollama
    ports:
      - "11434:11434"
    volumes:
      - ollama-data:/root/.ollama
    restart: unless-stopped

volumes:
  ollama-data: