Subject: MS SQL to Postgres

From: KK CHN <[email-masked]>
Date: Wed, 20 Aug 2025 22:55:56 +0530

Hi,

I am in search of the best practices to migrate from an MS SQL database
server to PostgreSQL 16

Existing DB server MSSQL with 6 Million records and 3.5 TB  with 424 Tables
running from 2019 onwards.

Each table has 5 to 16 columns ( basically text, numbers, lat long
coordinates , time stamps, and images/voice file (stored in archive
folders)reference links, etc.  ).

I am in need to port / migrate all this data from this MS SQL server to
Postgres16 .

1. What are the best methods and practices folks employ to do this kind of
data porting operations?
2. what are the tools and techniques to explored / employed for this
3. How much time is consumed by employing the right tools, the entire
porting of 6 million records of 3.5 TB size to Postgres 16 takes
4. Any hurdles or challenges or risks

Kindly enlighten me with the best practices and  reference materials /
links or tutorials to perform these operations successfully.

Thank you,
Krishane

---

From: Avinash Vallarapu <[email-masked]>
Date: Wed, 20 Aug 2025 18:52:43 -0400

Hi Krishane,

On Wed, Aug 20, 2025 at 1:26 PM KK CHN <[email-masked]> wrote:

> Existing DB server MSSQL with 6 Million records and 3.5 TB  with 424
> Tables running from 2019 onwards.
>
This is definitely not a problem, I have seen hundreds of migrations with
more than 2k Tables and 10TB data from MSSQL 2019.

> folders)reference links, etc.  ).
This is not a problem either.

> 1. What are the best methods and practices folks employ to do this kind of
You could use Open Source migration tools like: pgloader for schema
migration (excluding any code objects like procedures or functions).
Or you could also use tools like HexaRocket: www.hexarocket.com
One more extension you could try is: tds_fdw, using which you could
directly query your MSSQL database and load data to PostgreSQL, but be
prepared to see some surprises.

> 2. what are the tools and techniques to explored / employed for this
 Already answered in the previous question.

> porting of 6 million records of 3.5 TB size to Postgres 16 takes
While there cannot always be a direct answer, I can talk about the tool:
*HexaRocket *for some of such migrations.
It took around 12 Hours, but remember, this can be more or even lesser
depending on your Infrastructure.

> links or tutorials to perform these operations successfully.
There are several differences you need to be aware of between MSSQL and
PostgreSQL.

   - Start with the data type mapping to begin with.
   - If PostGIS is enabled, use types like geometry, geography. Can use
   text for fallback support.
   - PostgreSQL supports composite types, arrays at the column level
   natively, while SQL Server cannot.
   - SQL Server often auto-generates constraint names, while PostgreSQL
   typically requires explicit
   names.
   - There is a good amount of difference between Clustered Indexes in SQL
   Server vs PostgreSQL
   - Spatial Indexes (Geometry/Geography) requires PostGIS extension in
   PostgreSQL.
   - Using PostgreSQL's native IDENTITY feature instead of legacy
   SERIAL/BIGSERIAL, as IDENTITY matches SQL
   Server's behavior with clear syntax.
   - In SQL Server, RANGE partitioning is the only natively supported
   partitioning method. But, during migration, partition boundaries must be
   carefully adjusted to match PostgreSQL's behavior.
   - User Defined Table Types of SQL Server are migrated to PostgreSQL as
   composite types which can
   encapsulate multiple columns under single type.
   - There is a much more bigger list for every category, so I will share
   with you a Slide deck from one of my talks on MSSQL to PostgreSQL.

-- 
Regards,
Avinash Vallarapu
+1-902-221-5976
www.hexarocket.com