1

I'm trying to convert my database from MySQL to PostgreSQL, and I'm using AWS RDS. I was trying AWS DMS to migrate data, however it didn't work well and was complicated.

While struggling with that, an idea came to mind. What if I use migrate migrations to the new PostgreSQL, and dumpdata from MySQL and loaddata to PostgreSQL? Would that work? Does anyone have experience to migrate database? Am I approaching a right direction?

Improve this question
1
  • It should work. The data is dumped in an agnostic json format. I do recall small little issues arising auto-incremented fields but your specific problems will surface when you try it out. Commented Nov 20, 2018 at 19:54

1 Answer 1

Reset to default
2

I've done this in the past and it has worked, but with some bumps in the road. Be sure your database is frozen - you might want to put users with access into read-only mode.

Good luck.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Do you remember what kind of bumps you had? I just finished manage.py loaddata and it seems to be working very well for now. Like you advised, I'm gonna freeze MySQL database for a while, and then delete it when I'm sure everything is okay.
The issues I ran into were with JSON in columns from the Wagtail CMS. Getting the keys within the JSON blocks made things tricky!
That CMS library looks great. I should try it later :) So, it sounds like it would be okay if not using Wagtail CMS?
As long as you're not doing anything too exotic with JSON in fields, I haven't run into any other problems. I'd take a wait-and-see approach - hang on to the old database for a while to make sure all is okay. In fact, do a mysqldump and gzip the backup to keep it, even after you shut it down. I've moved to PostgreSQL everywhere and have been very happy with it!
--natural-foreign was my friend when migratiing a django-cms project exactly the same way. Also, after the initial ./manage.py migrate on postgresql, I had to manually truncate contenttypes and permission tables (otherwise, their mappings from existing groups/users was not correctly, made loaddata fail).
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.