SQL Server to PostgreSQL Migration Using pgloader

The short answer

pgloader migrates a Microsoft SQL Server database to PostgreSQL in one command: it reads the source catalog, creates the tables, streams the rows with PostgreSQL's COPY protocol, then builds indexes, primary keys and foreign keys. The minimum is two connection strings:

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

For a real migration, write a load file that sets casting rules, filters tables and renames the dbo schema. pgloader is free and open source. It does not convert T-SQL: stored procedures, functions, triggers, SQL Agent jobs and the SQL inside your applications are a separate piece of work. The SQL Server to PostgreSQL migration guide covers those differences. This post is the pgloader manual in practice. If an end-of-support date is behind the move, the SQL Server 2016, 2017 and 2019 posts give the dates.

Which pgloader version you are running

The last tagged stable release is 3.6.9, from October 2022. It connects to SQL Server through the FreeTDS driver, and its documentation is the one this post follows. The project also publishes a version 4 development build, a Java application that needs Java 21 or later and uses Microsoft's JDBC driver instead of FreeTDS. The "latest" documentation on Read the Docs describes version 4, so check which version you have installed before copying examples, because connection handling and some defaults differ between the two.

Step 1: Install pgloader

Debian packages pgloader, and the PostgreSQL community repositories (apt.postgresql.org and yum.postgresql.org) carry it too. A Docker image is published for each tagged release:

# Debian or apt.postgresql.org
apt-get install pgloader

# Docker
docker run --rm -it dimitri/pgloader:latest pgloader --version

Building from source needs SBCL and the FreeTDS development headers (freetds-dev on Debian). Run pgloader on a host close to both databases, because every row passes through it.

Step 2: Configure FreeTDS

pgloader 3.6.9 expects data from SQL Server in UTF-8. Set the TDS protocol version and client character set in ~/.freetds.conf for the user running pgloader:

[global]
    tds version = 7.4
    client charset = UTF-8

Because 3.6.9 connects through FreeTDS, its documentation says to change the server port by exporting the TDSPORT environment variable. Test the connection with a small database before pointing pgloader at production.

Step 3: Run a first default migration on a copy

Restore a recent backup of the source to a scratch SQL Server and run the one-line command against an empty PostgreSQL database. The defaults show what pgloader does with your schema before you start overriding them:

pgloader --summary summary.txt \
  mssql://migrator@mssql-copy/Sales \
  pgsql://pg_owner@pg-test/sales

By default pgloader creates the same schemas as the source, so tables arrive in a PostgreSQL schema named dbo. Rows PostgreSQL refuses are written to a reject.dat file, with the reasons in reject.log. Read both after every run. A clean summary with rejected rows is not a clean migration.

Step 4: Check the default casts

These are pgloader's built-in MS SQL type mappings in 3.6.9:

Step 5: Write a load file

A load file makes the migration repeatable. This one migrates the dbo tables of a sales database into public, keeps string lengths, maps datetimes without a time zone, skips two staging tables and lists every option it relies on:

load database
  from mssql://migrator@mssql-copy/Sales
  into postgresql://pg_owner@pg-test/sales

with include drop, create tables, create indexes, foreign keys,
     reset sequences, workers = 4, concurrency = 1

set work_mem to '64MB', maintenance_work_mem to '512MB'

cast type datetime  to timestamp,
     type datetime2 to timestamp,
     type nvarchar  to varchar keep typemod,
     type varchar   to varchar keep typemod

including only table names like '%' in schema 'dbo'
excluding table names like 'stg_%' in schema 'dbo'

alter schema 'dbo' rename to 'public'

before load do $$ create schema if not exists public; $$;

pgloader --dry-run sales.load   # tests the load file without loading data
pgloader sales.load

Notes on the clauses:

• include drop drops matching target tables with CASCADE before recreating them, so you can rerun the file from a clean state. Never point it at a database that holds anything else.
• reset sequences moves each sequence to the current maximum of its column once the load and indexes are done.
• cast rules are matched before the defaults. keep typemod keeps the declared length, so nvarchar(40) becomes varchar(40).
• including only and excluding use SQL Server's LIKE patterns and need the schema name.
• alter schema and alter table names matching rename in pgloader's memory, not in PostgreSQL, so renamed tables keep their indexes and foreign keys.
• materialize views (not used above) loads the result of a view, or of a query you define, as a table. It is how pgloader handles views: as data, not as view definitions.

Two more checks after the first run. Identifiers: pgloader can downcase names or quote them to keep their case. Downcasing is what you want unless every query in the application will be rewritten with quotes. Column defaults: the version 4 documentation drops MS SQL default expressions unless a rule keeps them, because they are often not valid PostgreSQL, so compare defaults on both sides whichever version you use.

Step 6: Plan the cutover around pgloader's model

pgloader copies a snapshot. It does not capture changes made after it reads a table, so the source must stop taking writes for the duration of the final run. Rehearse that run on production-sized data and time it. If it does not fit your window, split the work: move large, append-only history tables ahead of time and load only the active tables during the outage, or use a change-data-capture tool for the final sync. AWS DMS can capture changes from SQL Server once the database uses the Full or Bulk-logged recovery model, has a full backup, and has MS-Replication or Change Data Capture turned on, which you enable per database with EXEC sys.sp_cdc_enable_db and per table with sys.sp_cdc_enable_table. DMS does not capture changes to memory-optimized tables, and it does not support SQL Server Express as a source.

Cutover itself:

1. Freeze schema changes and deployments on SQL Server.
2. Stop application writes and SQL Agent jobs that modify data.
3. Run the final pgloader load, then compare row counts and checksums on key tables.
4. Confirm sequences sit above the highest key, apply post-load SQL, run ANALYZE.
5. Switch connection strings and run smoke tests.

Rollback is simple while nothing has been written to PostgreSQL: point the application back at SQL Server, which you left intact and read-only. Once PostgreSQL takes writes, going back means copying those writes the other way, so set a deadline for the decision and keep it short.

What pgloader does not migrate

pgloader's MS SQL documentation covers tables, data, indexes, primary keys, foreign keys and views materialized as tables. Everything else is yours:

• Stored procedures, functions and triggers in T-SQL. Rewrite them in PL/pgSQL. Procedures that return result sets usually become set-returning functions.
• View definitions. Views come across as data only if you materialize them. Recreate the real views by hand.
• SQL Agent jobs, linked servers and Service Broker. Agent jobs live in SQL Server's own job system and need a new scheduler.
• CLR objects. Procedures, functions, triggers, types and aggregates written in .NET and loaded as assemblies have no PostgreSQL counterpart.
• Logins, users and permissions. Recreate roles and grants.
• Collation behaviour. SQL Server databases are often case-insensitive and PostgreSQL's default collations are not. The migration guide shows three fixes.
• Application SQL. TOP, ISNULL, + for strings, square-bracket names and GETDATE() all need edits in code that pgloader never sees. To capture the statements an application sends, use Extended Events; Microsoft has deprecated SQL Trace and SQL Server Profiler.

Free alternatives and other SQL Server to PostgreSQL migration tools

pgloader and a T-SQL rewrite give you native PostgreSQL that runs anywhere. Babelfish keeps more of the existing code and suits applications you cannot change; the trade-off is set out in SQL Server to Aurora PostgreSQL: Babelfish vs full conversion.

Where OSSeva fits

OSSeva assesses each SQL Server application, moves the data with tools such as pgloader, runs old and new side by side and supports the PostgreSQL cluster from the day you cut over, as described on SQL Server to PostgreSQL with OSSeva. Support covers community PostgreSQL on bare metal, VMs, any Kubernetes or any cloud account, with patched builds for versions past community end of life. Pick the target major version with the PostgreSQL major version upgrade guide in mind, so the new cluster does not start close to its own end-of-life date. OSSeva Operate patches Critical CVEs (CVSS ≥ 9.0) within 48 hours and High within 7 days. If your plan depends on Babelfish, the Babelfish project or Aurora's managed Babelfish is the better fit, because OSSeva supports community PostgreSQL rather than Babelfish's modified build. Support is priced per cluster; book a discovery call for a quote, or model your current support spend first.

Frequently asked questions

How do you migrate Microsoft SQL Server to PostgreSQL using pgloader?

Install pgloader, configure FreeTDS for UTF-8, then run pgloader mssql://user@host/db pgsql://user@host/db for a default migration. For production, write a load file with CAST rules, table filters and alter schema 'dbo' rename to 'public', test it with --dry-run, and convert T-SQL routines separately.

Is there a free SQL Server to PostgreSQL migration tool?

Yes. pgloader, sqlserver2pgsql, Ora2Pg, Babelfish and Babelfish Compass are all open source. None of them converts all T-SQL code automatically.

Does pgloader migrate stored procedures?

No. It migrates tables, data, indexes and keys, and can materialize views as tables. Procedures, functions and triggers have to be rewritten in PL/pgSQL.

Why does pgloader turn datetime into timestamptz?

That is its default cast for datetime and datetime2. SQL Server stores those values without a time zone, so if they are local times, add cast type datetime to timestamp, type datetime2 to timestamp to keep them unchanged.

How do I keep varchar lengths with pgloader?

Add CAST rules with keep typemod, such as type nvarchar to varchar keep typemod. The built-in rules map every character type to text and drop the length.

Can pgloader migrate SQL Server with no downtime?

No. It copies a snapshot and does not replicate later changes. Stop writes for the final run, or use a CDC tool such as AWS DMS to keep PostgreSQL in sync until cutover.

Can pgloader read from Azure SQL Database?

The version 4 documentation describes Azure SQL connections through JDBC URLs with Entra ID authentication. The 3.6.9 documentation does not cover Azure SQL, so test the connection with the version you install.