Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 2 · Lesson 13 · 6 min read

SQL Server to PostgreSQL Migration Tools Compared

On this page
  1. SQL Server to PostgreSQL migration tools compared
  2. pgloader: one command, schema and data
  3. AWS DMS: low downtime with change capture
  4. Babelfish: when rewriting isn't an option yet
  5. pg_dump and pg_restore
  6. Validating a migration
  7. Check yourself

SQL Server to PostgreSQL migration tools compared

A migration has four parts: the schema (tables, types, constraints), the data, the code (procedures, functions, triggers, views), and validation. Tools are good at the first two, partial at the third, and useless at the fourth unless you tell them what to check. Pick tools per part, not one tool for everything.

ToolMovesBest forWatch out for
pgloaderSchema and data, straight from SQL ServerOne-off migrations with a maintenance windowDoesn't convert procedures, triggers or views. Review its type choices.
AWS DMS + DMS Schema ConversionData with ongoing change capture, plus converted schema and codeLarge databases moving to RDS or Aurora with little downtimeConversion flags what it can't handle, and you rewrite that by hand
Azure Data FactoryDataCopying tables into Azure Database for PostgreSQLNo schema or code conversion
Babelfish for Aurora PostgreSQLLets PostgreSQL accept T-SQL and SQL Server connectionsHuge T-SQL codebases you can't rewrite soonAurora only, and not every T-SQL feature
pg_dump / pg_restorePostgreSQL to PostgreSQLCopying the finished target between environmentsCan't read from SQL Server

One correction to a common assumption: Azure Database Migration Service doesn't migrate SQL Server to PostgreSQL. It handles moves between similar engines, such as SQL Server to Azure SQL. For Azure targets, convert the schema yourself or with another tool, then copy the data with Azure Data Factory, pgloader or a CDC tool.

pgloader: one command, schema and data

pgloader reads directly from a live SQL Server and writes into PostgreSQL. It creates tables, converts types, copies rows in parallel, builds indexes and sets sequences afterwards. A command file describes the job:

SQL
LOAD DATABASE
  FROM mssql://migration_user:secret@sqlserver-host/SalesDB
  INTO postgresql://postgres:secret@pg-host/salesdb

WITH include drop, create tables, create indexes, reset sequences,
     workers = 4, concurrency = 1

CAST type money    to numeric,
     type datetime to timestamptz

INCLUDING ONLY TABLE NAMES LIKE 'Customers', 'Orders' IN SCHEMA 'dbo';

Run it with pgloader salesdb.load. Rows that fail to load are written to a reject file instead of stopping the whole run, so check that file every time. INCLUDING ONLY is useful for a trial on a few tables before the full run.

Treat pgloader's output as a draft. Review the generated tables against the type rules from Day 5 and the naming rules from Day 4. Its SQL Server support goes through the FreeTDS driver and is less polished than its MySQL support, so test early. And it leaves your procedures, functions, triggers and views alone. That's the work from Days 8 to 11.

AWS DMS: low downtime with change capture

For large databases where a long maintenance window isn't an option, AWS Database Migration Service runs in two phases:

  1. Full load. It copies every table to the target, often RDS for PostgreSQL or Aurora PostgreSQL.
  2. Change data capture (CDC). It keeps reading changes from SQL Server and applying them to PostgreSQL, so the target stays a few seconds behind while you test.

At cutover you stop writes to SQL Server, let DMS apply the last changes, switch the application's connection string and start writing to PostgreSQL. Downtime is minutes, not hours.

Schema and code conversion is a separate step. DMS Schema Conversion, or the older desktop AWS Schema Conversion Tool, analyzes your T-SQL and generates PostgreSQL DDL and PL/pgSQL. It produces a report of what it converted automatically and what needs a human. Expect the report to show work for complex procedures, dynamic SQL and SQL Server-specific features.

Babelfish: when rewriting isn't an option yet

Babelfish for Aurora PostgreSQL makes PostgreSQL accept SQL Server's network protocol (TDS) and a large part of T-SQL. Applications can sometimes connect with little more than a new connection string, and existing stored procedures run as written.

It's a bridge, not a destination. It only runs on Aurora PostgreSQL (an open-source version exists, but needs a specially built PostgreSQL), it doesn't support every T-SQL feature, and you keep maintaining T-SQL instead of moving to PostgreSQL idioms. Consider it when a system has thousands of procedures and a fixed deadline. Plan the gradual move to native PL/pgSQL anyway.

pg_dump and pg_restore

These are PostgreSQL's own logical backup tools (Day 23 covers them fully). They can't read SQL Server, but you'll use them during a migration to copy a finished, validated target from staging to production, or to snapshot it before a risky step:

Shell
pg_dump -Fc -f salesdb.dump salesdb          # custom format: compressed, restorable selectively
createdb salesdb_prod
pg_restore -d salesdb_prod -j 4 salesdb.dump  # parallel restore

One step people forget after loading data with explicit ids: move each sequence past the highest id, or the first new insert collides:

SQL
CREATE TABLE orders (order_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY);
INSERT INTO orders VALUES (1), (2), (500);   -- loaded with explicit ids
SELECT setval(pg_get_serial_sequence('orders', 'order_id'), (SELECT max(order_id) FROM orders));
INSERT INTO orders DEFAULT VALUES RETURNING order_id;   -- 501

pgloader's reset sequences option does this for you. Hand-written loads need it explicitly.

Validating a migration

Every tool reports success. Only validation tells you the data is right. Before cutover, check at least:

  • Row counts for every table, source against target.
  • Aggregates on important columns, such as sum(order_total), min and max of dates, and counts per status.
  • Spot checks of known records, like your biggest customer's order history.
  • Edge values: NULLs, empty strings, very long text, dates before 1900, the largest money amounts, non-Latin characters.
  • Orphans: child rows without parents, if constraints were added after loading.
  • Application smoke tests against the target with real workflows.

DataGrip helps here. Add both servers as data sources, run the same validation query in a console on each, and compare the results side by side.

Check yourself

Azure DMS doesn't support SQL Server to PostgreSQL. Convert the schema separately, and move data with Azure Data Factory, pgloader or a CDC-capable tool.

In pgloader's reject file and log, which hold rows that failed to convert or load. Then add row-count comparison to your standard validation checklist.

AWS DMS with a full load followed by change data capture, so the target stays in sync until a short cutover. Use DMS Schema Conversion for the schema and a first pass at the code.

It lets SQL Server applications connect over the TDS protocol and run much of their T-SQL unchanged against Aurora PostgreSQL. The catch is coverage and lock-in: not every T-SQL feature is supported, behavior can differ subtly, and it ties you to Aurora. It's best as a bridge while you rewrite.

Counts can match while values differ, for example truncated strings, shifted time zones or rounded money. Compare aggregates per column, such as sums and min and max dates, and a sample of rows field by field, so silent conversion errors are caught.