When you are migrating SQL Server to PostgreSQL
The schema conversion is the part everyone worries about and the part that turns out to be mechanical. What sinks migrations is everything attached to the schema that nobody enumerated first.
The situation
A licence renewal, a cloud move or a cost review has produced a decision to leave SQL Server. The decision arrived before the scope did, and the first question — how big is this — has no answer because nobody has an inventory of what depends on the current database.
Converting the DDL is a solved problem and takes an afternoon. The work that takes months is the views, the procedures, the jobs, the reports, and the applications holding assumptions about identity columns and collation. None of that appears in a schema diff.
So the useful first move is not conversion. It is building an inventory that makes the size of the job visible, and doing it in a form you can query rather than a spreadsheet that goes stale during the project.
You are here if
- You have been asked for a timeline and cannot give one with a straight face.
- Nobody can list which applications talk to the database.
- There are stored procedures whose owners have left.
- A previous attempt stalled and nobody has written down why.
How the work gets done
Read the real schema, not the one in the wiki
Import the SQL Server schema — tables, views, indexes, triggers and procedures — from a script or from the running database. What you want out of this step is not a picture; it is a count. How many procedures, how many views, how many tables that nothing references.
The unreferenced objects are the first win on almost every migration. A meaningful fraction of most legacy schemas is dead, and migrating it is pure cost.
Data ▸ Import DDL / Mermaid ERData ▸ Live DB Import
Model the target rather than translating the source
A migration is the one moment when changing the model is cheap, and translating SQL Server structures one-for-one throws that away. Decide deliberately where the target should differ, and record the decision next to the thing it applies to.
Naming is the clearest example. If the source schema has three naming conventions in it, the migration is when that gets fixed, and a standards check will tell you where the target has drifted before the DDL is generated rather than after it ships.
Data ▸ Naming StandardsData ▸ Validate Model
Map source to target where they differ
Where a table splits, merges or changes shape, record the mapping as a mapping rather than as a note in a ticket. The people writing the migration scripts and the people writing the reconciliation queries are usually not the same people, and they need the same document.
Data ▸ Source→Target Map
Generate the PostgreSQL DDL from the model
With the target modelled, the DDL is generated rather than hand-written, which removes an entire class of transcription error and makes the target reproducible when — not if — you need to rebuild it.
Subsequent rounds are a delta rather than a full rebuild, which matters because the target schema will change several times before cutover.
Diagram ▸ DDL (SQL)Diagram ▸ PostgreSQLData ▸ Delta DDL / Migration
Know what breaks before the cutover, not during it
Impact analysis over the modelled dependencies answers the question the cutover plan actually turns on: if this table changes shape, what else is affected. Doing that by reading code is how a migration discovers a dependency at two in the morning.
Environment comparison covers the other half — confirming that what you built in the target matches what you intended, before anything is pointed at it.
Data ▸ Impact AnalysisData ▸ Env Compare
What you end up holding
- A counted inventory of the source schema, including the objects nothing references.
- A modelled target schema that generates PostgreSQL DDL, and a delta script for each subsequent round.
- A source-to-target mapping for everything whose shape changes, and an impact list per table.
Not this, if
- Your source is Oracle or your target is Snowflake. The DDL importer reads PostgreSQL, MySQL and SQL Server; the exporter writes PostgreSQL, MySQL, BigQuery and SQL Server. Outside those, you would be retyping the schema, which defeats the point.
- The migration is a lift-and-shift with no schema change at all. A conversion tool will do it, and the modelling work here earns nothing back.
- You want automated data movement. Nothing here moves rows — this is the design and dependency side of the migration, not the ETL.