SQL Server to PostgreSQL Migration Workflow

Complete step-by-step workflow for migrating a SQL Server database to PostgreSQL.

Overview

This migration uses a two-phase approach:

Schema Migration (Tables, Constraints, Indexes) → Interactive via GitHub Copilot
Code Migration (Views, Functions, Procedures, Triggers) → Automated via AI pipeline

Prerequisites

SQL Server DACPAC/BACPAC file (or direct database access)
Azure OpenAI endpoint configured in .env file (for code migration)
SqlPackage utility (auto-downloaded if needed)
GitHub Copilot with agent mode enabled in VS Code

Complete Migration Steps

Phase 1: Extract SQL Server Objects

Purpose: Extract all database objects from DACPAC into organized folder structure

Hard rule: Start the extraction command and wait for the PowerShell prompt to return. Do not poll, tail, or run any progress/monitoring commands while it runs; it must complete uninterrupted.

Command:

.\scripts\extract_dacpac_objects.ps1 -Package "path\to\database.dacpac"

What it does:

Extracts DACPAC using SqlPackage
Organizes objects into Input// folders
Separates Tables, Constraints (by type), Indexes, Views, Functions, Procedures, Triggers
Creates SSMS-like folder structure for easy navigation

Output:

Input/
└── DatabaseName/
    ├── Tables/
    │   ├── dbo/
    │   │   └── Customer.sql
    │   └── HumanResources/
    │       └── Employee.sql
    ├── Constraints/
    │   ├── PrimaryKey/
    │   ├── ForeignKey/
    │   ├── Check/
    │   ├── Unique/
    │   └── Default/
    ├── Indexes/
    ├── Views/
    ├── Functions/
    ├── StoredProcedures/
    └── Triggers/

Duration: ~30 seconds for AdventureWorks (75 tables, 403 constraints, 96 indexes)

Phase 2: Schema Migration (Interactive)

Purpose: Convert SQL Server schema DDL to PostgreSQL DDL with human oversight

Command (in GitHub Copilot Chat):

/db-migrate-schema-sqltopg --schema path\to\database.dacpac --outdir migrations/DatabaseName/Output

Note: Output will be written to Output// to mirror Input structure

What it does:

Reads extracted SQL Server DDL from Input// folders
Converts tables, constraints, indexes to PostgreSQL syntax
Writes 10 numbered deployment files:
- 01_extensions.sql (auto-detected extensions: uuid-ossp, pgcrypto, ltree, etc.)
- 02_schemas.sql
- 03_sequences.sql
- 04_tables.sql
- 05_primary_keys.sql
- 06_unique_constraints.sql
- 07_check_constraints.sql
- 08_default_constraints.sql
- 09_foreign_keys.sql (after data load)
- 10_indexes.sql (after data load)

Key Benefits:

Interactive review of schema changes
Copilot asks clarifying questions (PostgreSQL version, extensions, naming conventions)
No API costs (uses GitHub Copilot, not Azure OpenAI)
Human oversight for critical schema decisions
Spatial safety: geography/geometry or ST_* usage triggers PostGIS in 01_extensions.sql automatically

Output:

Output/
└── DatabaseName/                # Same name as Input folder
    ├── 01_extensions.sql
    ├── 02_schemas.sql
    ├── 03_sequences.sql
    ├── 04_tables.sql
    ├── 05_primary_keys.sql
    ├── 06_unique_constraints.sql
    ├── 07_check_constraints.sql
    ├── 08_default_constraints.sql
    ├── 09_foreign_keys.sql
    └── 10_indexes.sql

Duration: ~2-5 minutes for AdventureWorks (interactive, requires review)

Phase 3: Code Migration (Automated)

Purpose: Convert SQL Server programmability objects to PL/pgSQL using AI

Command:

.\scripts\migrate_code_objects.ps1 `
    -InputDir "examples\DatabaseName\Input\DatabaseName" `
    -OutputDir "examples\DatabaseName\Output" `
    -EnvFile ".env"

Hard rule: Start the command and do nothing else until it finishes. Do not run Copilot “check progress” prompts or any monitoring/polling commands (Get-Process, log tailing, etc.). These interruptions have repeatedly broken the pipeline.

What it does:

Processes ONLY code objects (Views, Functions, Procedures, Triggers)
Uses 3-stage AI pipeline (Draft → Refine → Verify)
Creates individual .sql files for each object
Applies PostgreSQL best practices (no double quotes, lowercase identifiers)

AI Pipeline:

Stage 1 (Draft): Initial T-SQL → PL/pgSQL conversion
Stage 2 (Refine): Fix correctness gaps, improve quality
Stage 3 (Verify): Final validation against original semantics

Output:

Output/
└── DatabaseName/               # Mirrors Input/<DatabaseName>/ structure
    ├── 01_extensions.sql       (from Phase 2)
    ├── ...                      (from Phase 2)
    ├── 10_indexes.sql          (from Phase 2)
    ├── Views/
    │   ├── HumanResources/
    │   │   └── vEmployee.sql
    │   └── Person/
    │       └── vStateProvinceCountryRegion.sql
    ├── Functions/
    │   └── dbo/
    │       └── ufnGetStock.sql
    └── StoredProcedures/
        └── dbo/
            └── uspGetEmployees.sql

Duration: ~5-15 minutes for AdventureWorks (60+ objects, API calls)

Cost: Uses Azure OpenAI (gpt-5.1-codex) - approximately $X per migration

Phase 4: Deploy to PostgreSQL

Step 1: Deploy Schema (Before Data Load)

Run numbered files sequentially:

# Schema deployment
psql -d targetdb -f Output/01_extensions.sql
psql -d targetdb -f Output/02_schemas.sql
psql -d targetdb -f Output/03_sequences.sql
psql -d targetdb -f Output/04_tables.sql
psql -d targetdb -f Output/05_primary_keys.sql
psql -d targetdb -f Output/06_unique_constraints.sql
psql -d targetdb -f Output/07_check_constraints.sql
psql -d targetdb -f Output/08_default_constraints.sql

# --- LOAD DATA HERE ---
# Use pg_dump, AWS DMS, Azure DMS, or custom ETL

# After data load
psql -d targetdb -f Output/09_foreign_keys.sql    # FKs after data avoids violations
psql -d targetdb -f Output/10_indexes.sql         # Indexes after data for performance

Step 2: Deploy Code Objects

# Views (must run first, may have dependencies)
for file in Output/Views/**/*.sql; do
    psql -d targetdb -f "$file"
done

# Functions
for file in Output/Functions/**/*.sql; do
    psql -d targetdb -f "$file"
done

# Stored Procedures
for file in Output/StoredProcedures/**/*.sql; do
    psql -d targetdb -f "$file"
done

# Triggers (last, depend on tables/functions)
for file in Output/Triggers/**/*.sql; do
    psql -d targetdb -f "$file"
done

Cleanup & Reset

Clean All Generated Files

.\scripts\reset_workspace.ps1

Removes: temp/, test_output*, Output/, reports/, generated .md files, extracted objects

Keep Extracted Objects

.\scripts\reset_workspace.ps1 -KeepExtracted

Removes generated files but keeps Input// folders

Troubleshooting

Issue: "SqlPackage not found"

Solution: Script auto-downloads. If manual install needed:

Download from https://aka.ms/sqlpackage-windows
Install to Utilities/SqlPackage/ or add to PATH

Issue: "400 Bad Request" during code migration

Solution: Check Azure OpenAI configuration in .env:

DRAFT_AZURE_OPENAI_ENDPOINT=https://your-endpoint.cognitiveservices.azure.com
DRAFT_AZURE_OPENAI_KEY=your-key-here
AZURE_OPENAI_DEPLOYMENT_DRAFT=gpt-5.1-codex

Issue: Unicode encoding errors

Solution: Already fixed in run_migration_pipeline.py (uses UTF-8)

Issue: Double quotes in output

Solution: Prompts configured to prevent this. If still occurring, check AI prompts.

Command Reference

Command	Purpose	When to Use
`extract_dacpac_objects.ps1`	Extract SQL Server objects	First step, before any conversion
`/db-migrate-schema-sqltopg`	Schema migration (Copilot)	After extraction, for Tables/Constraints/Indexes
`migrate_code_objects.ps1`	Code migration (AI)	After schema migration, for Views/Functions/Procedures
`reset_workspace.ps1`	Clean up generated files	Between runs or to start fresh

Best Practices

✅ DO:

Run extraction first (creates Input structure)
Review schema migration interactively (GitHub Copilot)
Test converted code objects in development environment first
Deploy FKs and indexes AFTER data load
Version control the generated PostgreSQL files

❌ DON'T:

Skip the extraction step (Input structure required)
Mix permanent scripts with AI-generated temp scripts
Deploy indexes before data load (performance issue)
Commit .env file to version control (contains secrets)
Deploy to production without testing

Architecture Diagram

┌─────────────────┐
│  SQL Server DB  │
│   or DACPAC     │
└────────┬────────┘
         │
         ▼
┌─────────────────────────────────────┐
│  PHASE 1: Extract                   │
│  extract_dacpac_objects.ps1         │
│                                     │
│  Creates Input/<DatabaseName>/     │
│  - Tables/                          │
│  - Constraints/ (PK, FK, etc.)      │
│  - Indexes/                         │
│  - Views/, Functions/, etc.         │
└────────┬────────────────────────────┘
         │
         ├──────────────────┬──────────────────┐
         ▼                  ▼                  ▼
┌─────────────────┐  ┌─────────────┐  ┌──────────────┐
│  PHASE 2:       │  │             │  │              │
│  Schema Migrate │  │   GitHub    │  │   Output/    │
│  (Interactive)  │  │   Copilot   │  │   01-10.sql  │
│                 │  │             │  │   (numbered) │
└─────────────────┘  └─────────────┘  └──────────────┘
         │
         ▼
┌─────────────────────────────────────┐
│  PHASE 3: Code Migration            │
│  migrate_code_objects.ps1           │
│                                     │
│  Azure OpenAI (3-stage pipeline)   │
│  - Draft → Refine → Verify          │
│                                     │
│  Creates Output/Views/, Functions/  │
└────────┬────────────────────────────┘
         │
         ▼
┌─────────────────────────────────────┐
│  PHASE 4: Deploy to PostgreSQL      │
│                                     │
│  1. Run 01-08.sql (schema)          │
│  2. Load data                       │
│  3. Run 09-10.sql (FKs, indexes)    │
│  4. Deploy code objects             │
└─────────────────────────────────────┘

FAQ

Q: Why separate schema and code migration? A: Schema changes benefit from human review (Copilot interactive). Code conversion is repetitive and benefits from automation (AI pipeline).

Q: Can I use a different AI model? A: Yes, edit .env to use different models per stage (draft, refine, verify).

Q: Why numbered files for schema? A: File numbering enforces deployment order. Prevents FK errors (PKs must exist before FKs).

Q: What about data migration? A: This tool handles schema/code only. Use pg_dump, AWS DMS, Azure DMS, or custom ETL for data.

Q: Can I migrate partial database? A: Yes, edit Input folders to include only desired objects before running migrations.

FilesExpand file tree

MIGRATION_WORKFLOW.md

Latest commit

History

MIGRATION_WORKFLOW.md

File metadata and controls

SQL Server to PostgreSQL Migration Workflow

Overview

Prerequisites

Complete Migration Steps

Phase 1: Extract SQL Server Objects

Phase 2: Schema Migration (Interactive)

Phase 3: Code Migration (Automated)

Phase 4: Deploy to PostgreSQL

Step 1: Deploy Schema (Before Data Load)

Step 2: Deploy Code Objects

Cleanup & Reset

Clean All Generated Files

Keep Extracted Objects

Troubleshooting

Issue: "SqlPackage not found"

Issue: "400 Bad Request" during code migration

Issue: Unicode encoding errors

Issue: Double quotes in output

Command Reference

Best Practices

Architecture Diagram

FAQ