MIME-Version: 1.0
References: <CAKgGyB8ZZLzDVg3eCym=BMf7+8LOS-bfOE1EdXWA_X4qha_=Xw@mail.gmail.com>
In-Reply-To: <CAKgGyB8ZZLzDVg3eCym=BMf7+8LOS-bfOE1EdXWA_X4qha_=Xw@mail.gmail.com>
From: Avinash Vallarapu <avinash.vallarapu@gmail.com>
Date: Wed, 20 Aug 2025 18:52:43 -0400
Message-ID: <CAN0Tujf=C=hKL1ZOmKjGvsYc9f6L44iuE9ci0KnROJG8hrnA+w@mail.gmail.com>
Subject: Re: MS SQL to Postgres
To: KK CHN <kkchn.in@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006442d6063cd3d5b2"
Archived-At: <https://www.postgresql.org/message-id/CAN0Tujf%3DC%3DhKL1ZOmKjGvsYc9f6L44iuE9ci0KnROJG8hrnA%2Bw%40mail.gmail.com>
Precedence: bulk

--0000000000006442d6063cd3d5b2
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Krishane,

On Wed, Aug 20, 2025 at 1:26=E2=80=AFPM KK CHN <kkchn.in@gmail.com> wrote:

> 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.
>
This is definitely not a problem, I have seen hundreds of migrations with
more than 2k Tables and 10TB data from MSSQL 2019.

>
> 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.  ).
>
This is not a problem either.


>
> 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 o=
f
> data porting operations?
>
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.

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
>
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.

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.
>
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.


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

--0000000000006442d6063cd3d5b2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">Hi Krishane,<div><br></d=
iv></div><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">O=
n Wed, Aug 20, 2025 at 1:26=E2=80=AFPM KK CHN &lt;<a href=3D"mailto:kkchn.i=
n@gmail.com" target=3D"_blank">kkchn.in@gmail.com</a>&gt; wrote:<br></div><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hi,=C2=A0=
<br><div><br></div><div>I am in search of the best practices to migrate fro=
m an MS SQL database server to PostgreSQL 16</div><div><br></div><div>Exist=
ing=C2=A0DB server=C2=A0MSSQL with 6 Million records and 3.5 TB=C2=A0 with =
424 Tables running from 2019 onwards.</div></div></blockquote><div>This is =
definitely not a problem, I have seen hundreds of migrations with more than=
 2k Tables and 10TB data from MSSQL 2019.</div><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex"><div dir=3D"ltr"><div><br></div><div>Each table has 5=
 to 16 columns ( basically text, numbers, lat long coordinates , time stamp=
s, and images/voice file (stored in archive folders)reference links, etc.=
=C2=A0 ).</div></div></blockquote><div>This is not a problem either.<br>=C2=
=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"=
><div><br></div><div>I am in need to port / migrate all this data from this=
 MS SQL server to Postgres16 .</div><div><br></div><div>1. What are the bes=
t methods and practices folks employ to do this kind of data porting operat=
ions?=C2=A0=C2=A0</div></div></blockquote><div>You could use Open Source mi=
gration tools like: pgloader for schema migration (excluding any code objec=
ts like procedures or functions).</div><div>Or you could also use tools lik=
e HexaRocket: <a href=3D"http://www.hexarocket.com">www.hexarocket.com</a><=
/div><div>One more extension you could try is: tds_fdw, using which you cou=
ld directly query your MSSQL database and load data to PostgreSQL, but be p=
repared to see some surprises.</div><div>=C2=A0</div><blockquote class=3D"g=
mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204=
,204,204);padding-left:1ex"><div dir=3D"ltr"><div>2. what are the tools and=
 techniques to explored / employed for this</div></div></blockquote><div>=
=C2=A0Already answered in the previous question.=C2=A0</div><div><br></div>=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>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=C2=A0</div></div><=
/blockquote><div>While there cannot always be a direct answer, I can talk a=
bout the tool:<b>HexaRocket=C2=A0</b>for some of such migrations.</div><div=
>It took around 12 Hours, but remember, this can be more or even lesser dep=
ending on your Infrastructure.</div><div><br></div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex"><div dir=3D"ltr"><div>4. Any hurdles or challenge=
s or risks=C2=A0</div><div><br></div><div>Kindly enlighten me with the best=
 practices and=C2=A0 reference materials=C2=A0/ links or tutorials to perfo=
rm=C2=A0these operations successfully.=C2=A0</div></div></blockquote><div>T=
here are several differences you need to be aware of between MSSQL and Post=
greSQL.</div><div><ul><li>Start with the data type mapping to begin with.</=
li><li>If PostGIS is enabled, use types like geometry, geography. Can use t=
ext for fallback support.</li><li>PostgreSQL supports composite types, arra=
ys at the column level natively, while SQL Server cannot.</li><li>SQL Serve=
r often auto-generates constraint names, while PostgreSQL typically require=
s explicit<br>names.</li><li>There is a good amount of difference between C=
lustered Indexes in SQL Server vs PostgreSQL</li><li>Spatial Indexes (Geome=
try/Geography) requires PostGIS extension in PostgreSQL.</li><li>Using Post=
greSQL&#39;s native IDENTITY feature instead of legacy SERIAL/BIGSERIAL, as=
 IDENTITY matches SQL<br>Server&#39;s behavior with clear syntax.</li><li>I=
n SQL Server, RANGE partitioning is the only natively supported partitionin=
g method. But, during migration, partition boundaries must be carefully adj=
usted to match PostgreSQL&#39;s behavior.</li><li>User Defined Table Types =
of SQL Server are migrated to PostgreSQL as composite types which can<br>en=
capsulate multiple columns under single type.</li><li>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.</li></ul></div></div><div><br clear=
=3D"all"></div><div><br></div><span class=3D"gmail_signature_prefix">-- </s=
pan><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div>Re=
gards,<br>Avinash Vallarapu</div><div>+1-902-221-5976</div><div><a href=3D"=
http://www.hexarocket.com">www.hexarocket.com</a></div></div></div></div>
</div>

--0000000000006442d6063cd3d5b2--