Re: MS SQL to Postgres - Mailing list pgsql-general
| From | Avinash Vallarapu |
|---|---|
| Subject | Re: MS SQL to Postgres |
| Date | |
| Msg-id | CAN0Tujf=C=hKL1ZOmKjGvsYc9f6L44iuE9ci0KnROJG8hrnA+w@mail.gmail.com Whole thread Raw |
| In response to | MS SQL to Postgres (KK CHN <kkchn.in@gmail.com>) |
| Responses |
Re: MS SQL to Postgres
|
| List | pgsql-general |
Hi Krishane,
On Wed, Aug 20, 2025 at 1:26 PM 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 16Existing 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 risksKindly 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.
pgsql-general by date: