# 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.

{% hint style="info" %}
**Migration Paths Available:**

* **PostgreSQL users**: In-place upgrade with downtime
* **H2 users**: Migration to PostgreSQL required {% endhint %}
  {% 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)

{% hint style="warning" %}
**Important:** Always test the migration process in a staging environment before applying to production.
{% endhint %}

### Migration Scenarios

#### Scenario 1: PostgreSQL In-Place Upgrade

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

{% hint style="success" %}
**Advantages**: Simpler process, data remains in place

**Downtime**: 15-30 minutes typically
{% endhint %}

**Step-by-Step Process**

{% stepper %}
{% step %}

#### Backup

```bash
# 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
```

{% endstep %}

{% step %}

#### Stop Lenses 5

{% endstep %}

{% step %}

#### Deploy & Start Lenses Agent

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

* Via Helm[Deploying an Agent](/latest/devx/6.1/deployment/installation/helm/agent.md)
* Via Docker [Deploying an Agent](/latest/devx/6.1/deployment/installation/docker/agent.md)
* Via Archive [Deploying an Agent](/latest/devx/6.1/deployment/installation/linux/agent.md)
  {% endstep %}

{% step %}

#### Start Lenses Agent

{% endstep %}
{% endstepper %}

#### 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

```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

```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

```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

```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

```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

```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

```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

```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

```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

```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

```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

```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

```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

```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

```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

```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

```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](https://claude.ai/installation/lenses-6.md)
* [PostgreSQL Best Practices](https://claude.ai/database/postgresql-config.md)
* [Backup and Recovery](https://claude.ai/operations/backup-recovery.md)
* [Performance Tuning](https://claude.ai/operations/performance-tuning.md)

\\


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.lenses.io/latest/devx/6.1/deployment/upgrade-to-lenses-6/database-migration.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.