HeliosDB Migration Toolkit Guide

HeliosDB Migration Toolkit Guide

Complete guide for migrating databases from Oracle, PostgreSQL, SQL Server, and MySQL to HeliosDB.

Last Updated: November 24, 2025 Status: Production Ready Version: 1.0.0

---

Table of Contents

1. Overview
2. Architecture
3. Installation
4. Quick Start
5. Schema Migration
6. Data Migration
7. Query Translation
8. Validation
9. Performance Tuning
10. Troubleshooting
11. Best Practices
12. API Reference

---

Overview

The HeliosDB Migration Toolkit provides comprehensive tools for database migration:

Features

Feature	Coverage	Description
Schema Migration	30%	Convert DDL from Oracle, PostgreSQL, SQL Server, MySQL
Data Migration	30%	Bulk transfer, incremental sync, CDC-based replication
Query Translation	20%	SQL dialect conversion and optimization
Validation	20%	Schema, data, query, and performance validation

Supported Sources

• Oracle 11g R2, 12c, 18c, 19c, 21c, 23ai (55% compatibility)
• PostgreSQL 9.6+ (95%+ compatibility)
• SQL Server 2012, 2014, 2016, 2017, 2019, 2022 (60-70% compatibility)
• MySQL 5.7, 8.0, 8.1 (65-75% compatibility)

---

Architecture

┌─────────────────────────────────────────────────────────┐
│         HeliosDB Migration Toolkit                      │
├─────────────────────────────────────────────────────────┤
│                                                         │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐ │
│  │   Schema     │  │     Data     │  │    Query     │ │
│  │  Conversion  │  │  Migration   │  │ Translation  │ │
│  └──────────────┘  └──────────────┘  └──────────────┘ │
│         │                 │                  │         │
│         ├─────────────────┼──────────────────┤         │
│         │                 │                  │         │
│  ┌──────▼─────────────────▼──────────────────▼──────┐ │
│  │           Validation & Verification             │ │
│  └─────────────────────────────────────────────────┘ │
│                                                         │
└─────────────────────────────────────────────────────────┘
                           │
                           ▼
               ┌───────────────────────┐
               │     HeliosDB          │
               └───────────────────────┘

Component Breakdown

1. Schema Converter (30% of effort)

• Oracle Converter: Handles PL/SQL, DBMS packages, sequences
• PostgreSQL Converter: Minimal conversion (high compatibility)
• SQL Server Converter: Converts T-SQL, SQL Server Agent jobs
• MySQL Converter: Handles storage engines, AUTO_INCREMENT

2. Data Migrator (30% of effort)

• Bulk Transfer: Snapshot-based, high-speed transfer
• Incremental Sync: Multi-round catch-up synchronization
• CDC Replication: Change Data Capture for real-time sync
• Parallel Execution: Multi-threaded for performance

3. Query Translator (20% of effort)

• Dialect Translation: Automatic SQL dialect conversion
• Function Mapping: Database-specific function conversion
• Syntax Optimization: Query optimization for HeliosDB
• Compatibility Analysis: Feature support analysis

4. Validator (20% of effort)

• Schema Validator: Verify structure matches
• Data Validator: Check row counts, checksums
• Query Validator: Ensure result equivalence
• Performance Validator: Detect regressions

---

Installation

Prerequisites

• Rust 1.70+ (for building from source)
• Access to source and target databases
• Network connectivity between systems

Build from Source

cd tools/migration
cargo build --release

Binaries will be in target/release/:

• heliosdb-migrate
• heliosdb-schema-convert
• heliosdb-validate

Install Globally

cargo install --path tools/migration

Verify Installation

heliosdb-migrate --version
heliosdb-schema-convert --version
heliosdb-validate --version

---

Quick Start

1. Create Configuration

migration.toml

[source]
db_type = "Oracle"
host = "oracle.example.com"
port = 1521
database = "ORCL"
username = "source_user"
password = "source_password"

[target]
db_type = "HeliosDB"
host = "heliosdb.example.com"
port = 5432
database = "target_db"
username = "helios_user"
password = "helios_password"

strategy = "Incremental"
batch_size = 10000
parallelism = 4

2. Convert Schema

heliosdb-schema-convert \
  --source oracle \
  --input oracle_schema.sql \
  --output heliosdb_schema.sql

3. Migrate Data

heliosdb-migrate data \
  --tables "users,orders,products" \
  --strategy incremental \
  --config migration.toml

4. Validate

heliosdb-validate --validation-type all

---

Schema Migration

Oracle to HeliosDB

Compatibility: 55%

Supported:

• DDL (CREATE TABLE, ALTER TABLE, DROP TABLE)
• Data types (NUMBER, VARCHAR2, DATE, TIMESTAMP, CLOB, BLOB)
• Constraints (PK, FK, CHECK, UNIQUE)
• Indexes (B-Tree, Bitmap partial)
• Sequences
• Views (standard and materialized)
• PL/SQL (procedures, functions, packages - 70%)

Conversion Example:

-- Oracle DDL
CREATE TABLE employees (
  employee_id    NUMBER(6) PRIMARY KEY,
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25) NOT NULL,
  email          VARCHAR2(25) UNIQUE,
  hire_date      DATE NOT NULL,
  salary         NUMBER(8,2),
  department_id  NUMBER(4)
);

CREATE SEQUENCE emp_seq START WITH 1000;

Converts to:

-- HeliosDB DDL
CREATE TABLE employees (
  employee_id    NUMERIC(6) PRIMARY KEY,
  first_name     VARCHAR(20),
  last_name      VARCHAR(25) NOT NULL,
  email          VARCHAR(25) UNIQUE,
  hire_date      TIMESTAMP NOT NULL,
  salary         NUMERIC(8,2),
  department_id  NUMERIC(4)
);

CREATE SEQUENCE emp_seq START WITH 1000;

Data Type Mapping Reference

See tools/migration/src/schema/mapping.rs for complete mapping tables.

Key Mappings:

Oracle	HeliosDB	Notes
VARCHAR2(n)	VARCHAR(n)	Direct
NUMBER(p,s)	DECIMAL(p,s)	Direct
DATE	TIMESTAMP	Oracle DATE includes time
CLOB	TEXT	Large text
BLOB	BYTEA	Binary data

---

Data Migration

Strategy Selection

Choose migration strategy based on requirements:

Strategy	Downtime	Complexity	Use Case
Bulk	1-24 hours	Low	Dev/staging, scheduled maintenance
Incremental	<5 minutes	Medium	Most production systems
CDC	None	High	Mission-critical, 24/7 systems

Bulk Transfer

heliosdb-migrate data \
  --strategy bulk \
  --tables "users,orders,products" \
  --config migration.toml

Process:

1. Take snapshot
2. Export all data
3. Transfer to target
4. Load into target
5. Validate

Performance: 10,000-100,000 rows/sec depending on hardware

Incremental Sync

heliosdb-migrate data \
  --strategy incremental \
  --tables "users,orders,products" \
  --config migration.toml

Process:

1. Initial bulk load (90% of data)
2. Incremental sync round 1 (8% catch-up)
3. Incremental sync round 2 (1.5% catch-up)
4. Final sync (<0.5% lag)
5. Switchover

Downtime: 2-5 minutes for final switchover

CDC-based Migration

heliosdb-migrate data \
  --strategy cdc \
  --tables "users,orders,products" \
  --config migration.toml

Requirements:

• Source must support CDC
• Oracle: Archive log mode enabled
• SQL Server: CDC enabled
• PostgreSQL: Logical replication
• MySQL: Binlog enabled

Process:

1. Enable CDC on source
2. Take initial snapshot
3. Continuous replication
4. Gradual traffic switch (10% → 50% → 100%)

Downtime: None

---

Query Translation

Function Mapping

Oracle to HeliosDB

Oracle Function	HeliosDB Equivalent
SYSDATE	CURRENT_TIMESTAMP
NVL(a, b)	COALESCE(a, b)
DECODE()	CASE WHEN … END
SUBSTR()	SUBSTRING()
INSTR()	POSITION()
ROWNUM	ROW_NUMBER() OVER () or LIMIT

SQL Server to HeliosDB

SQL Server Function	HeliosDB Equivalent
GETDATE()	CURRENT_TIMESTAMP
ISNULL(a, b)	COALESCE(a, b)
LEN()	LENGTH()
TOP N	LIMIT N

MySQL to HeliosDB

MySQL Function	HeliosDB Equivalent
NOW()	CURRENT_TIMESTAMP
IFNULL(a, b)	COALESCE(a, b)
LIMIT offset, count	LIMIT count OFFSET offset

Translation Example

heliosdb-migrate query \
  --source oracle \
  --input oracle_queries.sql \
  --output heliosdb_queries.sql

Input:

SELECT SYSDATE, NVL(salary, 0)
FROM employees
WHERE ROWNUM <= 10;

Output:

SELECT CURRENT_TIMESTAMP, COALESCE(salary, 0)
FROM employees
LIMIT 10;

---

Validation

Schema Validation

heliosdb-validate --validation-type schema --verbose

Checks:

• Table count matches
• Column definitions match
• Data types compatible
• Constraints present
• Indexes created

Data Validation

heliosdb-validate --validation-type data --verbose

Checks:

• Row count matches
• Sample data equivalent
• Checksums match
• No missing records
• Foreign key integrity

Query Validation

heliosdb-validate --validation-type query --verbose

Checks:

• Translated queries execute
• Result sets match
• Row counts equal
• Column order preserved

Performance Validation

heliosdb-validate --validation-type performance --verbose

Checks:

• Query execution times
• Throughput comparison
• No significant regressions (>50% slower)

---

Performance Tuning

Optimize Bulk Transfer

batch_size = 50000      # Increase batch size
parallelism = 8         # More parallel workers

Optimize Network

• Use compression (if supported)
• Increase TCP buffer sizes
• Use dedicated network connection
• Co-locate migration tool near source/target

Optimize Source Database

• Disable triggers temporarily
• Disable constraints during load
• Increase checkpoint segments
• Disable logging (if safe)

Optimize Target Database

• Pre-create indexes after data load (not before)
• Increase shared_buffers
• Increase work_mem
• Disable autovacuum during migration

---

Troubleshooting

See README.md for common issues and solutions.

---

Best Practices

1. Test in Non-Production First
2. Use Configuration Files (not command-line passwords)
3. Monitor Progress (use separate terminal)
4. Keep Backups
5. Plan Rollback Procedures
6. Gradual Cutover (for production)
7. Document Everything

---

API Reference

Library Usage

use heliosdb_migration_toolkit::*;

// Convert schema
let converter = schema::oracle::OracleConverter::new();
let heliosdb_ddl = converter.convert(&oracle_ddl)?;

// Migrate data
let config = MigrationConfig::default();
let migrator = data::DataMigrator::new(config);
let results = migrator.migrate(tables).await?;

// Validate
let validator = validation::ValidationSuite::new();
let results = validator.validate_all().await;

---

Support

• Documentation: https://docs.heliosdb.com/migration
• Issues: https://github.com/heliosdb/heliosdb/issues
• Community: Discord/Slack

---

Related Documentation

• Oracle Migration Guide
• Cassandra Migration Guide
• MongoDB Migration Guide
• User Guide Index