---
title: "PostgreSQL to HeliosDB Migration Guide"
---

# PostgreSQL to HeliosDB Migration Guide

**Version:** 1.0

---

## Table of Contents

1. [Introduction](#introduction)
2. [Why Migrate from PostgreSQL to HeliosDB](#why-migrate-from-postgresql-to-heliosdb)
3. [Compatibility Overview](#compatibility-overview)
4. [Pre-Migration Checklist](#pre-migration-checklist)
5. [Step-by-Step Migration Process](#step-by-step-migration-process)
6. [Feature Mapping](#feature-mapping)
7. [Post-Migration Validation](#post-migration-validation)
8. [Performance Tuning After Migration](#performance-tuning-after-migration)
9. [Common Issues and Troubleshooting](#common-issues-and-troubleshooting)
10. [Rollback Procedures](#rollback-procedures)
11. [Appendix](#appendix)

---

## Introduction

This guide provides comprehensive instructions for migrating from PostgreSQL to HeliosDB. HeliosDB is designed as a drop-in replacement for PostgreSQL, offering 100% wire protocol compatibility while providing enhanced features for modern workloads including multi-model support, AI integration, and advanced distributed capabilities.

### Scope

This guide covers:

- Schema migration using standard PostgreSQL tools
- Data migration strategies for various database sizes
- Application connection string updates
- Feature mapping from PostgreSQL extensions to HeliosDB equivalents
- Post-migration validation and performance tuning
- Troubleshooting common migration issues

### Target Audience

- Database administrators migrating production PostgreSQL databases
- DevOps engineers planning database modernization
- Application developers updating connection configurations
- Architects evaluating HeliosDB for PostgreSQL replacement

---

## Why Migrate from PostgreSQL to HeliosDB

### Key Benefits

| Benefit | Description |
|---------|-------------|
| **100% Wire Protocol Compatibility** | Use existing PostgreSQL drivers, tools, and applications without modification |
| **Multi-Model Support** | Single database supporting relational, document, graph, time-series, and vector data |
| **Native AI Integration** | Built-in NL2SQL, vector search, and ML model inference |
| **HTAP Workloads** | Hybrid transactional/analytical processing in a single system |
| **Enhanced Performance** | Optimized storage engine with intelligent caching and SIMD acceleration |
| **Multi-Protocol Access** | Access data via PostgreSQL, MySQL, MongoDB, Redis, GraphQL, and REST APIs |
| **Cloud-Native Architecture** | Kubernetes-native deployment with auto-scaling and multi-region support |

### Migration Complexity: Low

Since HeliosDB implements 100% PostgreSQL wire protocol v3.0 compatibility, migration from PostgreSQL is among the simplest database migrations:

- **No schema changes required** in most cases
- **Standard pg_dump/pg_restore** tools work
- **Existing applications** connect without code changes
- **PostgreSQL client libraries** work without modification

---

## Compatibility Overview

### Wire Protocol Compatibility

| Component | Coverage | Availability |
|-----------|----------|--------|
| PostgreSQL Wire Protocol v3.0 | 100% | Supported |
| Extended Query Protocol | 100% | Supported |
| COPY Protocol | 100% | Supported |
| Replication Protocol | 100% | Planned (with clustering) |

### SQL Language Compatibility

| Feature Category | Coverage | Notes |
|-----------------|----------|-------|
| DDL (CREATE, ALTER, DROP) | 100% | All object types supported |
| DML (INSERT, UPDATE, DELETE, MERGE) | 100% | Including PostgreSQL 17 features |
| Queries (SELECT, CTEs, Window Functions) | 100% | Full SQL support |
| Transactions | 100% | MVCC, all isolation levels |
| JSON/JSONB | 100% | Including PostgreSQL 17 JSON_TABLE |
| Full-Text Search | 100% | tsvector, tsquery supported |

### PostgreSQL 17 Feature Support

| Feature | Result | Notes |
|---------|--------|-------|
| JSON_TABLE | Supported | Full SQL/JSON table conversion |
| MERGE with RETURNING | Supported | $action pseudo-column |
| COPY with ON_ERROR | Supported | stop/ignore modes |
| Incremental Backup | Supported | Block-level backups |
| Slot Failover | Supported | Logical replication HA |
| Vacuum 20x Memory Optimization | Supported | Enhanced via HeliosDB compaction |

### Client Driver Compatibility

All standard PostgreSQL client drivers work with HeliosDB:

| Language | Driver | Tested |
|----------|--------|--------|
| Python | psycopg2, psycopg3 | Yes |
| Node.js | node-postgres (pg) | Yes |
| Java | PostgreSQL JDBC | Yes |
| Go | lib/pq, pgx | Yes |
| Rust | rust-postgres, tokio-postgres | Yes |
|.NET | Npgsql | Yes |
| C/C++ | libpq | Yes |
| Ruby | pg gem | Yes |
| PHP | PDO_PGSQL | Yes |

### GUI Tool Compatibility

| Tool | Compatibility |
|------|--------------|
| psql | 100% |
| pgAdmin | 100% |
| DBeaver | 100% |
| DataGrip | 100% |
| pgcli | 100% |
| TablePlus | 100% |

---

## Pre-Migration Checklist

### Assessment Phase

- [ ] **Inventory your PostgreSQL installation**
  - PostgreSQL version (HeliosDB supports PostgreSQL 15, 16, 17 compatibility)
  - Total database size
  - Number of databases, schemas, tables
  - Extension usage

- [ ] **Document extensions in use**
  ```sql
  SELECT extname, extversion FROM pg_extension;
  ```

- [ ] **Identify stored procedures and functions**
  ```sql
  SELECT routine_schema, routine_name, routine_type
  FROM information_schema.routines
  WHERE routine_schema NOT IN ('pg_catalog', 'information_schema');
  ```

- [ ] **Catalog custom data types**
  ```sql
  SELECT typname, typtype FROM pg_type
  WHERE typnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');
  ```

- [ ] **Review foreign data wrappers**
  ```sql
  SELECT fdwname FROM pg_foreign_data_wrapper;
  ```

### Infrastructure Preparation

- [ ] **Provision HeliosDB instance**
  - Ensure adequate storage (1.2x PostgreSQL size recommended)
  - Configure memory (similar to PostgreSQL shared_buffers)
  - Set up network connectivity

- [ ] **Configure HeliosDB authentication**
  ```toml
  [postgresql.auth]
  method = "scram-sha-256" # Recommended
  hba_file = "/etc/heliosdb/pg_hba.conf"
  ```

- [ ] **Set up SSL/TLS** (if required)
  ```toml
  [postgresql.ssl]
  enabled = true
  mode = "prefer"
  cert_file = "/etc/heliosdb/server.crt"
  key_file = "/etc/heliosdb/server.key"
  ```

### Application Preparation

- [ ] **Inventory application connection strings**
- [ ] **Document ORM configurations**
- [ ] **Identify connection pooler usage** (PgBouncer, pgpool-II)
- [ ] **Review query patterns for unsupported extensions**

### Backup and Recovery

- [ ] **Create full PostgreSQL backup**
  ```bash
  pg_dumpall -h localhost -U postgres > full_backup.sql
  ```

- [ ] **Document replication topology** (if applicable)
- [ ] **Plan rollback procedure**
- [ ] **Schedule maintenance window**

---

## Step-by-Step Migration Process

### Phase 1: Schema Migration

#### Step 1.1: Export PostgreSQL Schema

```bash
# Export schema only (no data)
pg_dump -h localhost -U postgres -d mydatabase \
  --schema-only \
  --no-owner \
  --no-privileges \
  --no-tablespaces \
  -f schema.sql

# For all databases
pg_dumpall -h localhost -U postgres \
  --schema-only \
  --no-owner \
  --no-privileges \
  -f all_schemas.sql
```

#### Step 1.2: Review and Adjust Schema (if needed)

```bash
# Check for unsupported extensions
grep -E "CREATE EXTENSION" schema.sql

# Check for tablespace references
grep -E "TABLESPACE" schema.sql

# Check for large objects
grep -E "lo_create|lo_import" schema.sql
```

**Common adjustments:**

```sql
-- Remove unsupported extension references if needed
-- Most PostgreSQL extensions are supported natively in HeliosDB

-- Tablespaces map to HeliosDB storage tiers
-- Replace: TABLESPACE fast_storage
-- With: -- (HeliosDB handles storage optimization automatically)
```

#### Step 1.3: Import Schema to HeliosDB

```bash
# Create database in HeliosDB
psql -h heliosdb.host -U admin -c "CREATE DATABASE mydatabase;"

# Import schema
psql -h heliosdb.host -U admin -d mydatabase -f schema.sql

# Verify schema import
psql -h heliosdb.host -U admin -d mydatabase -c "\dt"
psql -h heliosdb.host -U admin -d mydatabase -c "\di"
```

---

### Phase 2: Data Migration

#### Option A: Using pg_dump/pg_restore (Recommended for < 100GB)

```bash
# Export data with pg_dump (custom format for parallel restore)
pg_dump -h postgres.host -U postgres -d mydatabase \
  --data-only \
  --format=custom \
  --compress=9 \
  -f data.dump

# Import to HeliosDB
pg_restore -h heliosdb.host -U admin -d mydatabase \
  --data-only \
  --jobs=4 \
  data.dump
```

#### Option B: Using COPY Command (For Large Tables)

```bash
# Export from PostgreSQL
psql -h postgres.host -U postgres -d mydatabase \
  -c "COPY large_table TO STDOUT WITH (FORMAT csv, HEADER true)" \
  > large_table.csv

# Import to HeliosDB (with PostgreSQL 17 ON_ERROR support)
psql -h heliosdb.host -U admin -d mydatabase \
  -c "COPY large_table FROM STDIN WITH (FORMAT csv, HEADER true, ON_ERROR ignore)" \
  < large_table.csv
```

#### Option C: Streaming Migration (Zero Downtime)

For production systems requiring minimal downtime:

```bash
# 1. Initial bulk copy
pg_dump -h postgres.host -U postgres -d mydatabase \
  --format=custom \
  | pg_restore -h heliosdb.host -U admin -d mydatabase

# 2. Set up logical replication from PostgreSQL
psql -h postgres.host -U postgres -d mydatabase -c "
  CREATE PUBLICATION helios_migration FOR ALL TABLES;
"

# 3. Create subscription in HeliosDB
psql -h heliosdb.host -U admin -d mydatabase -c "
  CREATE SUBSCRIPTION helios_sub
  CONNECTION 'host=postgres.host dbname=mydatabase user=replicator'
  PUBLICATION helios_migration;
"

# 4. Monitor replication lag
psql -h heliosdb.host -U admin -d mydatabase -c "
  SELECT * FROM pg_stat_subscription;
"

# 5. When lag reaches zero, switch applications
```

#### Option D: Parallel Data Migration Script

For very large databases, use parallel table exports:

```bash
#!/bin/bash
# parallel_migrate.sh

POSTGRES_HOST="postgres.host"
HELIOS_HOST="heliosdb.host"
DATABASE="mydatabase"
PARALLEL_JOBS=8

# Get list of tables
TABLES=$(psql -h $POSTGRES_HOST -U postgres -d $DATABASE -t -c "
  SELECT schemaname || '.' || tablename
  FROM pg_tables
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema');
")

# Export and import each table in parallel
echo "$TABLES" | xargs -P $PARALLEL_JOBS -I {} bash -c '
  TABLE="{}"
  echo "Migrating $TABLE..."
  psql -h '$POSTGRES_HOST' -U postgres -d '$DATABASE' \
    -c "COPY $TABLE TO STDOUT WITH (FORMAT binary)" | \
  psql -h '$HELIOS_HOST' -U admin -d '$DATABASE' \
    -c "COPY $TABLE FROM STDIN WITH (FORMAT binary)"
'
```

---

### Phase 3: Application Connection String Changes

#### Connection String Format

PostgreSQL and HeliosDB use identical connection string formats:

**Before (PostgreSQL):**
```
postgresql://user:password@postgres.host:5432/mydatabase
```

**After (HeliosDB):**
```
postgresql://user:password@heliosdb.host:5432/mydatabase
```

#### Language-Specific Examples

**Python (psycopg2/psycopg3):**
```python
# Before
conn = psycopg2.connect(
    host="postgres.host",
    port=5432,
    database="mydatabase",
    user="myuser",
    password="mypassword"
)

# After (only host changes)
conn = psycopg2.connect(
    host="heliosdb.host",
    port=5432,
    database="mydatabase",
    user="myuser",
    password="mypassword"
)
```

**Node.js (node-postgres):**
```javascript
// Before
const pool = new Pool({
    host: 'postgres.host',
    port: 5432,
    database: 'mydatabase',
    user: 'myuser',
    password: 'mypassword'
});

// After (only host changes)
const pool = new Pool({
    host: 'heliosdb.host',
    port: 5432,
    database: 'mydatabase',
    user: 'myuser',
    password: 'mypassword'
});
```

**Java (JDBC):**
```java
// Before
String url = "jdbc:postgresql://postgres.host:5432/mydatabase";

// After (only host changes)
String url = "jdbc:postgresql://heliosdb.host:5432/mydatabase";
```

**Go (lib/pq):**
```go
// Before
db, err := sql.Open("postgres",
    "host=postgres.host port=5432 user=myuser password=mypassword dbname=mydatabase")

// After (only host changes)
db, err := sql.Open("postgres",
    "host=heliosdb.host port=5432 user=myuser password=mypassword dbname=mydatabase")
```

**Environment Variables:**
```bash
# Before
export PGHOST=postgres.host

# After
export PGHOST=heliosdb.host
```

---

### Phase 4: Sequence and Identity Column Synchronization

After data migration, synchronize sequence values:

```sql
-- Generate sequence sync commands
SELECT 'SELECT setval(' || quote_literal(sequence_name) || ',
  (SELECT COALESCE(MAX(' || column_name || '), 1) FROM ' || table_name || '));'
FROM information_schema.columns
WHERE column_default LIKE 'nextval%';

-- Example output to execute:
SELECT setval('users_id_seq', (SELECT COALESCE(MAX(id), 1) FROM users));
SELECT setval('orders_id_seq', (SELECT COALESCE(MAX(id), 1) FROM orders));
```

---

## Feature Mapping

### Extensions to HeliosDB Equivalents

| PostgreSQL Extension | HeliosDB Equivalent | Migration Notes |
|---------------------|---------------------|-----------------|
| **PostGIS** | Planned | Geospatial functions and indexes are planned; keep PostGIS on PostgreSQL for now |
| **pgvector** | HTTP vector-store API | Vector stores with hybrid search over the HTTP gateway; the SQL vector type and indexes are planned |
| **pg_trgm** | HeliosDB Full-Text | Native trigram support |
| **hstore** | HeliosDB JSONB | Use JSONB for key-value data |
| **uuid-ossp** | Native UUID | Built-in UUID generation |
| **pg_stat_statements** | HeliosDB Query Analytics | Enhanced query monitoring |
| **pg_partman** | Native Partitioning | Enhanced partition management |
| **timescaledb** | HeliosDB Time-Series | Native time-series support |
| **pg_cron** | HeliosDB Scheduler | Native job scheduling |

### Geospatial (PostGIS)

:::caution[Planned]
Geospatial functions and indexes are planned; they are not in the current Nano or Full releases. Keep PostGIS workloads on PostgreSQL for now.
:::

### Vector Search (pgvector to HeliosDB Vector)

:::caution[Planned in SQL]
HeliosDB Full serves vector stores with hybrid search over its HTTP gateway (`POST /api/v1/vectors/stores/{store}/search/hybrid`). The SQL vector type, HNSW indexes and distance operators shown below, and SQL vector indexes with Product Quantization, are planned.
:::

**PostgreSQL/pgvector:**
```sql
CREATE EXTENSION vector;

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536)
);

CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops);

SELECT * FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;
```

**HeliosDB (native vector support):**
```sql
-- No extension needed

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding VECTOR(1536)
);

-- HNSW index for better performance
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

SELECT * FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;
```

**Additional HeliosDB Vector Features:**
- Multiple index types: HNSW, IVF, Flat
- Higher dimensions supported (up to 16384)
- Multi-modal vector search
- Built-in embedding generation

### JSON/JSONB Support

HeliosDB provides 100% JSONB compatibility plus additional features:

```sql
-- All PostgreSQL JSONB operations work identically
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- Indexing
CREATE INDEX idx_events_data ON events USING GIN(data);

-- Queries
SELECT * FROM events WHERE data->>'type' = 'purchase';
SELECT * FROM events WHERE data @> '{"status": "active"}';
SELECT * FROM events WHERE data @? '$.items[*] ? (@.price > 100)';

-- PostgreSQL 17 JSON_TABLE (supported in HeliosDB)
SELECT jt.* FROM events,
JSON_TABLE(data, '$.items[*]' COLUMNS(
    item_id INTEGER PATH '$.id',
    item_name TEXT PATH '$.name',
    item_price NUMERIC PATH '$.price'
)) AS jt;
```

### Full-Text Search

**PostgreSQL:**
```sql
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    search_vector TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('english', title || ' ' || body)
    ) STORED
);

CREATE INDEX idx_articles_fts ON articles USING GIN(search_vector);

SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'database & performance');
```

**HeliosDB (identical syntax with enhanced features):**
```sql
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    body TEXT,
    search_vector TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('english', title || ' ' || body)
    ) STORED
);

CREATE INDEX idx_articles_fts ON articles USING GIN(search_vector);

SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'database & performance');

-- Additional HeliosDB features:
-- BM25 ranking
-- Phrase proximity search
-- Fuzzy matching
-- Multi-language support (10+ languages)
```

### Window Functions, CTEs, and Lateral Joins

All advanced SQL features are fully supported:

**Window Functions:**
```sql
-- Fully supported
SELECT
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    AVG(salary) OVER (PARTITION BY department) as dept_avg,
    salary - LAG(salary) OVER (ORDER BY hire_date) as salary_change
FROM employees;
```

**Recursive CTEs:**
```sql
-- Fully supported
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;
```

**Lateral Joins:**
```sql
-- Fully supported
SELECT u.*, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
    SELECT * FROM orders o
    WHERE o.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) recent_orders;
```

---

## Post-Migration Validation

### Step 1: Row Count Verification

```sql
-- Generate row count comparison queries
SELECT 'SELECT ''' || table_name || ''' as table_name, COUNT(*) as row_count FROM ' ||
       table_schema || '.' || table_name || ' UNION ALL'
FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

-- Run on both PostgreSQL and HeliosDB, compare results
```

### Step 2: Data Integrity Checks

```sql
-- Check for NULL values in NOT NULL columns
SELECT table_name, column_name
FROM information_schema.columns
WHERE is_nullable = 'NO'
  AND table_schema = 'public';

-- Verify foreign key relationships
SELECT
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table,
    ccu.column_name AS foreign_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';
```

### Step 3: Index Verification

```sql
-- List all indexes
SELECT
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public';

-- Check index usage
SELECT
    indexrelname,
    idx_scan,
    idx_tup_read
FROM pg_stat_user_indexes;
```

### Step 4: Query Performance Comparison

```sql
-- Enable query timing
\timing on

-- Run representative queries and compare execution times
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE created_at > NOW() - INTERVAL '7 days'
ORDER BY total DESC
LIMIT 100;
```

### Step 5: Application Smoke Tests

- [ ] Test all CRUD operations
- [ ] Verify authentication works
- [ ] Test transaction handling
- [ ] Validate stored procedures
- [ ] Check scheduled jobs
- [ ] Test replication (if applicable)

---

## Performance Tuning After Migration

### Initial Optimization

> **`ANALYZE` in the current release:** `ANALYZE` (including `VACUUM ANALYZE`) is accepted for PostgreSQL compatibility and returns immediately; it does not collect planner statistics yet. Statistics collection is planned. `EXPLAIN ANALYZE`, which runs the query and reports actual rows and timing, works today.

```sql
-- Update statistics
ANALYZE;

-- Vacuum tables (HeliosDB uses optimized compaction)
VACUUM ANALYZE;

-- Enable intelligent caching for frequently accessed tables
ALTER TABLE frequently_accessed ENABLE INTELLIGENT_CACHING;
```

### Memory Configuration

```toml
# heliosdb.toml
[postgresql.memory]
# Allocate similar to PostgreSQL shared_buffers
work_mem = 64           # MB per operation
maintenance_work_mem = 256  # MB for maintenance
shared_buffers = 1024   # MB shared memory
effective_cache_size = 4096  # MB total cache estimate
```

### Connection Pooling

```toml
[postgresql.pooling]
enabled = true
pool_mode = "transaction"  # transaction pooling for web apps
min_pool_size = 10
max_pool_size = 100
idle_timeout = 300
```

### Query Optimization

```toml
[postgresql.query]
query_cache = true
query_cache_size = 256  # MB
parallel_query = true
max_parallel_workers = 4
```

### Index Optimization

```sql
-- Identify missing indexes
SELECT
    schemaname || '.' || relname AS table,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND idx_scan < seq_scan / 10
ORDER BY seq_tup_read DESC
LIMIT 20;

-- Create missing indexes
CREATE INDEX CONCURRENTLY idx_orders_user_date
ON orders(user_id, created_at DESC);
```

### Monitoring Performance

```sql
-- Active sessions
SELECT * FROM pg_stat_activity;

-- Slow queries
SELECT
    query,
    calls,
    total_exec_time / 1000 as total_sec,
    mean_exec_time / 1000 as avg_sec
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Table sizes
SELECT
    schemaname || '.' || tablename as table,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;
```

---

## Common Issues and Troubleshooting

### Connection Issues

| Issue | Cause | Solution |
|-------|-------|----------|
| Connection refused | Server not running | Check HeliosDB status: `systemctl status heliosdb` |
| Authentication failed | Wrong credentials | Verify pg_hba.conf and user credentials |
| SSL required | SSL mode mismatch | Set `sslmode=require` in connection string |
| Too many connections | Pool exhausted | Increase `max_connections` or use connection pooling |

**Debug Connection:**
```bash
# Test connection
psql -h heliosdb.host -U admin -d mydatabase -c "SELECT 1"

# Verbose connection
PGSSLMODE=prefer psql -h heliosdb.host -U admin -d mydatabase -v

# Check SSL
psql "postgresql://admin@heliosdb.host/mydatabase?sslmode=require" \
  -c "SELECT ssl_is_used()"
```

### Data Type Differences

| PostgreSQL | HeliosDB | Notes |
|------------|----------|-------|
| `OID` | `OID` | Supported, but prefer BIGINT for new tables |
| `MONEY` | `MONEY` | Supported, but NUMERIC recommended |
| Large Objects (`lo_*`) | BYTEA | Use BYTEA for binary data |
| `XML` | `XML` | Basic support, consider JSONB for structured data |

### Extension Compatibility

If an extension is not supported:

```sql
-- Check if extension exists
SELECT * FROM pg_available_extensions WHERE name = 'extension_name';

-- Alternative: Use HeliosDB native features
-- Example: Replace pg_trgm with HeliosDB full-text search

-- PostgreSQL pg_trgm approach
CREATE INDEX idx_name_trgm ON users USING GIN (name gin_trgm_ops);
SELECT * FROM users WHERE name % 'john';

-- HeliosDB native approach
CREATE INDEX idx_name_fts ON users USING GIN (to_tsvector('simple', name));
SELECT * FROM users WHERE to_tsvector('simple', name) @@ to_tsquery('john:*');
```

### Replication Issues

```sql
-- Check replication status
SELECT * FROM pg_stat_replication;

-- Check subscription status
SELECT * FROM pg_stat_subscription;

-- Check for replication lag
SELECT
    slot_name,
    active,
    restart_lsn,
    confirmed_flush_lsn
FROM pg_replication_slots;
```

### Performance Issues

```sql
-- Check for long-running queries
SELECT
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- Check for lock contention
SELECT
    blocked_locks.pid AS blocked_pid,
    blocking_locks.pid AS blocking_pid,
    blocked_activity.query AS blocked_query
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
JOIN pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid
WHERE NOT blocked_locks.granted;
```

---

## Rollback Procedures

### Pre-Rollback Checklist

- [ ] Confirm rollback is necessary
- [ ] Notify stakeholders
- [ ] Document current state
- [ ] Ensure PostgreSQL backup is accessible

### Rollback Steps

#### Step 1: Stop Application Traffic to HeliosDB

```bash
# Update load balancer or DNS to stop traffic
# Or update application connection strings back to PostgreSQL
```

#### Step 2: Export Data Changes from HeliosDB (if needed)

```sql
-- Export any data created in HeliosDB during migration
SELECT * FROM audit_log
WHERE created_at > 'migration_start_timestamp'
\copy (SELECT ...) TO '/tmp/new_data.csv' CSV HEADER;
```

#### Step 3: Restore PostgreSQL Service

```bash
# If PostgreSQL was stopped
sudo systemctl start postgresql

# Verify PostgreSQL is running
psql -h postgres.host -U postgres -c "SELECT 1"
```

#### Step 4: Update Application Connection Strings

```bash
# Revert environment variables
export PGHOST=postgres.host

# Update application configurations
# Restart applications
```

#### Step 5: Verify Application Functionality

- [ ] Test database connectivity
- [ ] Verify data integrity
- [ ] Run application smoke tests
- [ ] Monitor error logs

### Post-Rollback Actions

```sql
-- Apply any new data captured from HeliosDB
\copy new_table FROM '/tmp/new_data.csv' CSV HEADER;

-- Verify data consistency
SELECT COUNT(*) FROM critical_table;
```

---

## Appendix

### Quick Reference Commands

```bash
# Export PostgreSQL schema
pg_dump -h localhost -U postgres -d mydb --schema-only > schema.sql

# Export PostgreSQL data
pg_dump -h localhost -U postgres -d mydb --data-only -Fc > data.dump

# Import to HeliosDB
psql -h heliosdb.host -U admin -d mydb < schema.sql
pg_restore -h heliosdb.host -U admin -d mydb --data-only data.dump

# Sync sequences
psql -h heliosdb.host -U admin -d mydb -c "
  SELECT setval(pg_get_serial_sequence('tablename', 'id'),
         (SELECT MAX(id) FROM tablename));"

# Verify migration
psql -h heliosdb.host -U admin -d mydb -c "SELECT COUNT(*) FROM tablename;"
```

### Migration Checklist Summary

#### Pre-Migration
- [ ] Backup PostgreSQL database
- [ ] Document extensions and custom types
- [ ] Provision HeliosDB instance
- [ ] Configure authentication and SSL
- [ ] Plan maintenance window

#### Migration
- [ ] Export and import schema
- [ ] Migrate data (choose appropriate method)
- [ ] Sync sequences and identity columns
- [ ] Update application connection strings

#### Post-Migration
- [ ] Verify row counts
- [ ] Check data integrity
- [ ] Validate indexes
- [ ] Run performance tests
- [ ] Execute application smoke tests
- [ ] Monitor error logs
- [ ] Tune performance settings

#### Cleanup
- [ ] Remove replication subscriptions (if used)
- [ ] Archive PostgreSQL backup
- [ ] Update documentation
- [ ] Decommission PostgreSQL (after validation period)

### Sample Migration Timeline

| Phase | Duration | Activities |
|-------|----------|------------|
| Assessment | 1-2 days | Inventory, extension review, sizing |
| Preparation | 1-3 days | HeliosDB setup, configuration, testing |
| Schema Migration | 1-2 hours | Export, adjust, import schema |
| Data Migration | Varies | Depends on database size |
| Validation | 1-2 days | Testing, performance comparison |
| Cutover | 1-4 hours | Connection string updates, final sync |
| Monitoring | 1-2 weeks | Performance monitoring, issue resolution |

### Environment Variables Reference

```bash
# PostgreSQL-compatible environment variables (work with HeliosDB)
export PGHOST=heliosdb.host
export PGPORT=5432
export PGDATABASE=mydatabase
export PGUSER=admin
export PGPASSFILE=~/.pgpass
export PGSSLMODE=prefer
export PGSSLCERT=/path/to/client.crt
export PGSSLKEY=/path/to/client.key
export PGSSLROOTCERT=/path/to/ca.crt
```

---

## Related Documentation

- [PostgreSQL Protocol Documentation](https://www.heliosdb.com/docs/full/reference/protocols/postgresql/readme/)
- [PostgreSQL Compatibility Matrix](https://www.heliosdb.com/docs/full/reference/protocols/postgresql/compatibility/)
- [PostgreSQL Configuration Guide](https://www.heliosdb.com/docs/full/reference/protocols/postgresql/configuration/)
- [PostgreSQL Examples](https://www.heliosdb.com/docs/full/reference/protocols/postgresql/examples/)
- [Protocol Compatibility Matrix](https://www.heliosdb.com/docs/full/reference/protocols/protocol_compatibility_matrix/)
- [MySQL Migration Guide](https://www.heliosdb.com/docs/full/reference/protocols/migrations/migration_from_mysql_to_heliosdb/)
- [MSSQL Migration Guide](https://www.heliosdb.com/docs/full/reference/protocols/migrations/migration_from_mssql_to_heliosdb/)
- [SQLite Migration Guide](https://www.heliosdb.com/docs/full/reference/protocols/migrations/migration_from_sqlite_to_heliosdb/)

---

**Need Help?**

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

---

**Document Version History:**

| Version | Date | Changes |
|---------|------|---------|
| 1.0 | 2026-01-04 | Initial release |