---
title: "HeliosDB Migration Toolkit Guide"
---

# HeliosDB Migration Toolkit Guide

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

**Version**: 1.0.0

---

## Table of Contents

1. [Overview](#overview)
2. [Architecture](#architecture)
3. [Installation](#installation)
4. [Quick Start](#quick-start)
5. [Schema Migration](#schema-migration)
6. [Data Migration](#data-migration)
7. [Query Translation](#query-translation)
8. [Validation](#validation)
9. [Performance Tuning](#performance-tuning)
10. [Best Practices](#best-practices)

---

## Overview

The HeliosDB Migration Toolkit provides comprehensive tools for database migration:

### Features

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

### Supported Sources

- **Oracle** 11g R2, 12c, 18c, 19c, 21c, 23ai (see [Oracle compatibility for in-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/) for what runs unchanged and how to assess an application)
- **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

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

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

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

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

---

## Installation

The toolkit provides the following binaries:
- `heliosdb-migrate`
- `heliosdb-schema-convert`
- `heliosdb-validate`

### Prerequisites

- Access to source and target databases
- Network connectivity between systems

### Verify Installation

```bash
heliosdb-migrate --version
heliosdb-schema-convert --version
heliosdb-validate --version
```

---

## Quick Start

### 1. Create Configuration

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

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

### 3. Migrate Data

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

### 4. Validate

```bash
heliosdb-validate --validation-type all
```

---

## Schema Migration

### Oracle to HeliosDB

**Compatibility**: core DDL, data types, constraints and indexes convert directly; PL/SQL needs review (see [Oracle compatibility for in-house applications](/docs/full/reference/protocols/oracle/inhouse_application_compatibility/))

**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; beta, review each object)

**Conversion Example**:

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

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

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

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

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

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

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

**Input**:
```sql
SELECT SYSDATE, NVL(salary, 0)
FROM employees
WHERE ROWNUM <= 10;
```

**Output**:
```sql
SELECT CURRENT_TIMESTAMP, COALESCE(salary, 0)
FROM employees
LIMIT 10;
```

---

## Validation

### Schema Validation

```bash
heliosdb-validate --validation-type schema --verbose
```

Checks:
- Table count matches
- Column definitions match
- Data types compatible
- Constraints present
- Indexes created

### Data Validation

```bash
heliosdb-validate --validation-type data --verbose
```

Checks:
- Row count matches
- Sample data equivalent
- Checksums match
- No missing records
- Foreign key integrity

### Query Validation

```bash
heliosdb-validate --validation-type query --verbose
```

Checks:
- Translated queries execute
- Result sets match
- Row counts equal
- Column order preserved

### Performance Validation

```bash
heliosdb-validate --validation-type performance --verbose
```

Checks:
- Query execution times
- Throughput comparison
- No significant regressions (>50% slower)

---

## Performance Tuning

### Optimize Bulk Transfer

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

---

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

---

## Support

- **Documentation**: https://www.heliosdb.com/docs/full/
- Support: [support@heliosdb.com](mailto:support@heliosdb.com)
- Community: [Discord](https://discord.gg/yTykuUrFXc)

---

## Related Documentation

- [Oracle Migration Guide](https://www.heliosdb.com/docs/full/guides/oracle_migration/)
- [Cassandra Migration Guide](https://www.heliosdb.com/docs/full/guides/cassandra_migration/)
- [MongoDB Migration Guide](https://www.heliosdb.com/docs/full/guides/mongodb_migration/)