Skip to content

PostgreSQL to HeliosDB Migration Guide

Version: 1.0 Last Updated: 2026-01-04

Table of Contents

  1. Introduction
  2. Why Migrate from PostgreSQL to HeliosDB
  3. Compatibility Overview
  4. Pre-Migration Checklist
  5. Step-by-Step Migration Process
  6. Feature Mapping
  7. Post-Migration Validation
  8. Performance Tuning After Migration
  9. Common Issues and Troubleshooting
  10. Rollback Procedures
  11. 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

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

# 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

  • [ ] 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

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

# 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

[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

  • [ ] 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

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

Document Version History:

Version Date Changes
1.0 2026-01-04 Initial release