Hi Krishane, On Wed, Aug 20, 2025 at 1:26 PM KK CHN <kkchn...@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 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
