Migrating from SQL Server to PostgreSQL

SQL Server 2016 left extended support on July 14, 2026, and per-core licensing plus the CAL model is the reason most of these migrations get budget approval. But be clear about what you're signing up for: moving the tables and data is a solved problem that takes days. Rewriting T-SQL stored procedures, functions, and triggers into PL/pgSQL is the actual project, and its size depends entirely on how much logic lives in your database.

Worth saying up front: if your workload is heavily procedural (hundreds of stored procedures, SQL Agent jobs, SSIS packages), staying on SQL Server or moving to a compatibility layer like Babelfish may be cheaper than a full rewrite. Migrate cleanly when your database is mostly tables and constraints and the logic lives in application code.

The Three Approaches

There are also commercial/managed paths (AWS SCT + DMS, Azure DMS) that make sense if you're landing on a cloud-managed PostgreSQL anyway. This guide covers the self-managed tools.

Approach 1: pgloader

pgloader discovers the SQL Server schema automatically and builds tables, indexes, primary and foreign keys on the PostgreSQL side. The minimal form:

pgloader mssql://user:pass@mshost/dbname pgsql://pguser@pghost/dbname

For real migrations you want a load file:

load database
     from mssql://appuser:secret@sqlhost/sales
     into postgresql:///sales

including only table names like 'Orders', 'Customers' in schema 'dbo'

before load do $$ drop schema if exists dbo cascade; $$;

Two version notes (as of July 2026): the v3.6.9 release that apt ships is from 2022 and builds from SBCL; the v4 rewrite is a single Clojure/JVM JAR (Java 21+) that accepts the same load-file syntax but uses JDBC connection strings — jdbc:sqlserver://host;databaseName=sales — and fixes the heap-exhaustion failures v3 hits on large tables. v4 is still tagged as a dev release; test it on a staging copy first, and fall back to v3.6.9 if you hit gaps.

pgloader keeps SQL Server's dbo schema by default. Most teams ALTER SCHEMA dbo RENAME TO public afterwards (or set a rename in the load file) so tooling behaves.

Approach 2: tds_fdw

tds_fdw (2.0.5 as of September 2025, builds against PostgreSQL 13–18) exposes SQL Server tables as foreign tables inside PostgreSQL over the TDS protocol via FreeTDS:

CREATE EXTENSION tds_fdw;
 
CREATE SERVER sqlserver FOREIGN DATA WRAPPER tds_fdw
  OPTIONS (servername 'sqlhost', port '1433', database 'sales', tds_version '7.4');
 
CREATE USER MAPPING FOR postgres SERVER sqlserver
  OPTIONS (username 'appuser', password 'secret');
 
CREATE FOREIGN TABLE mssql_orders (
  id integer, customer_id integer, total_cents bigint, created_at timestamp
) SERVER sqlserver OPTIONS (schema_name 'dbo', table_name 'Orders');
 
-- Then copy at your own pace:
INSERT INTO orders SELECT * FROM mssql_orders;

You define target tables yourself (which forces you to make every type decision explicitly — a feature, not a bug) and you can transform in the SELECT. Slower than pgloader's bulk COPY path, but ideal for selective or incremental copies.

Approach 3: Babelfish

Babelfish is different in kind: it's a set of extensions (plus patches to community PostgreSQL) that make PostgreSQL speak the TDS wire protocol and execute T-SQL, including stored procedures. Your app keeps its SQL Server driver and connection string; the database underneath is PostgreSQL. It originated at AWS for Aurora but is Apache/PostgreSQL-licensed and self-hostable.

Honest assessment: Babelfish compatibility is broad but not complete — run its assessment tooling against your schema before committing. It's the right call when the stored-procedure rewrite is measured in person-years. It's the wrong call for a mostly-relational schema, where it adds a permanent compatibility layer you'll eventually want to remove anyway.

Data Type Mapping

The SQL Your Application Has to Change

Identifier behavior first, because it touches everything: SQL Server's default collations are case-insensitive; PostgreSQL is case-sensitive and folds unquoted identifiers to lowercase. WHERE email = 'Bob@X.com' silently stops matching. Fix with citext for equality-heavy columns or ILIKE/lower() at query sites, and let identifiers go lowercase rather than quoting "CamelCase" forever.

MERGE exists in PostgreSQL 15+, and simple upserts are usually better as INSERT ... ON CONFLICT. Stored procedures, functions, and triggers are line-by-line rewrites into PL/pgSQL — budget them by counting objects (SELECT COUNT(*) FROM sys.procedures) before you commit to a date. SQL Agent jobs need a replacement scheduler (pg_cron, or move them to application-level jobs).

Cutover and Validation

Freeze writes, run the final load, point the application, keep SQL Server read-only for a week as the rollback path. Validate with more than row counts:

-- On both sides, per table:
SELECT COUNT(*) FROM orders;
SELECT SUM(total_cents), MIN(created_at), MAX(created_at) FROM orders;
-- Spot-check text for collation/encoding surprises:
SELECT id, customer_name FROM orders ORDER BY random() LIMIT 20;

Aggregate checksums catch type-conversion damage (truncated datetimes, money rounding) that counts miss. Running the same validation query against both engines side-by-side is where a multi-database client earns its keep — Mako connects to SQL Server and PostgreSQL simultaneously, so you can diff results without juggling sqlcmd and psql.

Common Mistakes

For the underlying engine comparison, see PostgreSQL vs SQL Server. Re-pointing application code afterwards: the PostgreSQL connection guides cover drivers and pooling per language, and the T-SQL how-to series documents the dialect you're leaving.

Mako connects to SQL Server and PostgreSQL side-by-side with AI-powered autocomplete — useful for validating a migration query-by-query. Try it free at mako.ai.