SQL Server to PostgreSQL: the complete migration playbook.
Everything we do on a SQL Server → PostgreSQL engagement, in order: the business case, the compatibility analysis, T-SQL conversion, data migration with CDC, testing, cutover, rollback, and post-migration tuning. Written by people who have done the cutovers.
Why teams leave SQL Server
Nobody migrates databases for fun. The migrations that succeed start with a business reason strong enough to survive the hard weeks.
License economics
SQL Server licensing — especially Enterprise on virtualized cores — is often the single largest line item in the data platform budget. PostgreSQL removes it entirely, and the savings fund the migration several times over.
No platform lock-in
PostgreSQL runs identically on-prem, on any cloud, and on managed services (RDS, Cloud SQL, Azure Database, AlloyDB). Your exit options stay open and your negotiating position stays strong.
The extension universe
PostGIS, pgvector, TimescaleDB, Citus: PostgreSQL's extension model keeps absorbing workloads — geospatial, vector search, time-series, distributed — that used to require separate systems.
Hiring reality
Open-source database skills are easier to hire and cheaper to retain than proprietary-stack specialists, and your team stops depending on a single vendor's certification ladder.
Containers & IaC
PostgreSQL slots into Kubernetes, Terraform-managed infrastructure, and GitOps workflows without licensing friction per pod, per core, or per replica.
A forcing function
Migration is the moment to retire dead procedures, consolidate databases, and fix the schema debt everyone has been afraid to touch. The new platform starts clean.
When migration makes sense — and when it doesn't
Honest scoping beats enthusiasm. Here's how we qualify a migration in the first conversation.
Strong candidates
- OLTP and application databases with standard SQL and moderate procedural code
- Licensing costs that exceed the migration budget within 12–24 months
- Cloud or container strategy blocked by SQL Server licensing
- Data warehouse and analytics workloads already moving to a lakehouse
- Estates where SSIS/SSRS usage is light or already being retired
Weak candidates
- Deep CLR integration or FILESTREAM/columnstore features with no PostgreSQL equivalent
- Third-party applications that only certify SQL Server as a backend
- Tiny estates where the migration costs more than three years of licensing
- Teams with no PostgreSQL operational experience and no appetite to build it
- Migrations driven by a calendar deadline rather than a readiness assessment
The migration architecture
One pipeline, five stages. Assessment and conversion happen offline; data moves continuously; the application flips once.
Key design decision: the PostgreSQL target is kept in sync with production SQL Server via CDC before cutover, so the flip is a configuration change, not a data move. The rollback path stays warm until the new system proves itself.
Assessment: inventory before promises
A 2–3 week structured assessment produces the migration's scope, risk register, and estimate. Anything quoted before this is a guess.
Estate inventory
Every database, schema, table, view, procedure, function, trigger, job, linked server, and SSIS/SSRS artifact — catalogued with sizes, growth rates, and owners. Orphaned objects get flagged for retirement, not migration.
Workload profiling
Query-store or trace capture of the real workload: read/write ratios, peak concurrency, the top-100 queries by cost, and batch windows. This becomes the performance baseline PostgreSQL must beat.
Dependency mapping
Which applications, reports, ETL jobs, and downstream consumers touch each database — and how (ORM, raw SQL, linked servers, replication). The cutover sequence is derived from this graph.
Estimate & roadmap
Per-database effort, sequenced waves, the CDC topology, the testing plan, and the cutover calendar. You get a fixed-scope proposal for the build phase — or the honest verdict that a database should stay put.
Compatibility analysis: where the dragons are
We score every object on a convertibility scale. The distribution of that score — not the database count — determines the budget.
| SQL Server feature | PostgreSQL equivalent | Typical effort |
|---|---|---|
| Tables, views, basic indexes | Direct mapping; types mostly 1:1 | Low — automated |
| IDENTITY columns | GENERATED … AS IDENTITY / sequences | Low — automated |
| Stored procedures, functions | PL/pgSQL with rewritten idioms | Medium–High — auto + hand |
| TRY/CATCH, transactions in procs | BEGIN/EXCEPTION blocks; subtransactions | Medium — semantic review |
| Temp tables (#temp) | Temporary tables / CTEs | Medium — pattern rewrite |
| MERGE statement | INSERT … ON CONFLICT | Low–Medium |
| TOP / OFFSET-FETCH | LIMIT / OFFSET | Low — automated |
| PIVOT / UNPIVOT | crosstab() or conditional aggregation | Medium |
| CLR assemblies | PL/Perl, PL/Python, or app-side move | High — redesign |
| SSIS packages | Airflow / Dagster / cloud pipelines | High — rebuild |
| SQL Agent jobs | pgAgent / cron / orchestrator | Low–Medium |
| Linked servers | postgres_fdw / dblink | Medium — architecture call |
| Always On availability groups | Streaming replication + Patroni/Stolon | Medium — ops design |
| Transparent data encryption | pgcrypto / disk-level encryption | Low–Medium |
Schema conversion: mechanical, then careful
Tooling (AWS SCT and open-source converters) handles the mechanical 80%: DDL, data types, defaults, and constraints. The remaining 20% is judgment.
Type mapping decisions that matter
- DATETIME / DATETIME2 → TIMESTAMPTZ vs TIMESTAMP: choose explicitly per column based on whether the application treats values as instants or wall-clock times. This is the #1 source of subtle post-migration bugs.
- NVARCHAR → VARCHAR/TEXT: PostgreSQL is UTF-8 throughout; collation behavior differs — verify sort order on indexes that back ORDER BY queries.
- MONEY / SMALLMONEY → NUMERIC: never map to floating point; preserve exact arithmetic.
- UNIQUEIDENTIFIER → UUID: direct, but check default generation (NEWID() → gen_random_uuid()).
- BIT → BOOLEAN and TINYINT → SMALLINT: watch application code that does arithmetic on bit columns.
What we review by hand
- Clustered vs. heap/index organization: PostgreSQL has no clustered indexes — hot tables get fillfactor and autovacuum tuning instead.
- Filtered indexes and indexed views: rewritten as partial indexes or materialized views with refresh strategy.
- Partitioning schemes: mapped to declarative partitioning with matching partition pruning behavior.
- Case-sensitivity: unquoted identifiers fold to lowercase in PostgreSQL — quoted mixed-case names from SQL Server need a naming decision up front.
T-SQL → PL/pgSQL: the real work
Automated converters get you 60–80% of the way on routine code. The rest — and the correctness of all of it — is engineering.
Idioms we rewrite, not transliterate
- Error handling: TRY/CATCH becomes BEGIN … EXCEPTION WHEN … blocks; we audit every catch site because exception semantics differ around transaction state.
- Temp tables: #temp patterns become TEMPORARY TABLE or, more often, CTEs — which the PostgreSQL planner handles far better than most T-SQL ports assume.
- Cursors and WHILE loops: set-based rewrites wherever the logic allows; where it doesn't, we document why and keep the cursor.
- Dynamic SQL: sp_executesql → EXECUTE … USING with strict parameter binding — dynamic SQL is also where injection regressions hide, so each site gets a security review.
- OUTPUT clauses and @@ROWCOUNT: mapped to RETURNING and GET DIAGNOSTICS, verified against application expectations.
Our conversion discipline
- Every converted procedure gets a unit test with production-derived fixtures before it is called "done".
- Procedures are ranked by execution frequency × business criticality; the top decile gets human review regardless of converter confidence.
- Behavioral diffs — not just "it compiles" — are the acceptance bar: same inputs, same outputs, same error behavior.
Moving the data without stopping the business
Two patterns cover nearly every estate: one-shot loads for the small and static, CDC replication for everything that matters.
The standard pattern: initial load + CDC
- Initial load with pgloader or AWS DMS full-load into the converted schema — parallelized by table, with LOB columns handled explicitly.
- Continuous replication via DMS CDC or Debezium (Kafka) captures SQL Server transaction-log changes into PostgreSQL, keeping lag in seconds.
- Validation jobs run row counts, checksums, and sampled value comparisons continuously — drift is detected in minutes, not at cutover.
What bites teams
- Case-sensitive collations changing join behavior on migrated data — caught by the validation queries, not by hope.
- Identity/sequence gaps after cutover: sequences are reset to max+1 during the final sync, verified, then verified again.
- Large-object columns and sparse wide tables: loaded in dedicated streams with their own retry logic.
- Cutover rehearsal: we run the full flip — stop writes, drain CDC, flip connection strings, smoke-test — at least twice before the real date.
Prove it before you flip it
Testing is a phase with its own calendar, not a checkbox the week before cutover.
Four test gates
- Parity testing: captured production workloads replayed against both systems; result sets diffed. Mismatches are triaged as converter bugs, semantic differences, or acceptable changes — each with a sign-off.
- Functional testing: application test suites run against PostgreSQL; converted procedures run their unit tests with production-derived fixtures.
- Performance validation: the top-100 production queries from workload profiling run against PostgreSQL at production data volumes. Targets are set from the SQL Server baseline — typically parity or better, with a remediation sprint for regressions.
- Failure testing: kill the primary, break replication, saturate connections. The runbook for each scenario is executed, not just written.
Performance validation specifics
- Execution-plan review for the top queries: missing indexes surface immediately because the planner differs.
- Connection architecture: PostgreSQL connections are heavier than SQL Server's — PgBouncer or RDS Proxy is sized and load-tested before cutover.
- Autovacuum and statistics tuning against your actual write patterns, not defaults.
Cutover day: boring by design
A good cutover is uneventful because every step was rehearsed. Here's the runbook shape.
Cutover sequence
- Freeze schema changes; confirm CDC lag near zero.
- Stop application writes to SQL Server; drain remaining CDC events.
- Final validation: row counts, checksums, sequence alignment.
- Flip connection strings / DNS / proxy to PostgreSQL.
- Smoke-test critical paths; open the war room for the agreed window.
- Keep SQL Server warm and replication-capable for the rollback period.
Rollback, defined up front
- Trigger criteria written before cutover: which metrics, observed for how long, force a rollback — decided calmly, executed automatically.
- Data reconciliation: writes that landed in PostgreSQL during the incident window are replayed or exported back; the procedure is tested, not theoretical.
- Rollback rehearsal is part of the second cutover rehearsal. A rollback plan nobody has run is a hope.
Post-migration optimization
- 30/60/90-day tuning: slow-query review, index cleanup from the migration's conservative indexing, autovacuum and checkpoint tuning against real load.
- Decommission plan for the SQL Server estate: license true-down, backup retention, and the date the old system is finally powered off.
- Team enablement: PostgreSQL operations runbook, monitoring dashboards, and on-call training so your team owns the new platform.
The migration checklist
Print this. If an item isn't checked, you're not ready for the next phase.
- Full estate inventory: databases, schemas, procedures, jobs, SSIS/SSRS artifacts, linked servers
- Workload profile captured: top-100 queries, peak concurrency, batch windows
- Dependency map: every application, report, and ETL consumer identified with its access method
- Compatibility scores per object; hand-conversion backlog sized and sequenced
- Type-mapping decisions documented (especially datetime semantics and collations)
- Converted schema deployed to a dev target; DDL under version control
- Unit tests for converted procedures with production-derived fixtures — all green
- SSIS/SSRS/Agent replacements built and scheduled in the new orchestrator
- CDC replication running with lag alerts; validation jobs reporting zero drift
- Parity test: production workload replayed, diffs triaged and signed off
- Performance validation at production data volumes; regressions remediated
- PgBouncer/connection pooling sized and load-tested
- Cutover runbook written; rehearsed twice; rollback triggers and procedure defined
- Monitoring, alerting, and backups verified on the PostgreSQL target
- Rollback rehearsal completed; SQL Server kept warm for the rollback window
- 30/60/90-day optimization plan and team enablement scheduled
Migration questions, answered straight
How long does a SQL Server to PostgreSQL migration take?
A straightforward OLTP workload with modest stored-procedure usage typically runs 8–16 weeks from assessment to cutover. Heavy T-SQL estates — thousands of procedures, SSIS packages, CLR code — run 4–9 months. The assessment phase produces the only estimate that matters: a per-object compatibility score for your estate.
What is the hardest part of migrating from SQL Server to PostgreSQL?
Procedural code. T-SQL and PL/pgSQL differ in error handling, temp tables, cursors, and dynamic SQL idioms, so automated converters handle 60–80% of routine code and the rest needs hand conversion plus unit tests. Application-side SQL and ORM queries are usually the easy part.
Can we migrate with zero downtime?
Near-zero, yes: initial load plus continuous change-data-capture replication keeps the PostgreSQL target in sync while the application still writes to SQL Server, then a short cutover window flips writes over. True zero downtime is rarely worth the complexity; a 15–60 minute planned window is the honest target for most estates.
Should we use AWS DMS, pgloader, or a custom pipeline?
pgloader is excellent for one-shot or repeatable full loads of moderate estates. AWS DMS (or Debezium for open-source stacks) is the standard choice when you need ongoing CDC replication into a cutover window. Custom pipelines make sense only for exotic types, complex transformations during flight, or multi-source consolidation.
What happens to SSIS, SSRS, and SQL Agent jobs?
They don't migrate — they get replaced. SSIS packages move to a modern orchestrator (Airflow, Dagster, or cloud-native pipelines), SSRS reports to your BI layer, and SQL Agent jobs to pgAgent, cron, or the orchestrator. This is usually 20–30% of total migration effort and must be scoped in the assessment, not discovered during cutover.
Do we need PostgreSQL expertise in-house before migrating?
You need it by cutover, not before kickoff. Our engagements include team enablement — operations runbook, monitoring, and on-call training — so your team owns the platform from day one. Many clients also keep us on a fractional DBA arrangement through the first two quarters.
Run the SQL Server → PostgreSQL migration with working instruments, not guesswork.
Discovery questionnaires, compatibility matrices, inventory workbooks, cutover and rollback checklists — free for practitioners.
Know your compatibility score before you commit.
A 2–3 week assessment: full inventory, per-object compatibility scoring, sequenced roadmap, and a fixed-scope build proposal. The deliverable is a decision, not a sales deck.