MSSQL to PostgreSQL database migration can be done through Flyway? Please advise the steps if possible.
Flyway is a tool to apply incremental changes to an existing database. These changes can be data changes and/or data model changes. It won't help you with the migration from SQL Server to PostgreSQL or any other combination.
My recommended approach would be:
- Migrate the current MS SQL database to PostgreSQL
1.1. dump current DB - best would be as SQL statements - the recommended tools can help you with analyzing the current database schema, use can rewrite it for the new schema, dump data, restore data, test queries, ...
1.2. make changes to the DDL statements (CREATE TABLE, CREATE SEQUENCE,...) if necessary - essentially make a port to PostgreSQL; adapt indexes, stored procedures, views, triggers,...
1.3. create a database instance on PostgreSQL and import adapted structure and data
1.4. automate the dump and restore part as complete as possible to avoid errors on the final migration
Update/adapt and test application with new PostgreSQL database. - essentially fork your application and start a new flyway/application history with the new PostgreSQL schema.
Repeat steps 1 and 2 until everything works as expected
Go Live: deploy everything in production (take application down, migrate database, change db connection info, apply new application with PostgreSQL)
from there on use flyway to do incremental updates in your database matching the changes in your entity/repository classes. initial Flyway migration (for example V10basescheme.sql) should be the schema made in step 1.2.
