SQL Server to PostgreSQL Migration: The Technical Guide

Microsoft's lifecycle page lists SQL Server 2016 with mainstream support ended on 13 July 2021 and extended support ended on 14 July 2026. Paid Extended Security Updates run in three yearly steps, from 15 July 2026 to 17 July 2029 (checked 31 October 2026). If a supported version is the only goal, upgrading SQL Server or buying Extended Security Updates is a much smaller change than changing engine.

Migrate when there is a reason beyond preference: a license cost you cannot justify at renewal, a platform standard, extensions such as PostGIS or pgvector, or a move to a managed PostgreSQL service. The rest of this guide assumes you have decided to move.

Start with a count of what is actually there. An object census shows how much procedural code you have:

SELECT type_desc, COUNT(*) AS n
FROM sys.objects
WHERE is_ms_shipped = 0
GROUP BY type_desc
ORDER BY n DESC;

A column-type census shows how many of the hard type cases you have:

SELECT t.name AS data_type, COUNT(*) AS n
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
JOIN sys.objects o ON o.object_id = c.object_id
WHERE o.is_ms_shipped = 0 AND o.type = 'U'
GROUP BY t.name
ORDER BY n DESC;

Then list what a schema tool will not see: SQL Agent jobs (msdb.dbo.sysjobs), linked servers (sys.servers where is_linked = 1), SSIS packages, SSRS reports, CLR assemblies, Service Broker queues and full-text catalogs. None of these is a database object that converts. Each needs a named replacement in the plan, such as pg_cron, foreign data wrappers or an external scheduler. Finally, record the database collation, the recovery model, the largest tables and the sustained write rate, because they decide how the data move and cutover work.

Babelfish Compass is a compatibility assessment tool for Babelfish for PostgreSQL. You point it at your T-SQL DDL and SQL scripts and it reports which constructs Babelfish supports and which it does not. Run it over the DDL and over the application SQL you can capture, such as ORM output and the top statements from query statistics. The report tells you whether Babelfish is a viable bridge, and which constructs would need a rewrite either way.

For the full-conversion route, the AWS Schema Conversion Tool or DMS Schema Conversion produces an assessment of how much of the schema and code converts automatically and puts action items into the converted code where it could not finish. Treat either report as an input. Classify every object as converts, needs review or needs rewrite, and keep the list: it becomes the project plan and the regression checklist.

Babelfish for PostgreSQL is an open source project, licensed under Apache 2.0 and the PostgreSQL license, that lets PostgreSQL understand the SQL Server wire protocol (TDS 7.1 and higher) and T-SQL. Its documentation lists OLEDB, ODBC, JDBC and ADO.NET Entity Framework clients as working, and says the jTDS driver is not supported. Its docs also have pages on single versus multiple database setups, collations and language support, and migration workarounds.

The table lists the type decisions that most often matter. Tools apply defaults, and the point is to know where you are overriding them.

On the full-conversion route, simple procedures convert quickly. The table covers the constructs that cause rewrites and surprises:

Three tools cover most data moves. They differ mainly in whether they can keep PostgreSQL in sync while SQL Server still takes writes.

AWS lists the SQL Server prerequisites for DMS ongoing replication: the database must be set up for full backups with a backup taken before replication starts, and the recovery model must be Full or Bulk-logged. Transaction log entries are not marked for reuse until the capture job has processed them, which means a stalled capture job fills the log:

ALTER DATABASE appdb SET RECOVERY FULL;
BACKUP DATABASE appdb TO DISK = N'D:\backup\appdb.bak';

-- for MS-CDC based capture (DMS or Debezium)
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
     @source_schema = N'dbo', @source_name = N'orders', @role_name = NULL;

CDC capture and cleanup run as SQL Server Agent jobs, so the Agent must be running, and you need to monitor log size and capture latency for the whole migration. AWS also documents that DMS can lose events if SQL Server truncates the log before DMS reads it, that tables without a primary key capture only inserts and deletes unless MS-Replication or MS-CDC is configured, and that all columns of a unique index must be NOT NULL when CDC is used. Fix these on the source before the load starts.

Debezium. The SQL Server connector takes a snapshot and then reads the CDC tables, streaming change events into Kafka or, through Debezium Server, into other sinks. The same source prerequisites apply.

pgloader reads SQL Server directly, discovers the schema, and loads tables, indexes, primary keys and foreign keys. A load file makes the choices explicit and repeatable. Check the options against pgloader's MS SQL reference for the version you run:

LOAD DATABASE
     FROM mssql://migrator@mssql-host/appdb
     INTO postgresql://migrator@pg-host/appdb
 WITH include drop, create tables, create indexes, foreign keys, reset sequences;

Rehearse on a copy of production and time each phase. Where a write freeze of a few hours is acceptable this may be the whole data move. Otherwise load a snapshot, then let AWS DMS or Debezium carry the changes that arrive during and after the load. If you are using Babelfish, test the actual bulk-load path you intend to use against your volumes, because that is the part of the toolchain most specific to your estate.

Run validation continuously while the target syncs, not once at the end:

• Row counts and aggregates per table, plus hashes or sums over key ranges, because equal counts hide type coercion and timestamp shifts.
• Sequences: every identity or serial value must sit above the table's maximum. After a load with explicit IDs, advance each one:

SELECT setval(pg_get_serial_sequence('orders', 'id'),
              COALESCE(MAX(id), 1), MAX(id) IS NOT NULL)
FROM orders;

• Case-only duplicates: common SQL Server collations are case-insensitive, so unique columns can hold values that collide under PostgreSQL's case-sensitive default. Find them before the application does:

SELECT lower(email) AS e, count(*)
FROM users
GROUP BY 1
HAVING count(*) > 1;

• Real queries: replay the top statements against both databases, compare results, then compare plans on the PostgreSQL side with EXPLAIN ANALYZE. Indexes that suited SQL Server's optimizer may not suit PostgreSQL's.
• Jobs and reports: run the month-end procedures and scheduled reports, not just the screens, and compare the numbers.
• Behavior that changed on purpose: NOLOCK removal, time zone handling and NULL ordering. SQL Server sorts NULLs first in ascending order and PostgreSQL sorts them last.

1. Days before: freeze schema changes, lower DNS time to live on the database hostname, and rehearse the full runbook at least once on a copy.
2. Stop writes: put the application in maintenance mode or revoke write permissions on SQL Server. From here the write freeze clock is running.
3. Drain: wait until the capture lag is nil and the last changes have applied.
4. Compare: run the final counts, aggregates and sequence checks. Do not skip this to save minutes.
5. Switch: repoint the application, start it, and smoke-test the critical paths.
6. Rollback: until PostgreSQL takes its first write, SQL Server is untouched and rolling back means pointing back. After that you need reverse replication or a replay of the writes, so agree the point of no return in advance and write it into the change request.

Never combine the migration with a SQL Server upgrade or a major application release in the same window. If the source has to be upgraded first, that is its own change.

AceMQ plans and runs migrations to PostgreSQL from SQL Server, Oracle and MySQL: assessment, a proof of value on one database, conversion, validation and a rehearsed cutover. See database migration to PostgreSQL for the method and deliverables, and PostgreSQL support or managed PostgreSQL services for the platform afterwards.

Sources, all read on 31 October 2026: Microsoft lifecycle page for SQL Server 2016; AWS documentation for SQL Server as a DMS source; pgloader's MS SQL reference; and the Babelfish for PostgreSQL site, FAQ and Babelfish Compass README.