Database Migration

Lenses 5 to 6 Migration Guide

Overview

This guide covers the migration process from Lenses 5 to Lenses 6. The migration path depends on your current database setup.

Migration Paths Available:

• PostgreSQL users: In-place upgrade with downtime
• H2 users: Migration to PostgreSQL required {% endhint %}

Prerequisites

Before starting the migration:

• Full backup of your current Lenses 5 installation
• Lenses 6 Agent binaries downloaded
• Maintenance window scheduled
• PostgreSQL 12+ installed (for H2 users)

Important: Always test the migration process in a staging environment before applying to production.

Migration Scenarios

Scenario 1: PostgreSQL In-Place Upgrade

If you're already using PostgreSQL with Lenses 5, you can perform an in-place upgrade.

Advantages: Simpler process, data remains in place

Downtime: 15-30 minutes typically

Step-by-Step Process

1

Backup

# Create a full database backup
pg_dump -h localhost -U lenses_user -d lenses_db > lenses_v5_backup_$(date +%Y%m%d).sql

# Verify backup
pg_restore --list lenses_v5_backup_*.sql | head -20

2

Stop Lenses 5

3

Deploy & Start Lenses Agent

Instruction on how to Deploy & Start Lenses Agent can be found under:

• Via HelmDeploying an Agent
• Via Docker Deploying an Agent
• Via Archive Deploying an Agent

4

Start Lenses Agent

Scenario 2: H2 to PostgreSQL Migration

For users with H2 embedded database, migration to PostgreSQL is required.

{% hint style="danger" %}

Critical: H2 embedded database is not supported in Lenses 6. Migration to PostgreSQL is mandatory. {% endhint %}

Migration Options

{% tabs %} {% tab title="Option A: Export/Import" %}

Export/Import Method (Recommended)

Best for: Small to medium installations (<100GB data)

1. Prepare PostgreSQL

sql

-- Create database and user
CREATE DATABASE lenses_v6;
CREATE USER lenses_user WITH ENCRYPTED PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON DATABASE lenses_v6 TO lenses_user;

-- Enable required extensions
\c lenses_v6
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

2. Export H2 Data

bash

#!/bin/bash
# export-h2-data.sh

H2_JAR="/opt/lenses/lib/h2-1.4.200.jar"
H2_DB="/opt/lenses/storage/db/lenses"
EXPORT_DIR="/tmp/lenses-export"

mkdir -p $EXPORT_DIR

# Export schema and data
java -cp $H2_JAR org.h2.tools.Script \
  -url "jdbc:h2:$H2_DB;MODE=PostgreSQL" \
  -user sa \
  -script $EXPORT_DIR/lenses_export.sql \
  -options DROP

echo "Export completed: $EXPORT_DIR/lenses_export.sql"

3. Transform H2 to PostgreSQL

python

#!/usr/bin/env python3
# transform-h2-to-pg.py

import re
import sys

def transform_sql(input_file, output_file):
    """Transform H2 SQL to PostgreSQL compatible SQL"""
    
    with open(input_file, 'r') as f:
        content = f.read()
    
    # Transformation rules
    replacements = [
        # Data types
        (r'\bTINYINT\b', 'SMALLINT'),
        (r'\bDATETIME\b', 'TIMESTAMP'),
        (r'\bAUTO_INCREMENT\b', 'SERIAL'),
        (r'\bCLOB\b', 'TEXT'),
        (r'\bBLOB\b', 'BYTEA'),
        
        # Syntax
        (r'`([^`]+)`', r'"\1"'),  # Backticks to quotes
        (r'IF NOT EXISTS', ''),    # Remove IF NOT EXISTS for some statements
        
        # Functions
        (r'\bRAND\(\)', 'RANDOM()'),
        (r'\bNOW\(\)', 'CURRENT_TIMESTAMP'),
    ]
    
    for pattern, replacement in replacements:
        content = re.sub(pattern, replacement, content, flags=re.IGNORECASE)
    
    with open(output_file, 'w') as f:
        f.write(content)
    
    print(f"Transformation complete: {output_file}")

if __name__ == "__main__":
    transform_sql(sys.argv[1], sys.argv[2])

4. Import to PostgreSQL

bash

# Import the transformed data
PGPASSWORD=secure_password psql \
  -h localhost \
  -U lenses_user \
  -d lenses_v6 \
  -f /tmp/lenses-export/lenses_postgres.sql

# Verify import
echo "SELECT table_name FROM information_schema.tables WHERE table_schema='public';" | \
  psql -h localhost -U lenses_user -d lenses_v6

{% endtab %}

{% tab title="Option B: Migration Tool" %}

Automated Migration Tool

Best for: Large installations, minimal downtime required

Setup Migration Environment

yaml

# docker-compose.yml
version: '3.8'

services:
  postgres:
    image: postgres:14-alpine
    environment:
      POSTGRES_DB: lenses_v6
      POSTGRES_USER: lenses
      POSTGRES_PASSWORD: ${PG_PASSWORD}
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U lenses"]
      interval: 10s
      timeout: 5s
      retries: 5

  migrator:
    image: lenses/migrator:latest
    environment:
      SOURCE_DB: "jdbc:h2:/data/lenses;MODE=PostgreSQL"
      TARGET_DB: "jdbc:postgresql://postgres:5432/lenses_v6"
      TARGET_USER: lenses
      TARGET_PASSWORD: ${PG_PASSWORD}
      BATCH_SIZE: 1000
      PARALLEL_THREADS: 4
    volumes:
      - /opt/lenses/storage/db:/data:ro
    depends_on:
      postgres:
        condition: service_healthy

volumes:
  postgres_data:

Migration Configuration

yaml

# migration-config.yml
migration:
  source:
    type: h2
    path: /opt/lenses/storage/db/lenses
    
  target:
    type: postgresql
    host: localhost
    port: 5432
    database: lenses_v6
    schema: public
    
  tables:
    # Core tables (migrate first)
    - name: users
      batch_size: 100
      
    - name: roles
      batch_size: 100
      
    - name: permissions
      batch_size: 500
      
    # Configuration tables
    - name: connections
      batch_size: 50
      transform:
        - field: config
          type: json_to_jsonb
          
    - name: topics
      batch_size: 1000
      
    - name: schemas
      batch_size: 500
      
    # Data tables (can be parallelized)
    - name: audit_logs
      batch_size: 10000
      parallel: true
      
    - name: metrics
      batch_size: 10000
      parallel: true
      partitioned_by: created_at
      
  validation:
    enabled: true
    check_row_counts: true
    check_data_samples: true
    sample_size: 100

Run Migration

bash

#!/bin/bash
# run-migration.sh

set -e

echo "Starting Lenses H2 to PostgreSQL migration..."

# Run with docker-compose
docker-compose up -d postgres
sleep 10  # Wait for PostgreSQL to be ready

docker-compose run --rm migrator

# Validate migration
docker-compose run --rm migrator validate

echo "Migration completed successfully!"

{% endtab %}

{% tab title="Option C: Script" %}

Comprehensive Migration Script

Best for: Full control, custom requirements

bash

#!/bin/bash
# lenses-migrate.sh

set -euo pipefail

#############################################
# Configuration
#############################################
readonly SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
readonly TIMESTAMP=$(date +%Y%m%d_%H%M%S)
readonly LOG_FILE="/var/log/lenses-migration_${TIMESTAMP}.log"

# Source configuration
H2_DB_PATH="${H2_DB_PATH:-/opt/lenses/storage/db/lenses}"
PG_HOST="${PG_HOST:-localhost}"
PG_PORT="${PG_PORT:-5432}"
PG_DB="${PG_DB:-lenses_v6}"
PG_USER="${PG_USER:-lenses}"
PG_PASSWORD="${PG_PASSWORD:-}"
BACKUP_DIR="${BACKUP_DIR:-/backup/lenses-migration/${TIMESTAMP}}"

#############################################
# Functions
#############################################
log() {
    local level=$1
    shift
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] [$level] $*" | tee -a "$LOG_FILE"
}

error_exit() {
    log "ERROR" "$1"
    exit 1
}

check_prerequisites() {
    log "INFO" "Checking prerequisites..."
    
    # Check if running as appropriate user
    if [[ $EUID -eq 0 ]]; then
        log "WARN" "Running as root, this may cause permission issues"
    fi
    
    # Check H2 database exists
    if [[ ! -f "${H2_DB_PATH}.mv.db" ]]; then
        error_exit "H2 database not found at ${H2_DB_PATH}"
    fi
    
    # Check PostgreSQL connectivity
    PGPASSWORD=$PG_PASSWORD psql -h $PG_HOST -p $PG_PORT -U $PG_USER \
        -d postgres -c "SELECT 1" > /dev/null 2>&1 || \
        error_exit "Cannot connect to PostgreSQL"
    
    # Check required tools
    command -v java >/dev/null 2>&1 || error_exit "Java is required"
    command -v psql >/dev/null 2>&1 || error_exit "psql is required"
    command -v python3 >/dev/null 2>&1 || error_exit "Python 3 is required"
    
    log "INFO" "Prerequisites check passed"
}

create_backup() {
    log "INFO" "Creating backup of H2 database..."
    
    mkdir -p "$BACKUP_DIR"
    cp -r "${H2_DB_PATH}"* "$BACKUP_DIR/"
    
    # Create checksum for validation
    find "$BACKUP_DIR" -type f -exec md5sum {} \; > "$BACKUP_DIR/checksums.md5"
    
    log "INFO" "Backup created at $BACKUP_DIR"
}

export_h2_data() {
    log "INFO" "Exporting data from H2..."
    
    local export_file="${BACKUP_DIR}/lenses_export.sql"
    
    java -cp "/opt/lenses/lib/h2*.jar" org.h2.tools.Script \
        -url "jdbc:h2:${H2_DB_PATH}" \
        -user sa \
        -script "$export_file" \
        -options DROP COMPRESSION ZIP
    
    if [[ ! -f "$export_file" ]]; then
        error_exit "Failed to export H2 data"
    fi
    
    log "INFO" "H2 data exported to $export_file"
}

transform_data() {
    log "INFO" "Transforming data for PostgreSQL compatibility..."
    
    python3 "${SCRIPT_DIR}/transform-h2-to-pg.py" \
        "${BACKUP_DIR}/lenses_export.sql" \
        "${BACKUP_DIR}/lenses_postgres.sql"
    
    log "INFO" "Data transformation completed"
}

import_to_postgres() {
    log "INFO" "Importing data to PostgreSQL..."
    
    # Create database if it doesn't exist
    PGPASSWORD=$PG_PASSWORD psql -h $PG_HOST -p $PG_PORT -U $PG_USER \
        -d postgres -c "CREATE DATABASE IF NOT EXISTS $PG_DB"
    
    # Import data
    PGPASSWORD=$PG_PASSWORD psql -h $PG_HOST -p $PG_PORT -U $PG_USER \
        -d $PG_DB -f "${BACKUP_DIR}/lenses_postgres.sql" \
        > "${BACKUP_DIR}/import.log" 2>&1
    
    if [[ $? -ne 0 ]]; then
        error_exit "Failed to import data to PostgreSQL. Check ${BACKUP_DIR}/import.log"
    fi
    
    log "INFO" "Data imported successfully"
}

validate_migration() {
    log "INFO" "Validating migration..."
    
    local validation_passed=true
    
    # Get list of tables
    tables=$(PGPASSWORD=$PG_PASSWORD psql -h $PG_HOST -p $PG_PORT -U $PG_USER \
        -d $PG_DB -t -c "SELECT tablename FROM pg_tables WHERE schemaname='public'")
    
    for table in $tables; do
        # Check if table has data
        count=$(PGPASSWORD=$PG_PASSWORD psql -h $PG_HOST -p $PG_PORT -U $PG_USER \
            -d $PG_DB -t -c "SELECT COUNT(*) FROM $table")
        
        if [[ $count -eq 0 ]]; then
            log "WARN" "Table $table is empty"
            validation_passed=false
        else
            log "INFO" "Table $table has $count rows"
        fi
    done
    
    if [[ "$validation_passed" == "false" ]]; then
        log "WARN" "Validation completed with warnings"
    else
        log "INFO" "Validation completed successfully"
    fi
}

#############################################
# Main Execution
#############################################
main() {
    log "INFO" "Starting Lenses 5 to 6 migration (H2 to PostgreSQL)"
    
    check_prerequisites
    create_backup
    export_h2_data
    transform_data
    import_to_postgres
    validate_migration
    
    log "INFO" "Migration completed successfully!"
    log "INFO" "Backup location: $BACKUP_DIR"
    log "INFO" "Next steps:"
    log "INFO" "  1. Stop Lenses 5 service"
    log "INFO" "  2. Update Lenses 6 configuration to use PostgreSQL"
    log "INFO" "  3. Start Lenses 6 Agent"
    log "INFO" "  4. Verify functionality"
}

# Run main function
main "$@"

{% endtab %} {% endtabs %}

Post-Migration Steps

1. Configuration Update

Update your Lenses 6 configuration to use PostgreSQL:

hocon

# lenses.conf
lenses {
  # License configuration
  license.file = "/etc/lenses/license.json"
  
  # Storage configuration
  storage {
    type = postgres
    
    postgres {
      host = ${?LENSES_STORAGE_POSTGRES_HOST}
      port = ${?LENSES_STORAGE_POSTGRES_PORT}
      database = ${?LENSES_STORAGE_POSTGRES_DATABASE}
      username = ${?LENSES_STORAGE_POSTGRES_USERNAME}
      password = ${?LENSES_STORAGE_POSTGRES_PASSWORD}
      
      # Connection pool settings
      properties {
        connectionTimeout = 30000
        maximumPoolSize = 10
      }
    }
  }
}

2. Validation Checklist

{% hint style="info" %} Complete all validation steps before considering the migration successful. {% endhint %}

Data Validation

• User accounts exist and can log in
• Roles and permissions are preserved
• Connection configurations work
• Topics and schemas are visible
• Alert definitions are present
• Audit logs are retained

Functional Validation

• Kafka cluster connections active
• Schema Registry accessible
• SQL processors execute correctly
• Alerts trigger as expected
• Data governance policies enforced

Performance Validation

• UI responsiveness acceptable
• Query performance comparable or better
• No excessive database connections
• Memory usage within limits

3. Monitoring

Set up monitoring for the new installation:

yaml

# prometheus.yml
scrape_configs:
  - job_name: 'lenses'
    static_configs:
      - targets: ['localhost:9991']
    metrics_path: '/metrics'

Rollback Procedure

{% hint style="danger" %} Important: Keep Lenses 5 installation intact until migration is fully validated. {% endhint %}

PostgreSQL Users

bash

# Stop Lenses 6
systemctl stop lenses6

# Restore database
PGPASSWORD=$PG_PASSWORD pg_restore -h localhost -U lenses_user -d lenses_db \
  --clean --if-exists lenses_v5_backup.sql

# Start Lenses 5
systemctl start lenses5

H2 Migration Rollback

bash

# Simply restart Lenses 5 (H2 data untouched)
systemctl start lenses5

# Remove PostgreSQL data if needed
PGPASSWORD=$PG_PASSWORD psql -h localhost -U lenses_user \
  -c "DROP DATABASE IF EXISTS lenses_v6"

Troubleshooting

Common Issues

{% tabs %} {% tab title="Connection Issues" %} Problem: Cannot connect to PostgreSQL

bash

# Check PostgreSQL is running
systemctl status postgresql

# Test connection
psql -h localhost -U lenses_user -d lenses_v6

# Check pg_hba.conf
grep lenses /etc/postgresql/*/main/pg_hba.conf

Solution: Ensure PostgreSQL is configured to accept connections:

bash

# pg_hba.conf
host    lenses_v6    lenses_user    127.0.0.1/32    md5
host    lenses_v6    lenses_user    ::1/128         md5

{% endtab %}

{% tab title="Migration Errors" %} Problem: Data type incompatibilities

sql

-- Common fixes
ALTER TABLE your_table ALTER COLUMN your_column TYPE TEXT;
ALTER TABLE your_table ALTER COLUMN date_column TYPE TIMESTAMP;

Problem: Foreign key violations

sql

-- Disable FK checks during import
SET session_replication_role = 'replica';
-- Run import
SET session_replication_role = 'origin';

{% endtab %}

{% tab title="Performance Issues" %} Problem: Slow queries after migration

sql

-- Update statistics
ANALYZE;

-- Rebuild indexes
REINDEX DATABASE lenses_v6;

-- Check missing indexes
SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE tablename IN ('topics', 'connections', 'users')
ORDER BY n_distinct DESC;

{% endtab %} {% endtabs %}

Support

{% hint style="warning" %} If you encounter issues not covered in this guide, please contact Lenses support with:

• Migration logs
• Error messages
• Lenses version numbers (source and target)
• Database versions {% endhint %}

Related Documentation

• Lenses 6 Installation Guide
• PostgreSQL Best Practices
• Backup and Recovery
• Performance Tuning

\