Sql To Postgres

Inputs

Phase-by-Phase Orchestration

Phase 0: Precheck

1. Verify MSSQL extension is available (mssql_connect).
2. Verify PostgreSQL extension is available (pgsql_*).
3. Check for ora2pg, pgLoader, sqlfluff, pgtap, pg_prove, HammerDB availability.
4. Check for DAB CLI (dab --version).
5. Check for sec-check CLI (agentsec --version).

Phase 1: Source Assessment

Tools: mssql_connect, mssql_list_tables, mssql_run_query, ora2pg, DAB, SSMS 22, sec-check Output: docs/01-source-assessment.md, docs/tsql-incompatibility-report.md

Step 1.1: Schema Discovery (3-tool cross-validation)

Consensus gate: All 3 report same table count, column types, FK relationships.

Step 1.2: SP/Trigger/View Extraction

-- Run via mssql_run_query
SELECT
  o.name AS object_name,
  o.type_desc,
  m.definition,
  LEN(m.definition) AS definition_length
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
ORDER BY o.type_desc, o.name;

Step 1.3: T-SQL Incompatibility Scan (24 patterns)

Scan all extracted definitions for:

• HIGH: Cursors, MERGE, @@TRANCOUNT/nested transactions, HIERARCHYID, GEOGRAPHY/GEOMETRY, linked servers, OPENROWSET/OPENQUERY
• MEDIUM: @@ROWCOUNT, @@IDENTITY, CROSS APPLY, TRY/CATCH, #TempTables, table variables, sp_executesql, TRY_CAST
• LOW: ISNULL to COALESCE, NEWID to gen_random_uuid, GETDATE to NOW, TOP to LIMIT, NOLOCK to remove, RAISERROR to RAISE EXCEPTION, OUTPUT to RETURNING

Output: docs/tsql-incompatibility-report.md with every instance, location, severity, recommended fix.

Step 1.4: Performance Baseline

Capture SSMS 22 execution plans for key stored procedures. Record:

• Execution time (ms)
• Logical reads
• Plan cost
• Query hint recommendations

Step 1.5: Security Baseline

-- Run via mssql_run_query
SELECT name, type_desc, is_disabled FROM sys.sql_logins;
SELECT * FROM sys.server_permissions;
SELECT name, is_encrypted FROM sys.databases;

Also run: agentsec scan on any SQL scripts in the source path.

Mermaid Diagrams: Source ER diagram, SP dependency graph, incompatibility heatmap.

Phase 2: Migration Execution

Tools: pgLoader, ora2pg, Copilot, sqlfluff, pgtap, PostgreSQL ext Output: docs/02-migration-execution.md, docs/schema-optimization-logic.md

Step 2.1: pgLoader Dry Run

pgloader --dry-run pgloader.conf

Validate type mappings. Cross-check with ora2pg schema conversion output.

Step 2.2: Data Transfer

pgloader pgloader.conf

pgLoader CAST rules for WideWorldImporters:

• NVARCHAR to TEXT
• BIT to BOOLEAN
• DECIMAL to NUMERIC
• DATETIME2 to TIMESTAMPTZ
• UNIQUEIDENTIFIER to UUID
• MONEY to NUMERIC(19,4)
• HIERARCHYID to TEXT (with ltree migration plan)
• GEOGRAPHY to PostGIS geography

Step 2.3: SP Translation (T-SQL to PL/pgSQL)

For each stored procedure:

1. Copilot generates PL/pgSQL translation from MSSQL ext source
2. ora2pg auto-converts independently
3. Compare outputs - merge best of both
4. sqlfluff lint the result
5. Write pgtap test for functional equivalence
6. Iterate until sqlfluff AND pgtap pass

Step 2.4: Incompatible Pattern Rewrites

For each HIGH/MEDIUM pattern from Phase 1.3:

• Cursors to Set-based CTEs / window functions
• MERGE to INSERT ... ON CONFLICT DO UPDATE
• @@TRANCOUNT to SAVEPOINT / ROLLBACK TO SAVEPOINT
• CROSS APPLY to LATERAL JOIN
• TRY/CATCH to BEGIN...EXCEPTION WHEN
• #TempTables to CREATE TEMP TABLE
• sp_executesql to EXECUTE format(...) with %I/%L

Document the reasoning for each optimization in docs/schema-optimization-logic.md.

Mermaid Diagrams: Migration flow, type mapping table, SP transformation pipeline, schema optimization decision tree.

Phase 3: Validation and Testing (Iterate Until Consensus)

Tools: MSSQL ext + PG ext (side-by-side), pgtap, DAB, HammerDB, pgbench, sec-check Output: docs/03-validation-report.md, tests/performance/results/trending.md

Step 3.1: Data Integrity (3-tool validation)

Step 3.2: Functional Equivalence (3-tool validation)

Step 3.3: Performance (before/after)

Run perf-001 through perf-010 test suite. Store results as timestamped JSON.

Iterate: Baseline then add indexes then rewrite cursors then enable PgBouncer then track improvement.

Step 3.4: Security (before/after)

Run sec-001 through sec-010 test suite:

• sec-001: No plaintext credentials
• sec-002: SSL enforced
• sec-003: pgAudit enabled
• sec-004: Least-privilege roles
• sec-005: Row-level security on PII tables
• sec-006: PUBLIC schema locked
• sec-007: Parameterized queries only
• sec-008: Encryption at rest
• sec-009: Firewall rules (no 0.0.0.0/0)
• sec-010: Defender for Open-Source DBs enabled

Mermaid Diagrams: Validation flow, performance trending, security progression, migration readiness dashboard.

Phase 4: Fabric Integration (Optional)

Tools: SqlPackage, MSSQL ext (to Fabric SQL DB), DAB, trivy Output: docs/04-fabric-integration.md

1. SqlPackage export .bacpac from source SQL Server.
2. SqlPackage import to Fabric SQL DB.
3. MSSQL extension connects to Fabric SQL DB endpoint (same tool, new target).
4. Fabric SQL DB auto-replicates to OneLake (Delta/Parquet).
5. DAB config pointing at Fabric SQL DB - same API surface.
6. trivy scan on deployment configs.

Phase 5: Data Agent + GraphQL (Optional)

Tools: MSSQL ext (Copilot Agent Mode to Fabric), DAB MCP, Fabric Portal Output: docs/05-data-agent-setup.md

1. DAB MCP Server on migrated PostgreSQL database.
2. Copilot Agent Mode via MSSQL ext on Fabric SQL DB.
3. Fabric Data Agent / AI Skill for conversational analytics.
4. GraphQL API from Fabric portal.
5. Power BI dashboard with before/after metrics.

GitHub Actions CI Pipeline

.github/workflows/migration-ci.yml runs on every PR:

1. ora2pg assess - complexity report
2. MSSQL schema extract - source inventory
3. sqlfluff lint PL/pgSQL - syntax errors
4. sec-check scan - injection/secrets
5. pgLoader --dry-run - validate type mappings
6. pgtap tests - functional equivalence
7. Row-count comparison - data integrity
8. DAB API regression - application-level validation
9. Generate merged consensus report artifact

All 8 pass = migration validated. Any fail = investigate and iterate.

Guardrails

• This is database-layer only - no application code assumptions.
• Always target Azure Database for PostgreSQL Flexible Server.
• Always document the reasoning behind every schema/type/index optimization.
• Always track test results across iterations with timestamped JSON.
• Always produce before/after metrics for performance AND security.
• Only write result evidence to docs/. No orchestration content in docs/.