SQL Server to PostgreSQL Migration Blueprint
A migration plan from Microsoft SQL Server to PostgreSQL covering T-SQL-to-PL/pgSQL conversion, type mapping, collation handling, and DMS-based CDC cutover.
Overview
Microsoft SQL Server is a strong commercial RDBMS, but its per-core licensing is costly and it ties teams to the Microsoft stack. PostgreSQL offers comparable features under an open-source license. This blueprint migrates schema, T-SQL logic, and data while keeping application behavior identical.
The key challenges are T-SQL stored procedures, identity columns, MERGE statements, and SQL Server quirks such as case-insensitive default collations and the [bracket] identifier syntax.
Phases
Assessment. Inventory databases, stored procedures, functions, triggers, and SQL Agent jobs. Use AWS SCT or sqlserver2pgsql to estimate conversion effort and flag unsupported constructs.
Schema conversion. Map types: NVARCHAR to varchar/text, DATETIME2 to timestamp, BIT to boolean, MONEY to numeric. Replace IDENTITY with GENERATED AS IDENTITY. Decide collation strategy, since PostgreSQL is case-sensitive by default.
Code migration. Rewrite T-SQL procedures as PL/pgSQL. Convert ISNULL to COALESCE, GETDATE() to now(), TOP n to LIMIT n, and MERGE to INSERT ... ON CONFLICT. Port SQL Agent jobs to pg_cron or external schedulers.
Data migration. Bulk-extract with bcp and load via COPY, or use AWS DMS for ongoing replication. For minimal downtime, run DMS CDC so the target tracks source changes.
Cutover. Reconcile row counts and checksums, run read-only validation, then switch the connection strings during a planned window.
Key Risks and Mitigations
- Data consistency: Validate counts and checksums; keep CDC deltas small before cutover.
- T-SQL incompatibility:
MERGE, table-valued parameters, andCROSS APPLYneed rewrites. Triage and convert iteratively. - Downtime: Use AWS DMS CDC for near-zero-downtime switchover.
Recommended Tooling
AWS SCT and AWS DMS for automated conversion and replication; sqlserver2pgsql for schema translation; bcp for extraction; pg_stat_statements for tuning.
Success Metrics
License-cost reduction, query latency parity, zero data-integrity defects, and stable post-cutover error rates.
Prerequisites
Object inventory, collation decision, performance baselines on SQL Server, and a tested rollback path.