Home icon

Build a custom solution to migrate SQL Server HierarchyID to PostgreSQL LTREE with AWS DMS

Database Blog

This article provides a technical guide for migrating SQL Server HierarchyID columns to PostgreSQL LTREE using AWS DMS, addressing the lack of native support for this conversion.

  • SQL Server uses HierarchyID binary format with forward slash notation; PostgreSQL uses LTREE dot-separated paths
  • Create sample HierarchyID tables in SQL Server and enable CDC for change capture
  • Install LTREE extension in target Aurora PostgreSQL database
  • Use AWS DMS Schema Conversion to prepare target table structure
  • Run AWS DMS full-load task with "Stop after applying cached changes" setting
  • Add generated LTREE column transforming HierarchyID data format during migration
  • Resume DMS task to verify CDC replication with new LTREE column
  • Post-migration: drop original column, rename LTREE column, restore IDENTITY behavior
  • Includes mapping table for HierarchyID functions to LTREE equivalents

This solution enables successful hierarchical data migration from SQL Server to PostgreSQL while preserving tree structure relationships.

Go to article

The AWS News Feed is currently looking for gold sponsors. If you want to support the AWS community and reach a large audience of AWS professionals, consider sponsoring the AWS News Feed.

Related articles

Jan 3
2024
Migrate SQL Server databases to AWS with minimal downtime using AWS Migration Hub Orchestrator
Oct 28
2025
Migrate Oracle reference partitioned tables to Amazon RDS or Aurora PostgreSQL with AWS DMS
Jul 1
2024
AWS DMS homogenous migration from PostgreSQL to Amazon Aurora PostgreSQL
Jun 4
2026
AWS Transform SQL Server to PostgreSQL Schema Validation in .NET Application Modernization

The AWS News Feed is currently looking for silver sponsors. If you want to support the AWS community and reach a large audience of AWS professionals, consider sponsoring the AWS News Feed.