public inbox for [email protected]
help / color / mirror / Atom feedMS SQL to Postgres
2+ messages / 2 participants
[nested] [flat]
* MS SQL to Postgres
@ 2025-08-20 17:25 KK CHN <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: KK CHN @ 2025-08-20 17:25 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: MS SQL to Postgres
@ 2025-08-20 22:52 Avinash Vallarapu <[email protected]>
parent: KK CHN <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Avinash Vallarapu @ 2025-08-20 22:52 UTC (permalink / raw)
To: KK CHN <[email protected]>; +Cc: [email protected]
Hi Krishane,
On Wed, Aug 20, 2025 at 1:26 PM KK CHN <[email protected]> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-08-20 22:52 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-20 17:25 MS SQL to Postgres KK CHN <[email protected]>
2025-08-20 22:52 ` Avinash Vallarapu <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox