Re: MS SQL to Postgres

Hi Krishane,

On Wed, Aug 20, 2025 at 1:26 PM KK CHN <kkchn(dot)in(at)gmail(dot)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 of
> 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.

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