Data Migration: SQL Server to PostgreSQL — the Complete Guide

Published Jun 29, 2026
by

A data migration from SQL Server to PostgreSQL rarely fails at actually copying the data. It fails at the silent differences that only surface in the target: datetime, which knows no time zone, bit, which is not a boolean, an IDENTITY that turns into a sequence, and a collation that suddenly compares case-sensitively. Anyone who sets out to migrate SQL Server to PostgreSQL isn’t just copying tables — they’re translating types, schema, code and behaviour from one engine into another.

This guide gives the overview: it sorts the move into five phases and names the key trip-ups for each. The depth lives in a dedicated article per phase — this one supplies the through-line that ties the phases together.

The essentials up front:

  • Five phases instead of “moving data”: the move breaks down into data types, schema/DDL, data transfer, code porting and verification. Tackling them in this order spares you the typical setbacks.
  • Each phase with its own trip-up: sometimes it’s breaking types, sometimes IDENTITY becomes a sequence, sometimes it’s the transfer method or translating T-SQL into PL/pgSQL. None of them shows up until it strikes in the target — which is why verification comes last.
  • What tooling takes off your plate: tools like pgloader handle the mechanical ~80% — the uncritical types and the bulk transfer. The last 20% — type edge cases, procedure logic, triggers — stay handwork.
  • The through-line: each phase has its own detailed article with the full depth. This hub connects them and tells you in which order they play together.

Prerequisite: a basic grasp of relational databases. SQL Server 2017+ as the source, PostgreSQL 16/17 as the target. Postgres concepts (sequences, text, COPY, PL/pgSQL) are placed in context briefly on first mention — no prior Postgres knowledge required, T-SQL basics yes.

Contents

Why migrate SQL Server to PostgreSQL?

The reasons are rarely technical — they’re usually economic or strategic. Licensing costs fall away: PostgreSQL is open source and usable without core or CAL licences. The open-source stack runs freely on any infrastructure, with no vendor lock-in. And cloud portability is high — nearly every provider offers managed PostgreSQL.

This is no holy war over “Postgres is better than SQL Server”. Both engines are mature, and not every workload belongs on the move. The point is sober: once the decision to migrate SQL Server to PostgreSQL has been made, it pays to plan the move as a structured path rather than a one-off copy operation. That’s exactly what this guide is for.

The migration path in five phases

A database migration is not a single step but a chain of dependent phases. Tackling them in this order avoids the typical setbacks — such as transferring data before the target schema has the right types. Each phase has its own detailed article.

1. Data types. The foundation. Most types convert one-to-one (int, numeric, varchar, date) — but datetime, bit, money, uniqueidentifier, nvarchar and tinyint have no one-to-one equivalent. datetime forces the time-zone question (timestamp vs. timestamptz), bit becomes a real boolean, and the Postgres money type is best left untouched. Which types convert cleanly and which break is covered in Data Type Mapping SQL Server → PostgreSQL.

2. Schema & DDL. Once you have the types comes the structure around them: IDENTITY becomes GENERATED AS IDENTITY or a sequence, default constraints and named constraints move over, and the case sensitivity of identifiers flips from SQL-Server-tolerant to Postgres-exact. Details in Schema Migration SQL Server → PostgreSQL.

3. Data transfer. Only once the target schema stands do the data move. The choice of method depends on data volume and downtime tolerance: a bcp export plus COPY, the all-in-one tool pgloader, or an ETL pipeline. Which method fits when is compared in Transferring Data: bcp, COPY, pgloader, ETL.

4. Code porting. Tables are only half the database. Stored procedures, functions and triggers have to be translated from T-SQL to PL/pgSQL — different error handling, different variable syntax, different idioms (TRY_CONVERT has no direct counterpart). This is the part with the highest handwork share. Explored in depth in Porting T-SQL to PL/pgSQL.

5. Verification. A migration is only done once you’ve proven that nothing was lost or silently corrupted: row reconciliation per table, spot-check comparisons, data-quality checks after the load. How to check that systematically is shown in Verifying the Migration — Data Quality and Row Reconciliation.

The phases build on one another but can be iterated — it’s typical to run types and schema together on a pilot table before transferring the full load.

One Table, Several Phases at Once

To make the phase path tangible, a small table that bundles several trip-ups at once. First the source in T-SQL:

  1: CREATE TABLE dbo.customer
  2: (
  3:     customer_id   int             IDENTITY(1, 1)  NOT NULL
  4:    ,full_name     nvarchar(100)   NOT NULL
  5:    ,is_active     bit             NOT NULL  DEFAULT 1
  6:    ,created_at    datetime        NOT NULL  DEFAULT GETDATE()
  7:    ,CONSTRAINT pk_customer PRIMARY KEY (customer_id)
  8: );

And the same as a PostgreSQL target:

  1: CREATE TABLE customer
  2: (
  3:     customer_id   integer       GENERATED BY DEFAULT AS IDENTITY
  4:    ,full_name     text          NOT NULL
  5:    ,is_active     boolean       NOT NULL  DEFAULT true
  6:    ,created_at    timestamp     NOT NULL  DEFAULT now()
  7:    ,CONSTRAINT pk_customer PRIMARY KEY (customer_id)
  8: );

Four columns, and already two phases interlock — data types and schema — in a table nobody would call migration-critical. Three of the changes you see coming:

  • Line 3 (schema): int IDENTITY(1, 1) → integer GENERATED BY DEFAULT AS IDENTITY — the auto-value column moves to the SQL-standard mechanism. The real work comes after the load: the sequence must be advanced to the highest value, or the next INSERT collides.
  • Line 5 (data type): bit → boolean, 1 becomes true. Beware: ported queries like WHERE is_active = 1 break in Postgres.
  • Line 6 (data type): datetime → timestamp. The trap isn’t now() instead of GETDATE(), but that datetime carries no time zone: whether timestamp or timestamptz is right depends on whether the values were meant as local time or UTC — if you don’t decide deliberately, it shifts silently later.

The fourth one slips by when you skim — and is the most likely to go unnoticed:

  • Line 4 (data type): nvarchar(100) → text. “Postgres is natively Unicode, so text” is true — but it hides that the length limit disappears. This is exactly what an auto-converter like pgloader reaches for by default: nvarchar(100) becomes text, the limit drops without being asked. If the 100 was just technical baggage, text is the right choice. If it was a business rule — the database was not allowed to accept anything longer — a silent validation has been lost, and what belongs there is a varchar(100) or a CHECK constraint rather than bare text. The tool makes the translation automatically — but whether the limit was intended by the domain, it cannot know: that question isn’t in the type, it’s in the domain.

That’s the whole guide in one table: four lines that look like search-and-replace carry two phases and at least one trap you only see if you know the data by its meaning — not just its type.

What tooling takes off your plate — and where handwork remains

The honest expectation-setting first: there is no “one click and done”. Tools like pgloader (free, takes schema and data over in a single run) or commercial converters handle the mechanical bulk — and that’s a lot: the uncritical types, the bulk transfer, the standard constraints. As a rule of thumb they cover around 80% of the mechanics.

The remaining 20% are exactly the spots where a business decision is needed that no tool can know:

  • Breaking types — datetime → timestamp or timestamptz? money → which numeric scale? tinyint with a preserving CHECK constraint? These cases need checking, not blind adoption.
  • Procedure and trigger logic — T-SQL to PL/pgSQL is translation work, not search-and-replace.
  • Performance tuning — indexes, statistics and query plans differ; what was fast in SQL Server may need a different index in Postgres.
  • Behavioural differences — collation/case, NULL ordering, transaction semantics on errors.

The art of migration lies not in transferring the easy 80% but in the clean, verified translation of the hard 20%. This cluster devotes a dedicated article to each of these spots.

FAQ

How long does a SQL-Server-to-Postgres migration take?

It depends on schema complexity and the amount of code, not primarily on data volume. The data transfer itself is often done in hours; the time goes into code porting (stored procedures, triggers) and verification. A simple database with little logic is doable in days, one with hundreds of procedures in weeks to months.

Can I automate this completely?

No. Tools like pgloader take the mechanical ~80% off your plate — the uncritical types and the bulk transfer. The last 20% (breaking types, procedure logic, triggers, performance tuning) need human decisions. Expecting “one click and done” builds in silent errors.

What happens to my stored procedures?

They have to be ported from T-SQL to PL/pgSQL — that’s the most demanding phase. Error handling (TRY/CATCH → EXCEPTION), variable syntax and many idioms differ. There’s no direct translation tool; the dedicated article on code porting shows the patterns.

Do I need downtime?

For the simplest variant (export → transfer → switch over), yes — the source is ideally read-only during the transfer so no changes are lost. Low-downtime strategies (logical replication, gradual cutover) are possible but considerably more involved and a topic of their own.

Should I migrate gradually or as a big bang?

For most solo and mid-size scenarios, a big-bang cutover in a quiet maintenance window is pragmatic. Gradual migration (both systems in parallel) pays off for large, continuously available systems — but at the cost of considerable synchronisation complexity.

Does this also work for Azure SQL or other source databases?

The phases (types → schema → transfer → code → verification) apply generally, and Azure SQL is closely related to SQL Server — much of it carries over. This cluster, though, is specifically tailored to SQL Server → PostgreSQL; Oracle or MySQL as the source bring different type and dialect traps.

This guide is the overview of the cluster on migrating from SQL Server to PostgreSQL. Each phase has its own detailed article:

For more on adjacent topics: