I was faced with the project of migrating an mssql database to postgres (why? mainly because of licensing fees as we are about to go live with the solution – without any revenues any time soon 🙂 )
This is more a #notetoself if im faced with this again rather than any kind of guideline attempt – please feel free to contact me for details if I can in any way help – I would be happy to share my experience with this.
So, I thought I would document a checklist for those that might stumble across this post while preparing the same action. I was an absolute beginner with postgres when starting this process so some of the comments might feel a bit “verbal”.
The porting took me about 16 hours for a database with around 100 tables, 50 procedures, 50 functions. My process is not applicable for all as I moved the data between the database using json as staging storage for all data – creating a simple import mechanism in c# thought EF6.
- Postgres only handles lowercase objects (schemas, table names, columns, procedure names, … ) without quotes. Prepare to rename everything! … unless you want to add a million double quotes to your database procedures and views.
- Make sure you read this: https://www.npgsql.org/efcore/modeling/table-column-naming.html before thinking about changing your db entity names in c# (from GolfClub to golf_club) This is mapped automatically by EF6.
- Get ready with your favorite text editor with a lot of search-replace regexp.
- When putting together dynamic queries and using the pipe || to concatenate strings (instead of + in mssql) make sure you declare empty strings as empty quotes – as when a null is “and-ed” with a string the result is null. I sent quite a few nulls to the execute statement when I was expecting the dynamic string to be full of meaning.