Migrate legacy application to new db/schema

Question

I apologize if I made any mistake/missing info, fist post here!

I am in the process of rewriting a website from scratch to a newer tech stack with a completely redesigned architecture. The website in question is a real estate website to rent houses for short period of times. It was designed/created in 1998-1999 and has been working ever since. A few cosmetic redesigns have been made to the website, but no changes have been made to the database or the architecture except for hosting-mandated updates to the infrastrcture (they run on one of those shared spaces). The current stack is as follows:

• DB: MySQL (10.1.41-MariaDB-cll-lve)
• Server: PHP 7.0
• Client (Web): Simple Bootstrap website, nothing fancy

The system was originally designed by a student, which had no idea/experience with these kind of systems. The structure of the DB is quite flat, and there are just a few tables (< 5). There are almost no joins in the code, and as an example their booking periods are all put together in the same field, with the dates split by delimiters and concatenated in the format

DD:MM:YYYY-DD:MM:YYYY/DD:MM:YYYY-DD:MM:YYYY/... 

So whenever they need to check for availability, they parse this string. Also, data types are scarcely used and most of the fields are strings.

I have rewritten the entire system with a new architecture and schema using relations whenever possible as well as adding quite a few tables. This is the stack:

• DB: PostgreSQL
• Server: Spring Boot (latest) with Spring Data
• Client (Web): Vue.js

I now need to migrate the old data to the new system, and I am not sure how to proceed. I have thought of two different options:

1. Write a script that takes all the data from the DB, creates the relations and adapts to the new schema, and stores them in the new DB (EDIT: The script could also parse the dates, etc). Of course this needs to be properly tested but I am not in a time crunch. The script could be written in either Python (to allow me to manipulate the fields without having to deal with types) or using Java (being as I use Spring) and using the new models directly.
2. Extract the data from MySQL and dump it into PostgreSQL. Then, write some kind of an adapter that allows me to read the data and manipulate it little by little whenever needed. I am afraid of this option as Spring Data is quite strict at times and I would need to duplicate a lot of the models I have created.

Keep in mind the following things:

1. There are about 5000 entries between all the tables, so not a huge migration. Still, they have a lot of information that needs to be accurate and cannot be lost.
2. Spring Data complains quite easily about foreign key restrictions if you touch the schema in any way (i.e. manually) and therefore I am afraid of doing all the work and then having to deactivate safety features.
3. The migration will be done only once. Once verified, we will keep a copy of the old database just to be sure but everything else will be moved to the new system.

EDIT: As pointed out in the comments, I forgot to ask the actual question :). Are any of the options I listed a good way to approach this problem? Are there any tools out there that may help me in this migration? I think that option 1 is the best for my case, am I missing something?

Any help is greatly appreciated!

Answer 1

As mentioned in the comments, this rather misses a question regarding a database technology. However here is my suggestion:

Since there are likely not any MySQL or Postgres specific features involved, just dump to CSV roughly in the data format that you will likely want in your target system and insert it there into any kind of table. If you afterwards need to massage data and add postgres features on top of it, create a new target table with the functionality you require (e.g. indexes, constraints, relations, views) and insert into it from the previous table.

I would not suggest anything more complicated, since you seem to migrate only small data.

PG has all the manipulation functions you need. Once you are done make it work in your application software.