Tips On Migrating DB from other website to django with Postgresql
27 views
Skip to first unread message
tango ward
Apr 11, 2018, 9:41:42 AM4/11/18
to django...@googlegroups.com
Hi there,
This will be my first time to migrate a database in my first programming job. I checked the dump file today and load it in MySQL benchmark to view the ERD. The database that I need to migrate has 48 tables and has existing data already.
I would like to know what are the things that I need to do first. I'm gonna test the migration first in my local machine before bringing it to live production. I am currently reading https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL.
Any suggestions will be highly appreciated.
Thanks,
J
Mike Dewhirst
Apr 11, 2018, 6:43:45 PM4/11/18
to django...@googlegroups.com, tango ward
2. Devise some repeatable automated success tests to prove the migration
works
3. Set up a new VM as an exact duplicate production machine running
MySQL. Use your hosts file to put its IP address somewhere else than the
DNS points to. Beware that ACLs often use IP addresses rather than host
names.
4. Do the migrations and run the tests on the duplicate machine. Debug
as necessary.
5. Announce to users that the system is going down for planned maintenance
6. Remove your hosts file adjustment so your system is back to looking
at the real production machine
7. Take production off-line and do a pre-migration dump or backup from
which you can restore if things go wrong
8. Do the migration, run the tests then restore service
I have done such things in the distant past but not recently. I may have
forgotten something but in my opinion items 5 and 7 above are super
critical.
Good luck
Mike
>
>
> Thanks,
> J
> --
> You received this message because you are subscribed to the Google
> Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to django-users...@googlegroups.com
> <mailto:django-users...@googlegroups.com>.
> To post to this group, send email to django...@googlegroups.com
> <mailto:django...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.
Mike Dewhirst
Apr 11, 2018, 8:47:54 PM4/11/18
to Django users
On 12/04/2018 9:47 AM, tango ward wrote:
> Hi Mike,
>
>
> Thanks for the advice.
>
> May I ask as well, on Django side, since we have already an existing
> system, how should I adjust the fields that are existing from the dump
> file to the Django? Should I just add fields in models.py?
Treat that as a separate task. You can do it before the migration or
after. I would preferdo it afterwards because I am more comfortable with
Postgres. And I suspect Postgres will be a better fit with Django.
However, I'm not sure what you mean exactly by adjusting fields. Your
last question above indicates you want to change the schema in an
existing Django project. You need to consider when the fields need to be
changed in the context of real-world pressures from your production
system users. It might need to be sooner rather than later and that may
force adjustments prior to the move to Postgres.
If there are problems with the existing MySQL system it would be best to
fix them before switching.
The bottom line is that preparations and testing for the move has to
happen in parallel with ongoing support and maintenance and possibly
ongoing development of the production system. When you are ready to
switch and happy that it will go like clockwork the actual switch can be
scheduled as a definable task in the ongoing workflow. All developers
will have to switch at the same time - or prior if they can have access
to your completed scripts for their personal development databases.
hth
Mike
>
> On Thu, Apr 12, 2018 at 6:42 AM, Mike Dewhirst <mi...@dewhirst.com.au
> <mailto:mi...@dewhirst.com.au>> wrote:
>
> On 11/04/2018 11:40 PM, tango ward wrote:
>
> Hi there,
>
> This will be my first time to migrate a database in my first
> programming job. I checked the dump file today and load it in
> MySQL benchmark to view the ERD. The database that I need to
> migrate has 48 tables and has existing data already.
>
>
> I would like to know what are the things that I need to do
> first. I'm gonna test the migration first in my local machine
> before bringing it to live production. I am currently reading
> https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
> <https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL>.
> <mailto:django-users%2Bunsu...@googlegroups.com>
> <mailto:django-users...@googlegroups.com
> <mailto:django-users%2Bunsu...@googlegroups.com>>.
>
>
>
> Hi Mike,
>
>
> Thanks for the advice.
>
> May I ask as well, on Django side, since we have already an existing
> system, how should I adjust the fields that are existing from the dump
> file to the Django? Should I just add fields in models.py?
Treat that as a separate task. You can do it before the migration or
after. I would preferdo it afterwards because I am more comfortable with
Postgres. And I suspect Postgres will be a better fit with Django.
However, I'm not sure what you mean exactly by adjusting fields. Your
last question above indicates you want to change the schema in an
existing Django project. You need to consider when the fields need to be
changed in the context of real-world pressures from your production
system users. It might need to be sooner rather than later and that may
force adjustments prior to the move to Postgres.
If there are problems with the existing MySQL system it would be best to
fix them before switching.
The bottom line is that preparations and testing for the move has to
happen in parallel with ongoing support and maintenance and possibly
ongoing development of the production system. When you are ready to
switch and happy that it will go like clockwork the actual switch can be
scheduled as a definable task in the ongoing workflow. All developers
will have to switch at the same time - or prior if they can have access
to your completed scripts for their personal development databases.
hth
Mike
>
> On Thu, Apr 12, 2018 at 6:42 AM, Mike Dewhirst <mi...@dewhirst.com.au
> <mailto:mi...@dewhirst.com.au>> wrote:
>
> On 11/04/2018 11:40 PM, tango ward wrote:
>
> Hi there,
>
> This will be my first time to migrate a database in my first
> programming job. I checked the dump file today and load it in
> MySQL benchmark to view the ERD. The database that I need to
> migrate has 48 tables and has existing data already.
>
>
> I would like to know what are the things that I need to do
> first. I'm gonna test the migration first in my local machine
> before bringing it to live production. I am currently reading
> https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL
> <mailto:django-users...@googlegroups.com
> <mailto:django-users%2Bunsu...@googlegroups.com>>.
> To post to this group, send email to
> django...@googlegroups.com
> <mailto:django...@googlegroups.com>
> <mailto:django...@googlegroups.com
> django...@googlegroups.com
> <mailto:django...@googlegroups.com>
> <mailto:django...@googlegroups.com>>.
> Visit this group at
> https://groups.google.com/group/django-users
> <https://groups.google.com/group/django-users>.
> Visit this group at
> https://groups.google.com/group/django-users
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com>
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer
> https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com>
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer>>.
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
> For more options, visit https://groups.google.com/d/optout
>
>
>
tango ward
Apr 11, 2018, 9:00:08 PM4/11/18
to django...@googlegroups.com
At the moment, I am comparing the fields from the dump file and the fields in our models.py. There are fields that exist on the dump file that are not yet in our existing model/models that why I want to know if should I modify the schema of our database to match the dump file of the client. I'm lost on this, sorry.
<mailto:django-users%2Bunsubscri...@googlegroups.com>it, send an email to django-users+unsubscribe@googlegroups.com
<mailto:django-users+unsubscrib...@googlegroups.com
<mailto:django-users%2Bunsubscri...@googlegroups.com>>.
To post to this group, send email to
django...@googlegroups.com
<mailto:django-users@googlegroups.com>
<mailto:django-users@googlegroups.com
<mailto:django-users@googlegroups.com>>.
Visit this group at
https://groups.google.com/group/django-users
<https://groups.google.com/group/django-users>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com
<https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com>
<https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer
<https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer>>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/07a26b64-9923-f9ee-b6c2-47676d245038%40dewhirst.com.au.
tango ward
Apr 11, 2018, 10:44:56 PM4/11/18
to django...@googlegroups.com
Sorry for the follow up question, when you say run migration locally, this is running python manage.py makemigrations and python manage.py migrate, right? Please let me know if I got these steps correctly:
- make a postgres test db
- changed database settings in settings.py and point it to test db in postgres
- If i run makemigrations and migrate, the only db and tables that will be saved in test db are the ones in our models.py
Should I modify the models.py to match the fields in the dump file? or should I just check the existing data from the dump file and see which model and fields it fits in our existing system?
Mike Dewhirst
Apr 12, 2018, 5:02:50 AM4/12/18
to django...@googlegroups.com, tango ward
On 12/04/2018 12:44 PM, tango ward wrote:
> Sorry for the follow up question, when you say run migration locally,
> this is running python manage.py makemigrations and python manage.py
> migrate, right? Please let me know if I got these steps correctly:
>
> - make a postgres test db
> - changed database settings in settings.py and point it to test db in
> postgres
> - If i run makemigrations and migrate, the only db and tables that
> will be saved in test db are the ones in our models.py
>
> Should I modify the models.py to match the fields in the dump file? or
> should I just check the existing data from the dump file and see which
> model and fields it fits in our existing system?
That's a bit hit-and-miss.
> Sorry for the follow up question, when you say run migration locally,
> this is running python manage.py makemigrations and python manage.py
> migrate, right? Please let me know if I got these steps correctly:
>
> - make a postgres test db
> - changed database settings in settings.py and point it to test db in
> postgres
> - If i run makemigrations and migrate, the only db and tables that
> will be saved in test db are the ones in our models.py
>
> Should I modify the models.py to match the fields in the dump file? or
> should I just check the existing data from the dump file and see which
> model and fields it fits in our existing system?
https://docs.djangoproject.com/en/1.11/ref/django-admin/#inspectdb
Run manage.py inspectdb over your MySQL db and that will give you a set
of models. You should be able to fire up the Django Admin to examine
what's in there. If necessary.
Edit those generated models as required to match your existing
Django/Postgres system and run manage.py makemigrations and migrate.
That should give you two databases with the same schema.
At that point do a MySQL SQL dump file and drop/create/load Postgres
using that dump file.
Since the name of the database is in the dump file you might need to
edit it if you want a different db name in Postgres. Don't use test
because Django uses that for unit testing.
There may be wrinkles. However, you should be able to iron them out by
adjusting the generated models so they look more and more like the
existing models. It may need a few migrations and you may need to start
again and again with what you learn refining your procedures. The data
transfer process needs to run reliably enough for the eventual
switchover so the customers don't get hurt.
hth
Mike
> <mailto:django-users%2Bunsu...@googlegroups.com>
>
> <mailto:django-users%2Bunsu...@googlegroups.com
> <mailto:django-users%252Buns...@googlegroups.com>>
> <mailto:django-users...@googlegroups.com
> <mailto:django-users%2Bunsu...@googlegroups.com>
>
> <mailto:django-users%2Bunsu...@googlegroups.com
> <mailto:django-users%252Buns...@googlegroups.com>>>.
> <mailto:django...@googlegroups.com>
> <mailto:django...@googlegroups.com
> <mailto:django...@googlegroups.com>>
> <mailto:django...@googlegroups.com
> <mailto:django...@googlegroups.com>
> <mailto:django...@googlegroups.com
> <mailto:django...@googlegroups.com>>>.
> <mailto:django...@googlegroups.com>>
> <mailto:django...@googlegroups.com
> <mailto:django...@googlegroups.com>
> <mailto:django...@googlegroups.com
> Visit this group at
> https://groups.google.com/group/django-users
> <https://groups.google.com/group/django-users>
> <https://groups.google.com/group/django-users
> <https://groups.google.com/group/django-users>>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com>
>
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com>>
>
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer>
>
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer>>>.
> For more options, visit
> https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>
> <https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>>.
>
>
>
>
> --
> You received this message because you are subscribed to the
> Google Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from
> it, send an email to django-users...@googlegroups.com
> https://groups.google.com/group/django-users
> <https://groups.google.com/group/django-users>
> <https://groups.google.com/group/django-users
> <https://groups.google.com/group/django-users>>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com>
>
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com>>
>
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer>
>
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKcf22aXXKd6xUn%3DT6HOxHLBN7u1xcn%3DOXO3DdL%3D54XSg%40mail.gmail.com?utm_medium=email&utm_source=footer>>>.
> For more options, visit
> https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>
> <https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>>.
>
>
>
>
> --
> You received this message because you are subscribed to the
> Google Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from
> <mailto:django-users%2Bunsu...@googlegroups.com>.
> <mailto:django...@googlegroups.com>.
> Visit this group at
> https://groups.google.com/group/django-users
> <https://groups.google.com/group/django-users>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/07a26b64-9923-f9ee-b6c2-47676d245038%40dewhirst.com.au
> https://groups.google.com/group/django-users
> <https://groups.google.com/group/django-users>.
> To view this discussion on the web visit
> <https://groups.google.com/d/msgid/django-users/07a26b64-9923-f9ee-b6c2-47676d245038%40dewhirst.com.au>.
>
>
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to django-users...@googlegroups.com
>
> For more options, visit https://groups.google.com/d/optout
> <https://groups.google.com/d/optout>.
>
>
>
> --
> You received this message because you are subscribed to the Google
> Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> <mailto:django-users...@googlegroups.com>.
> To post to this group, send email to django...@googlegroups.com
> <mailto:django...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-users/CAA6wQLKdJit5eLHCiFSwwoMA0Feg7b69vyRDP_d7u%3D4W0YGkdQ%40mail.gmail.com
> To view this discussion on the web visit
> <https://groups.google.com/d/msgid/django-users/CAA6wQLKdJit5eLHCiFSwwoMA0Feg7b69vyRDP_d7u%3D4W0YGkdQ%40mail.gmail.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.
Reply all
Reply to author
Forward
0 new messages