PostgreSQL to HeliosDB Migration Guide

Version: 1.0 Last Updated: 2026-01-04

Introduction
Why Migrate from PostgreSQL to HeliosDB
Compatibility Overview
Pre-Migration Checklist
Step-by-Step Migration Process
Feature Mapping
Post-Migration Validation
Performance Tuning After Migration
Common Issues and Troubleshooting
Rollback Procedures
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 seamlessly
Existing applications connect without code changes
PostgreSQL client libraries work without modification

Compatibility Overview

Wire Protocol Compatibility

Component	Coverage	Status
PostgreSQL Wire Protocol v3.0	100%	Complete
Extended Query Protocol	100%	Complete
COPY Protocol	100%	Complete
Replication Protocol	100%	Complete

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

SELECT extname, extversion FROM pg_extension;

Identify stored procedures and functions

SELECT routine_schema, routine_name, routine_type
FROM information_schema.routines
WHERE routine_schema NOT IN ('pg_catalog', 'information_schema');

Catalog custom data types

SELECT typname, typtype FROM pg_type
WHERE typnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

Review foreign data wrappers

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

[postgresql.auth]
method = "scram-sha-256"  # Recommended
hba_file = "/etc/heliosdb/pg_hba.conf"

Set up SSL/TLS (if required)

[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

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

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

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

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

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

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

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

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

#!/bin/bash
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):

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

// 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):

// Before
String url = "jdbc:postgresql://postgres.host:5432/mydatabase";

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

Go (lib/pq):

// 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:

# Before
export PGHOST=postgres.host

# After
export PGHOST=heliosdb.host

Phase 4: Sequence and Identity Column Synchronization

After data migration, synchronize sequence values:

-- 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	HeliosDB Geospatial	Native ST_* functions, R-tree indexing
pgvector	HeliosDB Vector	Native vector type with HNSW/IVF indexes
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 to HeliosDB Geospatial)

HeliosDB provides PostGIS-compatible geospatial functionality:

PostgreSQL/PostGIS:

CREATE EXTENSION postgis;

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326)
);

CREATE INDEX idx_locations_geom ON locations USING GIST(geom);

SELECT name, ST_Distance(
    geom::geography,
    ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::geography
) AS distance
FROM locations
ORDER BY distance
LIMIT 10;

HeliosDB (identical syntax):

-- No CREATE EXTENSION needed - geospatial is built-in

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326)
);

CREATE INDEX idx_locations_geom ON locations USING GIST(geom);

SELECT name, ST_Distance(
    geom::geography,
    ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::geography
) AS distance
FROM locations
ORDER BY distance
LIMIT 10;

Supported ST_ Functions:*

Measurement: ST_Distance, ST_Length, ST_Area, ST_Perimeter
Relationships: ST_Contains, ST_Within, ST_Intersects, ST_Overlaps
Processing: ST_Centroid, ST_Buffer, ST_ConvexHull, ST_Simplify
Accessors: ST_X, ST_Y, ST_SRID, ST_GeometryType

Vector Search (pgvector to HeliosDB Vector)

PostgreSQL/pgvector:

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):

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

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

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):

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:

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

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

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

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

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

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

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

Performance Tuning After Migration

Initial Optimization

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

[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

[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

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

Index Optimization

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

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

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

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

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

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

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

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

# 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

# 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

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

# 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

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

# 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

Need Help?

Documentation: docs.heliosdb.io
Community: community.heliosdb.io
Support: support@heliosdb.io

Document Version History:

Version	Date	Changes
1.0	2026-01-04	Initial release

PostgreSQL to HeliosDB Migration Guide

PostgreSQL to HeliosDB Migration Guide

Table of Contents

Introduction

Scope

Target Audience

Why Migrate from PostgreSQL to HeliosDB

Key Benefits

Migration Complexity: Low

Compatibility Overview

Wire Protocol Compatibility

SQL Language Compatibility

PostgreSQL 17 Feature Support

Client Driver Compatibility

GUI Tool Compatibility

Pre-Migration Checklist

Assessment Phase

Infrastructure Preparation

Application Preparation

Backup and Recovery

Step-by-Step Migration Process

Phase 1: Schema Migration

Step 1.1: Export PostgreSQL Schema

Step 1.2: Review and Adjust Schema (if needed)

Step 1.3: Import Schema to HeliosDB

Phase 2: Data Migration

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

Option B: Using COPY Command (For Large Tables)

Option C: Streaming Migration (Zero Downtime)

Option D: Parallel Data Migration Script

Phase 3: Application Connection String Changes

Connection String Format

Language-Specific Examples

Phase 4: Sequence and Identity Column Synchronization

Feature Mapping

Extensions to HeliosDB Equivalents

Geospatial (PostGIS to HeliosDB Geospatial)

Vector Search (pgvector to HeliosDB Vector)

JSON/JSONB Support

Full-Text Search

Window Functions, CTEs, and Lateral Joins

Post-Migration Validation

Step 1: Row Count Verification

Step 2: Data Integrity Checks

Step 3: Index Verification

Step 4: Query Performance Comparison

Step 5: Application Smoke Tests

Performance Tuning After Migration

Initial Optimization

Memory Configuration

Connection Pooling

Query Optimization

Index Optimization

Monitoring Performance

Common Issues and Troubleshooting

Connection Issues

Data Type Differences

Extension Compatibility

Replication Issues

Performance Issues

Rollback Procedures

Pre-Rollback Checklist

Rollback Steps

Step 1: Stop Application Traffic to HeliosDB

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

Step 3: Restore PostgreSQL Service

Step 4: Update Application Connection Strings

Step 5: Verify Application Functionality

Post-Rollback Actions

Appendix

Quick Reference Commands

Migration Checklist Summary

Pre-Migration

Migration

Post-Migration

Cleanup

Sample Migration Timeline

Environment Variables Reference

Related Documentation