** DISCLAIMER ** I’m new to the liquidbase community so bear with me while I get my bearings. ** DISCLAIMER **
I’ve recently been tasked with migrating a SQL Server database to a Postgres database for a large project I’m working on. The table structure is about 15+ years old and as you can imagine a little bit complicated, and I need a replicable solution. One of the DBAs that I work with is looking into using liquibase for normal migrations on the MSSQL side of things, so I’m interested in trying to do the same to migrate between the two.
I’ve read several accounts on these forums regarding this sort of aspect and I understand that this is possible more as an artifact of liquibase’s capabilities and less as programmed in functionality. As such, it falls a little short regarding data type conversion, and saftey.
I’ve got a few concerns, so I’m looking to get some advice / discuss how best to approach outstanding issues.
== BEGIN QUESTIONS ==
First, regarding data types, there are definitely types in use on the MSSQL side of things that are not postgresql compatible. Given the current state of liquibase, things like NVARCHAR and VARCHAR(MAX) get missed as they don’t have conversions. My intention is to adjust / improve the TypeConverters responsible for these data types, is this the preferred approach? What about special types, with conditions? For example, in MSSQL, identity columns are used to provide incrementing values for keys and what not. However, on the postgres side of things, this is handled by the SERIAL datatype. Any suggestions of how to move between the two?
Next regarding views. I need to be able to migrate views as well, and I realize this is quite a challenge given the differences between the syntax of the two view definitions. Right now, MSSQL changeLogs are output as raw sql, with poorly formatted newlines. Any suggestions on how to tackle this?
I’m more than willing to put the time into fixing these things and packaging up the results to share if someone can point me in the right direction.