SQL Server to PostgreSQL Migration: The Type Map, the T-SQL That Breaks, and a Cutover Plan That Does Not Lose Rows

A SQL Server to PostgreSQL migration is three separate jobs that people try to do as one: converting the schema, where the data types and identity columns need a mapping decided by a person; moving the data, where a tool such as pgloader does the bulk transfer in one command once the schema is right; and rewriting the code, meaning every stored procedure, trigger and application query that uses T-SQL syntax Postgres does not have. The first and third are where the time goes. The second is an evening. Plan the cutover as a rehearsal you run twice against a copy before you run it once for real, reset every sequence after the load, and keep the SQL Server instance read-only rather than off for the first week, because the rollback you never need is the cheapest insurance you will buy.

The motivation is almost always the licence, and the migration is almost always more work than the licence saving suggests in the first month and less than it suggests over three years. What follows is the order that avoids doing anything twice.

Step one: inventory before you convert anything
Step two: the type map
Step three: pgloader does the schema and data in one pass
Step four: the T-SQL that does not translate
Step five: rehearse the cutover, then do it
How this fits the rest of the stack
FAQ

Step one: inventory before you convert anything

Before touching a type, list what the database contains, because the count of each category is the estimate.

-- on SQL Server
SELECT type_desc, COUNT(*) FROM sys.objects
WHERE is_ms_shipped = 0
GROUP BY type_desc;

-- tables by row count, largest first
SELECT t.name, SUM(p.rows) AS rows
FROM sys.tables t JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN (0,1)
GROUP BY t.name ORDER BY rows DESC;

Tables and indexes convert mechanically. Views usually convert with syntax fixes. Stored procedures, functions and triggers are the hand-written part, and their count times their average length is the number that decides whether this is a week or a quarter. A database with two hundred tables and four procedures is a small migration. A database with twenty tables and four hundred procedures is a rewrite, and the honest answer may be to move the logic into the application rather than translate it line by line.

Step two: the type map

Most types have an obvious equivalent. The ones below are where a wrong choice costs data or performance.

INT, BIGINT, SMALLINT map directly. TINYINT has no Postgres equivalent; use SMALLINT.
DECIMAL(p,s) and NUMERIC map directly. MONEY should become NUMERIC(19,4), not Postgres’s own money type, which is locale-dependent and awkward.
BIT becomes BOOLEAN. Check that application code comparing to 1 and 0 still works, or cast in a view.
NVARCHAR(n) and VARCHAR(n) become VARCHAR(n) or plain TEXT; Postgres is UTF-8 throughout so the N prefix is redundant. NVARCHAR(MAX) becomes TEXT.
DATETIME and DATETIME2 become TIMESTAMP. DATETIMEOFFSET becomes TIMESTAMPTZ. Decide deliberately whether stored times were UTC, because Postgres will not guess.
UNIQUEIDENTIFIER becomes UUID, which is native and indexed properly.
VARBINARY(MAX) and IMAGE become BYTEA.
XML becomes XML; JSON stored in NVARCHAR should become JSONB, which is a genuine upgrade.
IDENTITY(1,1) becomes GENERATED ALWAYS AS IDENTITY or a SERIAL column backed by a sequence. Either way, the sequence must be reset after the data load, which is the step everyone forgets.

Two structural differences bite beyond types. Unquoted identifiers are case-insensitive in both, but Postgres folds them to lowercase while SQL Server preserves case; a schema with CustomerID becomes customerid unless quoted, and quoted identifiers are a maintenance burden forever. Fold everything to lowercase and fix the application. And string comparison in SQL Server is case-insensitive by default under most collations, while Postgres is case-sensitive; queries relying on WHERE email = '[email protected]' matching a lowercase row will silently return nothing until you add LOWER() or a case-insensitive collation.

Step three: pgloader does the schema and data in one pass

pgloader connects to SQL Server, reads the catalogue, creates equivalent tables in Postgres using its default type mapping, copies the data in parallel, and creates the indexes and constraints afterwards. A load file overrides the parts of the default mapping you disagree with.

LOAD DATABASE
  FROM mssql://app_user:[email protected]/AppDb
  INTO postgresql://app_user:[email protected]/appdb

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

CAST type datetime to timestamp,
     type nvarchar to text,
     type money to numeric(19,4),
     type bit to boolean using bits-to-boolean

INCLUDING ONLY TABLE NAMES LIKE 'dbo.%'

ALTER SCHEMA 'dbo' RENAME TO 'public';

pgloader migrate.load
# review the summary table it prints: rows read, rows written, errors per table

Read the summary. Any table where the two row counts differ has rows that failed conversion, and pgloader writes those rows to a reject file with the reason. Most rejects are a datetime outside the Postgres range, a string that is not valid UTF-8, or a decimal beyond the declared precision. Fix the source data or add a cast rule and rerun that table.

The reset sequences option does what the name says, but verify it. After the load, every identity-backed column’s sequence must be at or above the maximum existing value, or the first insert after cutover fails with a duplicate key.

SELECT setval(pg_get_serial_sequence('orders', 'id'), COALESCE(MAX(id), 1)) FROM orders;

Step four: the T-SQL that does not translate

Stored procedures move from T-SQL to PL/pgSQL by hand or with a conversion tool that gets you most of the way. The constructs that always need attention:

TOP n becomes LIMIT n, and TOP n WITH TIES needs a window function.
ISNULL(a, b) becomes COALESCE(a, b). GETDATE() becomes NOW() or CURRENT_TIMESTAMP. LEN() becomes LENGTH(). CHARINDEX becomes POSITION or STRPOS.
String concatenation with + becomes ||, and + on a NULL yields NULL in both, but the implicit conversions SQL Server allowed will now raise errors.
DATEADD, DATEDIFF and CONVERT with style codes become interval arithmetic and TO_CHAR. This is where most procedure rewriting time goes.
Temporary tables #temp become CREATE TEMP TABLE or, more often, a CTE.
MERGE exists in modern Postgres, but INSERT ... ON CONFLICT is usually what you want and is simpler.
Procedures that return result sets need to become functions returning SETOF or TABLE, or be replaced by views, or be moved into the application.
TRY/CATCH becomes BEGIN ... EXCEPTION WHEN ... END, and transaction control inside procedures is more restricted.

The application layer has its own list: connection strings, the driver, parameter placeholders, SELECT SCOPE_IDENTITY() becoming RETURNING id, and any query that relied on case-insensitive string matching. If the application uses an ORM, most of this is a dialect switch and a test run. If it uses hand-written SQL, it is a grep for every construct above.

Step five: rehearse the cutover, then do it

Rehearsal one, against a restored copy of production. Run pgloader, run the converted procedures, run the application’s test suite against the Postgres copy, and time the whole thing. Fix everything that fails. Note the duration.
Rehearsal two, the same, timed. If it runs clean, the duration is your maintenance window plus a margin.
Cutover. Put the application in maintenance mode. Set the SQL Server database to read-only rather than stopping it. Run the load. Reset sequences. Run a row-count comparison for every table and a checksum on the largest ones. Point the application at Postgres. Run the smoke tests. Open the doors.
Rollback, if needed. Point the application back at SQL Server, which has lost nothing because it was read-only. This is the reason not to shut it down.
Decommission after a week of clean operation, and only after a final backup of the SQL Server instance is stored somewhere it can be restored from.

For a database too large to load inside an acceptable window, the alternative is continuous replication: pgloader for the initial load, then change data capture streaming from SQL Server to Postgres until the two are in sync, then a cutover measured in seconds. That is a different project with more moving parts and it is worth it only when the window genuinely does not exist.

How this fits the rest of the stack

The destination decides half of the operational story. A managed Postgres on RunxBuild comes with backups, user management and a connection limit per plan, which are the things the SQL Server DBA used to own and the things a migration plan tends to leave to later. The RunxBuild hosting calculator puts that database next to the service that queries it, so the licence saving can be compared against the actual monthly figure rather than against a hope.

Useful related references:

FAQ

What is the best tool to migrate SQL Server to PostgreSQL?

pgloader for the schema and bulk data: it reads the SQL Server catalogue, creates matching Postgres tables with a configurable type map, copies rows in parallel and rebuilds indexes. Stored procedures, triggers and application SQL still need manual or tool-assisted rewriting from T-SQL to PL/pgSQL.

How do SQL Server data types map to PostgreSQL?

Most map directly: INT to INT, DECIMAL to NUMERIC, UNIQUEIDENTIFIER to UUID, VARBINARY to BYTEA. The ones to decide deliberately are NVARCHAR to TEXT, DATETIME to TIMESTAMP versus TIMESTAMPTZ, MONEY to NUMERIC(19,4), BIT to BOOLEAN, TINYINT to SMALLINT, and IDENTITY to an identity column with its sequence reset after loading.

Why do inserts fail with duplicate key errors after migration?

Because the sequences behind identity columns were not advanced past the loaded data, so the first new insert reuses an existing id. Run setval on every serial or identity column’s sequence to the current maximum after the load. pgloader’s reset sequences option does this, but verify it.

Why does a query return no rows in Postgres when it worked in SQL Server?

Usually string case. SQL Server’s default collations compare strings case-insensitively; Postgres compares them case-sensitively. Add LOWER() on both sides, use the citext extension, or define a case-insensitive collation for the affected columns.

How do I migrate with minimal downtime?

Rehearse the full load against a copy twice to learn the duration, then cut over inside that window with SQL Server set read-only for rollback. If the window is too long, do an initial pgloader load followed by change data capture replication until the two databases are in sync, then switch in seconds.