SQL Server to PostgreSQL migration: schema conversion, data types, and cutover

How do I migrate a SQL Server database to PostgreSQL?

Convert the schema, backfill the data once, keep the two databases in step with incremental loads, then move the application and turn the old instance off. Four passes, each reversible on its own. The reason to do it this way rather than as a single dump and restore is that a parallel run gives you something a big-bang cutover never does: two live systems you can compare row counts and financial totals across before anyone commits.

The first pass is the schema, and it decides everything after it. Pull the table, view, index, and constraint definitions out of the source catalog, translate each column to its PostgreSQL equivalent, and then sit with the twenty or so columns where the translation is a judgment call rather than a lookup. Microsoft ships SQL Server Migration Assistant, AWS ships the Schema Conversion Tool, and both handle the mechanical bulk well. Neither can tell you whether a DECIMAL(18,2) column called Amount is money that must never round or a quantity that can. That is an hour with whoever owns the general ledger, and it is the cheapest hour in the whole project.

The second pass is the backfill. Extract every table once and load it. For anything past a few million rows this means bulk export to files and COPY on the PostgreSQL side, because COPY is roughly an order of magnitude faster than row-by-row inserts. Run the extract against a readable secondary or a restored backup rather than the primary, because a full scan of your largest fact table will compete with the transactions that pay the bills. Load with the indexes dropped and rebuild them afterward, which on a large table is the difference between an afternoon and two days.

The third pass is where the project actually lives. Between the backfill and the cutover the two databases have to agree, which means loading only the rows that changed since the last run. That bookkeeping is unglamorous and it is where hand-rolled migrations quietly break: a job dies halfway, the watermark advanced anyway, and a day of orders is missing from a table nobody checks until quarter end. It is what the SQL Server to PostgreSQL connector owns: the watermark, the staging table, the upsert, the retry, and a log per run showing what moved.

The fourth pass is the one nobody plans for. Reports, extracts, SSIS packages, linked servers, scheduled jobs, and someone's Excel workbook all point at SQL Server. Inventory every consumer before you cut over, because the migration is not finished when the data is in PostgreSQL. It is finished when nothing is reading the old instance. Give yourself a fortnight of parallel running and check the totals daily.

What is the SQL Server to PostgreSQL data type mapping?

Most types map straight across. INT stays INT, VARCHAR stays VARCHAR, NUMERIC keeps its declared precision. The handful worth deciding by hand are MONEY, which should become NUMERIC(19,4), the datetime family, where only DATETIMEOFFSET earns TIMESTAMPTZ, and UNIQUEIDENTIFIER, which belongs in the native UUID type.

The table below is the mapping we use, with the AWS SQL Server to Aurora PostgreSQL migration playbook as the reference point for the mechanical conversions and a note where we deviate from a default converter on purpose.

One mapping worth extra thought is JSON. SQL Server stores JSON in NVARCHAR(MAX) and queries it with JSON_VALUE. PostgreSQL has JSONB, which parses once, stores a binary form, and can be indexed with GIN. If a column holds JSON that anything actually queries, converting it to JSONB during the migration is free performance you will not get later without a rewrite.

Why does my query break after migrating from SQL Server to PostgreSQL?

Nine times out of ten it is identifier case. PostgreSQL folds unquoted identifiers to lowercase, so a table created as CREATE TABLE Customers is physically customers. SQL Server preserves the case you typed and, on a default collation, compares identifiers case insensitively. The two behaviors only collide when a conversion tool quotes the original names.

That is the trap. If your converter emits CREATE TABLE "Customers" ("CustomerID" INT), the mixed case is now baked in, and every query for the rest of that database's life has to quote it too. SELECT CustomerID FROM Customers will fail, because unquoted it resolves to customerid, which does not exist. Pick one convention at conversion time. We fold everything to lowercase snake_case and update the application references once, because the alternative is quoting forever and an ORM that occasionally forgets.

Case sensitivity in data is the second surprise. SQL Server's default collation compares string values case insensitively, so WHERE Email = '[email protected]' matches a stored [email protected]. PostgreSQL compares case sensitively and that same query returns nothing. Either normalize on write, index lower(email) and query through it, or use the citext extension. Whichever you pick, find every equality comparison on a user-entered string before cutover rather than after.

The third is implicit casting. SQL Server happily concatenates an INT onto a string. PostgreSQL wants ::text or an explicit CAST. It is a mechanical fix, but there are usually more of them than anyone expects, and they only surface when the statement runs.

What are the biggest SQL Server to PostgreSQL migration challenges?

Stored procedures, identifier case, collation behavior, and the reporting estate. Data volume almost never makes the list. Bulk loading rows is fast and well understood, while translating T-SQL to PL/pgSQL is line-by-line work that needs someone who understands what the procedure is for, not just what it says.

A few specifics that cost real time. T-SQL table variables and temp tables behave differently from PostgreSQL temporary tables, which are transaction or session scoped and are visible to the planner in a different way. TOP n becomes LIMIT n, and TOP n WITH TIES becomes a window function. ISNULL becomes COALESCE, GETDATE() becomes now(), and MERGE exists in modern PostgreSQL but the older idiom is INSERT ... ON CONFLICT DO UPDATE. Error handling moves from TRY/CATCH to EXCEPTION blocks. None of these is hard. There are just a lot of them.

Then there is everything hanging off the database. SSIS packages, linked servers, SQL Server Agent jobs, SSRS reports, and Reporting Services subscriptions have no direct equivalents, so each one becomes a small project of its own. Count them early. A team that discovers forty Agent jobs in week six has just lost its schedule.

During the cutover window itself, keep something watching the application from the outside. A migration that technically succeeded but left one service pointed at a connection string that no longer answers looks identical from the database side, so an external check that polls your endpoints and the database port every thirty seconds tells you within a minute rather than when a customer calls.

What is the best SQL Server to PostgreSQL migration tool?

It depends on which half of the problem you have. Schema and procedural code conversion is a different job from moving rows continuously, and most teams end up using one tool for each. Here is the honest split.

The practical combination for most mid-market teams is a free converter for the DDL, a human for the procedures, and a managed sync for the weeks of parallel running in between. That middle phase is the one people try to script and regret, because it has to survive weekends, schema drift, and a failed batch at 3 a.m. without losing a day of rows. If you want the arguments for and against writing that yourself, we walk through them in build versus buy for integration.

Is PostgreSQL cheaper than SQL Server?

Yes, on licensing, and by a wide margin. PostgreSQL has no license fee. Microsoft's published list price for SQL Server 2022 is $15,123 per two-core pack for Enterprise and $3,945 per two-core pack for Standard, with a minimum of four cores per instance. A 16 core Enterprise instance is therefore around $121,000 at list before Software Assurance, and that is per instance, so development and staging count too.

The savings are real but they are not free money. You still pay for hardware or managed hosting, and you may pay for a PostgreSQL support contract. The migration itself costs engineering time measured in months for anything with substantial procedural code. Most teams we talk to justify the move on a combination of license spend, the ability to run the same engine on any cloud, and getting out of core-count audits, rather than on licensing alone.

Is PostgreSQL faster than SQL Server?

Neither is categorically faster. On the same hardware with equivalent indexing, both handle typical OLTP workloads comparably, and published benchmarks tend to reflect the tuning effort more than the engine. Where they differ is in what they optimize for natively, and that is what actually shows up in your workload.

SQL Server's columnstore indexes give it a genuine advantage on large analytical scans out of the box, and its query optimizer has decades of work behind it. PostgreSQL wins on extensibility, on JSONB with GIN indexing, and on the fact that its planner behavior is transparent enough to reason about. Expect to spend time on autovacuum tuning that SQL Server never asked of you, and expect the PostgreSQL connection model to need a pooler such as PgBouncer at connection counts SQL Server absorbs without comment. Plan for both and the performance conversation stops being a surprise.

How long does a SQL Server to PostgreSQL migration take?

A single reporting database with a few dozen tables and no procedural code takes days. A departmental application database with a hundred tables, a few dozen stored procedures, and a reporting estate takes a quarter. A core system with thousands of objects takes six months to a year. The variable is procedural code and consumers, not gigabytes.

Run the assessment first. SSMA and AWS SCT both produce an object-by-object report that scores how much manual effort each one needs, and that report is the only credible input to a timeline. Counting tables tells you almost nothing. Counting stored procedures that the tool marked as needing manual conversion tells you most of what you need.

Can I keep SQL Server and PostgreSQL in sync during the migration?

Yes, and you should. Read only the rows that changed since the last run using an indexed ModifiedDate watermark, or read the change data capture tables when you also need deletes, then upsert into PostgreSQL with INSERT ... ON CONFLICT DO UPDATE keyed on the primary key. Advance the stored watermark only after the upsert commits.

Two details decide whether this holds up. First, deletes: a watermark load cannot see a hard delete, because the row is gone and nothing changed. Either soft delete in the source, enable CDC, or reconcile primary keys between the two databases on a schedule and remove the orphans. Second, if you do enable CDC, watch the transaction log. With CDC on, the log truncation point does not advance until the capture process has gathered the changes, and that holds even under simple recovery. A capture job that stalls will fill the disk on the instance still running production.

The same incremental pattern is what our SQL Server to PostgreSQL connector runs on a schedule, with the mapping visible in the browser and a log per run. If your destination is a warehouse rather than another operational database, the SQL Server to Snowflake migration guide covers the same decisions with columnar targets in mind, and the MySQL to PostgreSQL migration guide covers the other common route into Postgres. For a wider look at what these tools cost and where each fits, see the best data integration tools.