Disclosure: I'm the author of dbSlice, a Windows tool, and I use it in the example. The five details below apply to any tool, and to a hand-written script too.

When an application moves from SQL Server to PostgreSQL, the schema usually gets the attention. The data is where the surprises are.

1. datetimeoffset loses its offset

A common mapping is TIMESTAMP, with values written in UTC. If the application needs the time zone, declare the column TIMESTAMPTZ in the CREATE TABLE before the load.

2. bit becomes BOOLEAN

1 and 0 arrive as true and false. Every query that compares with = 1 has to change.

3. nvarchar(max), ntext and xml

Use TEXT. PostgreSQL text is Unicode, so there is no N prefix. Declare XML yourself if you query it as XML.

4. rowversion / timestamp is not a date

It is binary in SQL Server (BYTEA in PostgreSQL). Leave it out of the copy unless the application reads it.

5. Case and collation

SQL Server usually compares text case-insensitively; PostgreSQL does not by default. Plan ILIKE, citext or a case-insensitive collation wherever the application relies on it.

How I check it

  1. Create the target tables from reviewed DDL. dbSlice does not create anything in your database by default: if the PostgreSQL table is missing it stops and shows a CREATE TABLE translated from SQL Server, for you or your DBA to review and run. That is where fixes 1 to 4 go.
  2. Initial load, then a nightly upsert by key (use a column that only grows, like updated_at or the IDENTITY id) so PostgreSQL stays in step until cutover.
  3. Fix the sequences. After rows are copied with their IDENTITY values, the PostgreSQL sequence can be behind the highest id. The tool can move it past the max id, or show the command.
  4. Compare key by key before the switch. The compare saves the list of divergent keys to a file.

Replicator copying a list of tables

What it does not do

It moves data, not T-SQL: stored procedures, triggers and views have to be rewritten for PostgreSQL. Incremental runs do not detect deletes at the source, so compare or reload before cutover. No CDC or SQL Server Agent is needed: it reads with ordinary queries.

Try it

There is a free version on the Microsoft Store (connects, browses, compares, exports CSV). Writing to the target needs a license.

Which of these has bitten you? I'm happy to answer questions about the type mapping.