Pattern 04 · Modernization
SQL Server → PostgreSQL migration pattern
A production pattern for moving off SQL Server licensing onto PostgreSQL — assess honestly, convert deliberately, replicate continuously, and cut over only when validation proves parity.
01 · PROBLEM
SQL Server licensing is a growing tax on growth
SQL Server licensing — per-core, with Software Assurance — scales with your success and penalizes modern architectures (more cores, more replicas, more environments). Meanwhile the ecosystem has moved: PostgreSQL now matches or exceeds SQL Server on most enterprise workloads, with extensions (PostGIS, pgvector, TimescaleDB, Citus) that turn it into a platform.
This pattern describes how to migrate production SQL Server estates to PostgreSQL without heroic cutovers — continuous replication, dual-run validation, and rollback plans that have been rehearsed, not just written.
02 · WHEN TO USE
Fit and anti-fit
- License renewals are forcing the conversation, or growth plans multiply core costs.
- You want cloud portability — PostgreSQL runs well on every cloud and on-prem.
- Workloads are OLTP, mixed, or analytical with standard SQL and procedural logic.
- The organization can fund conversion engineering, not just a "lift and shift."
Don't use it as a pure lift-and-shift: moving SQL Server-isms (linked servers, CLR, heavy SSIS) to Postgres without conversion just relocates the pain. And if your estate is deeply coupled to the Microsoft stack with no appetite for change, honesty beats a doomed migration.
03 · ARCHITECTURE
Reference migration flow
The spine of the pattern: replicate early, validate continuously, cut over late. Validation isn't a phase — it's a standing process from the first replicated row to decommission.
04 · COMPONENTS
What each piece does
| Component | Responsibility | Build/buy notes |
|---|---|---|
| Assessment | Inventory databases, score complexity (T-SQL, CLR, linked servers, SSIS/SSRS), size effort honestly | Automated scanners (AWS SCT/DMS, ora2pg-style analysis) plus human review of the hard 20% |
| Schema conversion | Data types, constraints, indexes, sequences, partitions | Automate the 80%; hand-convert edge cases (hierarchies, sparse columns, filestream) |
| Code conversion | T-SQL → plpgsql: procedures, functions, triggers; Agent jobs → orchestrator; SSIS → ELT | The long pole. Budget per-procedure, and simplify logic where the business allows |
| Replication (initial + CDC) | Bulk load then continuous change capture with lag monitoring | Debezium, AWS DMS, or Striim — pick for your topology, test failover |
| Validation | Row counts, checksums, data sampling, application-level dual-run comparison | Dual-run is the gold standard: same inputs, compare outputs, investigate diffs |
| Cutover | Blue-green switch with rehearsed rollback | Rehearse the rollback like the cutover — an untested rollback is a hope |
| Postgres operations | HA (streaming replication), PITR backups, vacuum/autovacuum tuning, monitoring | Plan ops from day one; Postgres rewards good operations and punishes neglect |
05 · FLOW
How the migration proceeds
06 · TRADEOFFS
Decisions with real costs
| Decision | Option A | Option B | Our default |
|---|---|---|---|
| Strategy | Big-bang — one cutover, maximum risk | Wave-based trickle — longer program, contained blast radius | Wave-based; big-bang only for trivial estates |
| Procedural code | Convert T-SQL → plpgsql faithfully — preserves behavior, preserves complexity | Simplify/move logic to app — better long-term, more app work | Convert faithfully first; simplify where the business signs off |
| Postgres flavor | Managed (RDS, Cloud SQL, Azure) — less ops, some limits | Self-managed — full control, full responsibility | Managed default; self-manage only with a real Postgres ops team |
| Downtime | Planned outage window — simpler, business cost | Near-zero via CDC + blue-green — more engineering | Near-zero for production; windows only for dev/test |
On SQL Server → PostgreSQL specifically, see our in-depth migration guide.
07 · SECURITY
Non-negotiables
- Lower environments: migrate with masked or synthetic data; production dumps in dev are how breaches start.
- Credential rotation: new secrets for the Postgres estate — don't carry SQL Server service accounts across.
- Encryption parity: TDE equivalents (disk-level + pgcrypto where needed), TLS everywhere, audit logging from day one.
- Replication security: CDC channels are privileged — encrypt, authenticate, and monitor them like production access, because they are.
08 · GOVERNANCE
Running it like a program, not a project
- Wave governance: each wave has entry/exit criteria; no wave cuts over until dual-run diffs are explained.
- Rollback rehearsals: tested on schedule, timed, with named owners — a rollback plan that's never been run is fiction.
- License tracking: track SQL Server license retirement per wave; the business case is realized at decommission, not at cutover.
- Knowledge transfer: your DBAs and developers learn Postgres operations during the program, not after — pair with practitioners throughout.
09 · COST
Where the money goes
- Conversion engineering is the dominant cost — procedural code conversion and app remediation, not tooling.
- Dual-running doubles infra temporarily; budget it per wave and release SQL Server capacity aggressively after each decommission.
- License savings are real but lagging — model the crossover point honestly, including the program cost.
- Postgres ops (managed service fees or DBA time) replaces license cost; it's smaller, but it isn't zero.
10 · IMPLEMENTATION
A phased path that de-risks
Engagement tie-in: our Modernization practice runs these migrations as engineering programs, starting with a fixed-scope SQL Server → PostgreSQL assessment.
11 · RELATED
Keep exploring
SQL Server → PostgreSQL Guide
The in-depth migration guide: assessment, conversion, and cutover detail.
Read guide →Lakehouse
Many migrations pair with a lakehouse for analytics — one modernization, two outcomes.
Read pattern →Modernization Discovery Kit
The questionnaire we use to scope database modernization honestly.
Download PDF →Start here
Talk to an Architect
Bring your hardest AI, data, or modernization problem. We'll tell you plainly whether we can help — and what it takes.
FAQ
Questions we hear
It depends on database count, procedural code volume, and application coupling — not data size. A single low-complexity database can move in weeks; estates with heavy T-SQL, SSIS, and linked servers run in phased quarters. The assessment phase exists to replace guessing with a measured plan.
Usually yes, using initial load plus change-data-capture with a blue-green cutover. Zero-downtime is an engineering outcome you design for — it requires dual-run validation and a rehearsed rollback, not just a replication tool.
They don't migrate — they get replaced. SSIS becomes your ELT/ingest layer of choice, SSRS reports move to your BI platform, and Agent jobs become scheduled tasks in your orchestrator. Plan this as part of conversion, not after.