Skip to main content
Migration

SQL Server to PostgreSQL Migration

Move a SQL Server workload to PostgreSQL with verified schema parity, data validation, and a cutover plan that accounts for T-SQL rewrites and operational differences.

Complexity
High

Executive Summary

SQL Server → PostgreSQL migrations are most often blocked by SQL dialect differences (T-SQL → PL/pgSQL), data type semantics, and application assumptions around collation/identity behavior. Treat the migration as schema + code + operations, not just data export/import.

Migration Profile

| Dimension | Typical value | |---|---| | Complexity | High | | Downtime | None (dual-write) to hours (big bang) | | Primary risks | Stored procedure translation, query performance, edge-case data semantics | | Validation | Row counts + checksums + critical query comparison |

Options

  • Big bang cutover: migrate schema + data, freeze writes, cut traffic.
  • CDC / dual-run: replicate changes during migration and cut over with minimal downtime.
  • Strangler: migrate one schema/service at a time (best when apps can be decomposed).

Runbook (phased)

Phase 0 — Assessment

  • Inventory: tables, indexes, views, functions, stored procedures, SQL Agent jobs, SSIS packages.
  • Identify blockers: CLR, linked servers, special collations, temp table heavy workloads.

Phase 1 — Schema Translation

  • Map types (e.g. uniqueidentifier → uuid, datetime2 → timestamptz, money → numeric).
  • Convert identity/sequence behavior and constraints.

Phase 2 — Code Translation

  • Rewrite T-SQL procedures/functions to PL/pgSQL (or move logic to application layer).
  • Update ORMs/drivers (connection string, parameter styles, pagination).

Phase 3 — Data Migration

  • Bulk load initial snapshot.
  • Validate with counts + sample checksums; reconcile mismatches early.

Phase 4 — Performance + Operations

  • Recreate indexes with Postgres-specific tuning.
  • Review autovacuum, statistics, connection pooling, and monitoring.

Phase 5 — Cutover

  • Freeze writes (if big bang) or stop replication (if CDC).
  • Switch application traffic and run smoke tests for critical flows.
  • Keep rollback path (DNS/traffic switch + preserved SQL Server snapshot).

Keywords

sql serverpostgresqldatabasemigration
08:53Z[DRIFT]Next.jsNext.js is 2 major versions behind (current: 14.2.35, latest: 16.1.6).
08:54Z[OWASP]A03:2021 – InjectionUnescaped user input rendered into HTML template (src/routes/admin.ts:42)
08:52Z[SCANNER]semgrepscan signature set is up to date
08:48Z[DRIFT]of dependencies are 2+ major versions behind in acme.39% of dependencies are 2+ major versions behind in acme.
08:50Z[OWASP]A02:2021 – Cryptographic FailuresJWT secret is hardcoded — use environment variables (src/auth/jwt.ts:18)
08:45Z[SCANNER]gitleaksscan signature set is up to date
08:43Z[DRIFT]@types/node@types/node is 3 major versions behind (spec: 22.15.29, latest: 25.2.3).
08:46Z[OWASP]A03:2021 – InjectionRegular expression built from user input — potential ReDoS (src/utils/search.ts:67)
08:38Z[SCANNER]trufflehogstatus: unavailable
08:38Z[DRIFT]electronelectron is 3 major versions behind (spec: ^37.6.0, latest: 40.4.1).
08:42Z[OWASP]A03:2021 – InjectiondangerouslySetInnerHTML used with potentially untrusted content (src/components/RichText.tsx:31)
08:33Z[DRIFT]@types/node@types/node is 5 major versions behind (spec: ^20.17.52, latest: 25.2.3).
08:38Z[OWASP]A05:2021 – Security MisconfigurationCookie set without httpOnly or secure flags (src/middleware/session.ts:12)
08:28Z[DRIFT]@types/supertest@types/supertest is 4 major versions behind (spec: ^2.0.16, latest: 6.0.3).
08:34Z[OWASP]A03:2021 – Injectioneval() called with dynamic expression (src/utils/template-engine.ts:88)
08:23Z[DRIFT]VitestVitest is 4 major versions behind (current: 0.34.6, latest: 4.0.18).
08:30Z[OWASP]A01:2021 – Broken Access ControlRedirect URL comes from user-controlled parameter (src/pages/auth/callback.tsx:15)
08:18Z[DRIFT]@types/node@types/node is 5 major versions behind (spec: ^20.8.0, latest: 25.2.3).
08:26Z[OWASP]A03:2021 – InjectionUnsanitised input passed to MongoDB query (src/services/users.ts:34)
08:13Z[DRIFT]vitestvitest is 4 major versions behind (spec: ^0.34.6, latest: 4.0.18).
08:22Z[OWASP]A03:2021 – InjectionChild process spawned with user-controlled arguments (src/utils/pdf-generator.ts:52)
08:08Z[DRIFT]of dependencies are 2+ major versions behind in @acme/api.31% of dependencies are 2+ major versions behind in @acme/api.
08:18Z[OWASP]A05:2021 – Security MisconfigurationExternal link opened without rel="noreferrer" (src/components/ExternalLink.tsx:8)
08:03Z[DRIFT]@types/node@types/node is 5 major versions behind (spec: ^20.11.0, latest: 25.2.3).
08:14Z[OWASP]A02:2021 – Cryptographic FailuresMath.random() used for token generation — use crypto.randomBytes (src/utils/token.ts:6)
07:58Z[DRIFT]of dependencies are 2+ major versions behind in @acme/workflow-engine.52% of dependencies are 2+ major versions behind in @acme/workflow-engine.
08:10Z[OWASP]A05:2021 – Security MisconfigurationExpress app without Helmet security headers middleware (src/server.ts:1)
07:53Z[DRIFT]@types/node@types/node is 5 major versions behind (spec: ^20.19.9, latest: 25.2.3).
07:48Z[DRIFT]@types/node@types/node is 3 major versions behind (spec: ^22.15.29, latest: 25.2.3).
08:53Z[DRIFT]Next.jsNext.js is 2 major versions behind (current: 14.2.35, latest: 16.1.6).
08:54Z[OWASP]A03:2021 – InjectionUnescaped user input rendered into HTML template (src/routes/admin.ts:42)
08:52Z[SCANNER]semgrepscan signature set is up to date
08:48Z[DRIFT]of dependencies are 2+ major versions behind in acme.39% of dependencies are 2+ major versions behind in acme.
08:50Z[OWASP]A02:2021 – Cryptographic FailuresJWT secret is hardcoded — use environment variables (src/auth/jwt.ts:18)
08:45Z[SCANNER]gitleaksscan signature set is up to date
08:43Z[DRIFT]@types/node@types/node is 3 major versions behind (spec: 22.15.29, latest: 25.2.3).
08:46Z[OWASP]A03:2021 – InjectionRegular expression built from user input — potential ReDoS (src/utils/search.ts:67)
08:38Z[SCANNER]trufflehogstatus: unavailable
08:38Z[DRIFT]electronelectron is 3 major versions behind (spec: ^37.6.0, latest: 40.4.1).
08:42Z[OWASP]A03:2021 – InjectiondangerouslySetInnerHTML used with potentially untrusted content (src/components/RichText.tsx:31)
08:33Z[DRIFT]@types/node@types/node is 5 major versions behind (spec: ^20.17.52, latest: 25.2.3).
08:38Z[OWASP]A05:2021 – Security MisconfigurationCookie set without httpOnly or secure flags (src/middleware/session.ts:12)
08:28Z[DRIFT]@types/supertest@types/supertest is 4 major versions behind (spec: ^2.0.16, latest: 6.0.3).
08:34Z[OWASP]A03:2021 – Injectioneval() called with dynamic expression (src/utils/template-engine.ts:88)
08:23Z[DRIFT]VitestVitest is 4 major versions behind (current: 0.34.6, latest: 4.0.18).
08:30Z[OWASP]A01:2021 – Broken Access ControlRedirect URL comes from user-controlled parameter (src/pages/auth/callback.tsx:15)
08:18Z[DRIFT]@types/node@types/node is 5 major versions behind (spec: ^20.8.0, latest: 25.2.3).
08:26Z[OWASP]A03:2021 – InjectionUnsanitised input passed to MongoDB query (src/services/users.ts:34)
08:13Z[DRIFT]vitestvitest is 4 major versions behind (spec: ^0.34.6, latest: 4.0.18).
08:22Z[OWASP]A03:2021 – InjectionChild process spawned with user-controlled arguments (src/utils/pdf-generator.ts:52)
08:08Z[DRIFT]of dependencies are 2+ major versions behind in @acme/api.31% of dependencies are 2+ major versions behind in @acme/api.
08:18Z[OWASP]A05:2021 – Security MisconfigurationExternal link opened without rel="noreferrer" (src/components/ExternalLink.tsx:8)
08:03Z[DRIFT]@types/node@types/node is 5 major versions behind (spec: ^20.11.0, latest: 25.2.3).
08:14Z[OWASP]A02:2021 – Cryptographic FailuresMath.random() used for token generation — use crypto.randomBytes (src/utils/token.ts:6)
07:58Z[DRIFT]of dependencies are 2+ major versions behind in @acme/workflow-engine.52% of dependencies are 2+ major versions behind in @acme/workflow-engine.
08:10Z[OWASP]A05:2021 – Security MisconfigurationExpress app without Helmet security headers middleware (src/server.ts:1)
07:53Z[DRIFT]@types/node@types/node is 5 major versions behind (spec: ^20.19.9, latest: 25.2.3).
07:48Z[DRIFT]@types/node@types/node is 3 major versions behind (spec: ^22.15.29, latest: 25.2.3).