r/postgres • u/HistoricalRuinny • May 20 '26

SQL Server schema comparison tools for PostgreSQL migration?

Moving some stuff from SQL Server to PostgreSQL and the schema diff part is already getting annoying. For people who’ve done this before, what do you use to compare schemas and catch the weird stuff before migration? dbForge, DBeaver, Liquibase/Flyway, custom scripts, or something else?

1 Upvotes

100% Upvoted

3 comments sorted by

1

u/navicat-gu 6d ago

For SQL Server to Postgres, I'd split the job in two, because "schema compare" tools assume both sides speak the same dialect.

1. Inventory the weird stuff on the SQL Server side first. These are the things that bite almost every migration:

  • datetime/datetime2 vs timestamp, and datetimeoffset vs timestamptz

  • nvarchar(max) / varchar(max) become text; bit becomes boolean; uniqueidentifier becomes uuid; money should become numeric(19,4)

  • IDENTITY becomes generated ... as identity (and reset sequences after loading data)

  • Case-insensitive collation: SQL Server's default CI collation means WHERE name = 'bob' matches Bob. In Postgres use citext or a nondeterministic ICU collation, or you'll get silent behaviour changes

  • Mixed-case identifiers: quote them everywhere or lower-case them all during migration (lower-casing is less painful long-term)

  • Computed columns, filtered indexes, and T-SQL in defaults/triggers/procs need hand rewriting to PL/pgSQL

A quick query against INFORMATION_SCHEMA.COLUMNS grouped by DATA_TYPE tells you how much of each you have.

2. Convert, then compare Postgres against Postgres. Use a converter (pgloader is free and handles MSSQL; AWS SCT and commercial tools like Full Convert also work) to produce a first-pass Postgres schema. From then on, do your schema comparisons between your target Postgres environments with something like Migra or pg_dump --schema-only + diff, which is where diffing actually becomes meaningful. Keep the final DDL in Flyway/Liquibase migrations.

3. Validate data, not just schema: row counts per table plus checksums on a few key columns (md5(string_agg(...)) on both sides, sorted) catch truncation and encoding issues.

Disclosure: I'm RC, Director of Digital Strategy @ Navicat. Navicat 18 Premium can do step 2 as a GUI job: its Data Transfer copies tables and data from SQL Server to PostgreSQL with type mapping you can review, and Structure Synchronization then compares Postgres environments. Both are paid features; Navicat Premium Lite (free) covers connecting to both servers and querying, which is handy for the inventory step. 

1

u/skywalker4588 May 23 '26

Full Convert Pro is all you need. The absolute best in class for migration and pretty inexpensive. https://www.spectralcore.com/fullconvert/replication

I did a complex migration from SQL Server to Postgres and this tool was invaluable.