SQL Server to PostgreSQL migration: a practical checklist
Moving off SQL Server is rarely hard because of the data. It's hard because of everything that quietly depends on it. Here's the checklist I use to keep a migration predictable.
Why teams move to PostgreSQL
The usual drivers are licensing cost, the wish to run the same database on any cloud or on-premise, and PostgreSQL's mature feature set: strong standards compliance, JSONB, full-text search, rich indexing and a large extension ecosystem. None of that matters, though, if the migration breaks reports, integrations or month-end jobs. So the work starts with an inventory, not with a data copy.
1. Assess before you move anything
- Inventory every object: tables, views, stored procedures, functions, triggers, SQL Agent jobs, linked servers, CLR assemblies and SSIS packages.
- Find every consumer: applications, BI tools, ETL jobs, spreadsheets with live connections and third-party integrations. This list usually surprises people.
- Flag SQL Server–specific features in use:
IDENTITY,MERGE,TOP,NOLOCKhints, temp tables, table variables,FOR XML, and cross-database queries. - Measure the data: sizes, row counts and growth, so you can plan load windows realistically.
2. Map the data types
Most types translate cleanly, but a few cause subtle bugs if you map them on autopilot.
| SQL Server | PostgreSQL | Watch out for |
|---|---|---|
INT IDENTITY | INT GENERATED … AS IDENTITY | Reset sequences to MAX(id)+1 after loading |
NVARCHAR(n) / VARCHAR(MAX) | VARCHAR(n) / TEXT | PostgreSQL is UTF-8 throughout; check collation and case sensitivity |
DATETIME / DATETIME2 | TIMESTAMP | Decide deliberately between TIMESTAMP and TIMESTAMPTZ |
BIT | BOOLEAN | Application code comparing to 0/1 |
UNIQUEIDENTIFIER | UUID | Default generation: gen_random_uuid() |
MONEY | NUMERIC(19,4) | Avoid PostgreSQL's money type; it depends on locale |
VARBINARY(MAX) | BYTEA | Large binaries may belong in object storage instead |
One more trap: SQL Server's default collation is usually case-insensitive, while PostgreSQL compares text case-sensitively. Queries like WHERE email = 'A@x.com' can silently return nothing. Use citext, a case-insensitive collation, or lower-cased indexes where it matters.
3. Convert the code, not just the schema
T-SQL stored procedures have to be rewritten in PL/pgSQL. Tools such as AWS Schema Conversion Tool, pgloader or Babelfish can speed up the mechanical part, but plan for manual review of:
- Procedures that return multiple result sets (use functions returning tables, or refactor)
- Error handling:
TRY…CATCHbecomesBEGIN … EXCEPTION … END - Temp tables and table variables, dynamic SQL, and
MERGElogic (often better asINSERT … ON CONFLICT) - Scheduled SQL Agent jobs, which move to
pg_cron, the cloud scheduler or the application layer
4. Move the data and prove it arrived
- Run a full trial load in a staging environment first and time it.
- Validate with row counts per table, checksums or hash comparisons on key columns, and a set of business-level checks: totals by month, open invoices, active customers.
- For large or always-on systems, use change data capture (for example AWS DMS or Debezium) so the final cut-over only has to sync the last few changes.
5. Test what the business actually uses
Unit tests rarely catch migration issues. Replay real workloads: the top reports, the slowest queries, month-end jobs and every integration. Compare results between old and new side by side, and compare query plans for anything that got slower. PostgreSQL's planner is different, and indexes that were perfect on SQL Server may need rethinking.
6. Plan the cut-over like a release
- A written runbook with owners and timings for every step
- A freeze window and a clear go/no-go checkpoint
- A rollback path that has actually been rehearsed
- Monitoring in place from minute one: errors, slow queries, connection counts
The short version
- Inventory objects and consumers
- Map types deliberately, especially collation and timestamps
- Rewrite T-SQL with review, not blind conversion
- Load, then prove the data with counts, hashes and business checks
- Replay real workloads and compare results
- Cut over with a rehearsed runbook and rollback
Done this way, a SQL Server to PostgreSQL migration becomes a planned engineering project instead of a risky weekend. If you are planning one, see how I run SQL Server to PostgreSQL migrations.