// work / database administration

SQL Server to PostgreSQL migration with near-zero downtime

Moved a logistics company's 400 GB SQL Server estate to PostgreSQL with a rehearsed dual-write cutover, cutting licence costs and unblocking a move to Linux containers.

Client
Freight and logistics operator
Industry
Logistics
Year
2025
Role
Database administrator and migration lead
Duration
9 weeks
Aerial view of a shipping container yard
Photo: @chuttersnap on Unsplash

Context

A freight operator ran its tracking, billing and customer portal on SQL Server. The platform was fine, but the licences were a large and growing line item, the application team was moving to Linux containers, and the one person who understood the 300 stored procedures was retiring.

The problem

Migrate roughly 400 GB across 412 tables to PostgreSQL, with the business running 24 hours a day and an acceptable write freeze measured in minutes. Nothing could be lost, and the application team needed a clean rollback if anything looked wrong in the first week.

Approach

Inventory first. I catalogued every table, procedure, function, job and linked server, and ranked them by how often they were called and how much T-SQL-specific behaviour they used. About a third of the procedures turned out to be dead. They were dropped rather than ported.

Schema conversion. Types were mapped deliberately rather than automatically: datetime to timestamptz with a documented offset policy, money to numeric(19,4), uniqueidentifier to uuid. Identity columns became identity columns, not sequences bolted on afterwards. Every difference was recorded in a conversion log the application team reviewed.

Procedures. The remaining 190 procedures were rewritten in PL/pgSQL, with the handful that relied on SQL Server-only behaviour (such as MERGE edge cases and TRY_CONVERT) rewritten in the application instead. Each rewrite shipped with a test that ran the same inputs through both engines and compared the result.

Keeping both in step. Rather than a big-bang copy, I used change data capture from SQL Server into Kafka, with a small .NET service applying changes to PostgreSQL. A validation job sampled and compared rows continuously, so both databases ran in parallel for three weeks with the difference count visible on a Grafana dashboard. It reached zero in week two and stayed there.

Rehearsals. The cutover was rehearsed three times end to end on production-sized copies, each time shortening the runbook. The final rehearsal took 14 minutes of write freeze.

Cutover. On the night, writes were frozen at 01:00, the last changes drained, the validation job confirmed zero drift, connection strings flipped, and writes resumed at 01:11. SQL Server stayed warm and reverse-replicated for a week as the rollback path. It was never needed.

Outcome

Eleven minutes of write freeze for a 400 GB migration, with 1.9 billion rows validated against the source. Licence costs dropped by roughly 70% a year, the application now runs in Linux containers, and the procedure logic is in version control with tests for the first time. Post-migration tuning of the top 20 queries left the platform faster than it had been on SQL Server.

Need something like this?

This is the kind of work I do under database administration. Tell me about your version of the problem.