SQL Server to PostgreSQL Migration: A Realistic Roadmap

01 · Phase 0

The compatibility assessment (2–3 weeks, non-negotiable)

Every migration that blows up skipped this phase. The compatibility assessment answers one question: what are we actually migrating? Not "how many databases" — what is inside them, what depends on them, and what SQL Server-specific behavior the applications rely on.

Schema inventory. Every database: size, object counts, data types in use, and the features that don't travel. The hit list: CLR assemblies, Service Broker queues, linked servers, FILESTREAM, full-text catalogs, SSIS/SSAS/SSRS dependencies, and database mail. Each one needs a disposition: replace, re-architect, or accept a gap. AWS Schema Conversion Tool (SCT) and ora2pg produce the first-pass report; a human reads it and marks what the tools got wrong.

T-SQL complexity analysis. This is where timelines are made or broken. Simple DDL and CRUD converts mechanically — SCT handles 60–80% of it. The manual tail is where the effort lives: MERGE statements, TOP with ties, proprietary date functions, error handling built around @@ERROR/TRY-CATCH semantics, dynamic SQL, and cursors that were already slow on SQL Server. Score each database: green (mostly mechanical), amber (meaningful manual conversion), red (re-architecture required). Red databases get their own project plan, not a wave slot.

Dependency mapping. Cross-database queries, linked servers pointing outward, applications with embedded T-SQL, ETL jobs that touch the database at 2 AM, reporting tools with direct connections. Map every inbound and outbound edge. The dependencies you miss here become the cutover-week surprises.

Performance baselining. Capture the top-50 queries by total runtime on SQL Server now. After migration, the same queries get re-tuned against PostgreSQL's planner — which has different strengths (better at complex joins and CTEs, different indexing behavior). Without a baseline, "it's slower" becomes an argument with no evidence.

The assessment's deliverable is a wave plan with effort estimates per database, not a single big-bang date. Our Database Modernization Assessment runs exactly this scope in 2–3 weeks — and if your team does it internally, budget the same calendar time. Skipping it doesn't save three weeks; it borrows them at 10x interest.

02 · Sequencing

Wave planning: easy and valuable first

Sequence the migration in waves, each ending in a production cutover. The ordering rule: highest value, lowest complexity first. Early wins fund organizational confidence; early complexity teaches you the conversion patterns while the stakes are low.

Wave 1 — the quick wins. Small, green-scored databases with clear ownership and low dependency fan-out. Internal tools, departmental apps, the reporting replica nobody admits is production. Target: 2–4 databases, cut over in 6–8 weeks. These validate your tooling chain, your conversion patterns, and your cutover runbook on systems where a rollback is painless.

Wave 2 — the core transactional systems. Amber-scored databases with real users. By now your team has conversion patterns for the common T-SQL constructs, the CDC pipeline is proven, and the runbook has survived contact with production. This is where the license savings actually land.

Wave 3 — the reds. Databases that need re-architecture: SSIS estates, linked-server topologies, CLR-heavy logic. These may take as long as waves 1 and 2 combined. Some may never migrate — and that's a valid outcome if the assessment says so. A database that stays on SQL Server for a legitimate reason is not a failure; a database that stays because the project ran out of steam is.

Run waves in parallel only when wave 1's patterns are stable. The most common sequencing failure: starting wave 2's conversion before wave 1 has cut over, then discovering a systematic conversion error that now exists in two codebases.

03 · Conversion

T-SQL to PostgreSQL: the actual work

Conversion has three layers, and they require different skills.

Layer 1 — schema and data types. Mostly mechanical. DATETIME → TIMESTAMP, NVARCHAR → TEXT or VARCHAR, IDENTITY → GENERATED ALWAYS AS IDENTITY, UNIQUEIDENTIFIER → UUID. Tools handle this well. Watch for: MONEY (use NUMERIC), case-sensitivity differences in collation, and SQL Server's lenient implicit conversions that PostgreSQL will reject loudly. That last one is a feature — it finds bugs.

Layer 2 — procedural code. Stored procedures, functions, and triggers convert at 60–80% mechanically; the rest is judgment. T-SQL's MERGE becomes INSERT ... ON CONFLICT or staged upserts. Error handling moves from @@ERROR to EXCEPTION blocks in PL/pgSQL. Table-valued parameters have no direct equivalent — the standard replacement is passing JSON or arrays. Cursors that were slow on SQL Server stay slow; this is the time to rewrite them as set operations, not to transliterate them.

Layer 3 — application SQL. The code nobody owns: embedded T-SQL in application repositories, ORM-generated queries that relied on SQL Server behavior, and dynamic SQL built by string concatenation. This layer is found by searching codebases and by watching production query logs during the assessment — not by converting the database alone. Budget real time for it; it's where "the database migrated fine but the app is broken" comes from.

Conversion discipline: every converted object gets a test. Golden-query comparisons — same inputs on SQL Server and PostgreSQL, diffed outputs — catch semantic drift that unit tests miss. The SQL Server → PostgreSQL migration pattern includes the conversion testing approach we reuse.

04 · Data movement

Moving the data without stopping the business

For anything beyond a weekend-migration-sized database, the approach is initial load plus change data capture. Full load the target (pgloader, AWS DMS full load, or native COPY pipelines), then stream changes with CDC (Debezium against SQL Server's CDC, AWS DMS ongoing replication) until cutover. The target stays within seconds of the source for the entire project; cutover is a short maintenance window for the final sync, connection-string flip, and validation — not a 14-hour dump-and-restore.

Reconciliation is the job. Row counts per table, checksum comparisons on critical tables, and golden-query diffing on the top-50 baselined queries. Run reconciliation continuously during the CDC window, not once at cutover. A replication lag that grows silently for three weeks becomes a cutover-night crisis.

Dual-write is the fallback, not the default. Some teams dual-write from the application to both databases during the transition. It works but doubles write-path complexity and creates split-brain risk if the writes diverge. Prefer CDC from the source of truth; use dual-write only where CDC can't reach (certain application-level transformations).

The rollback plan is a deliverable. Every wave's cutover plan includes explicit rollback criteria and a tested rollback path — usually "revert connection strings, source database is still the system of record until the validation gate passes." If you can't describe the rollback in one paragraph, you're not ready to cut over.

05 · Cutover

Cutover week, minus the drama

A good cutover is boring. The runbook: freeze schema changes 48 hours before; final CDC lag check; stop writes to source; drain in-flight transactions; final reconciliation; flip connection strings (or DNS); smoke-test critical paths; open writes. Target maintenance window: 1–4 hours depending on final sync volume and validation scope.

Validation gates before you declare victory: golden-query diffs pass, application smoke tests pass, monitoring shows normal error rates for 2 hours, and the rollback decision point is explicitly time-boxed ("if X isn't green by T+2h, we roll back"). Assign a single cutover commander with authority to call the rollback. Committees don't cut over; people do.

Post-cutover, keep the SQL Server source in read-only mode for one full business cycle (a week for most systems, a month for month-end-close systems). It costs almost nothing and it's the cheapest insurance you will ever buy. Decommission only after the business has run its normal rhythms on the new system — payroll, month-end, quarter-end, whatever your cycle is.

06 · Failure modes

How these projects actually die

Scope by database count instead of complexity. "47 databases" is not a scope; it's a number. One red database can exceed twenty green ones in effort. The assessment's complexity scoring exists precisely to prevent this.

Converting without testing. Mechanical conversion without golden-query validation produces a database that looks migrated and behaves differently. Every semantic difference found in production costs 10x what the test would have cost.

Forgetting the ecosystem. The database migrates; the SSIS packages, the linked Excel spreadsheets, the Access front-ends, and the BI tool with a hardcoded connection string do not. Dependency mapping in Phase 0 is the antidote.

Big-bang cutover. Migrating everything in one weekend maximizes blast radius and eliminates learning. Waves exist so that wave 1's mistakes are cheap.

No performance re-tuning. PostgreSQL's planner is excellent but different. Queries ported verbatim and never re-tuned will be "slower" — not because PostgreSQL is slower, but because nobody tuned for it. Budget 10–15% of conversion effort for query re-tuning against the new planner, using the Phase 0 baselines as the scoreboard.

Read the full reference material in our SQL Server → PostgreSQL migration guide, grab the migration pack for the checklists, and if you want the assessment done for you, that's the Database Modernization Assessment. Then talk to an architect — bring your database list and we'll help you score it.