OT: Resources for converting MySQL data to PostgreSQL for Django

This might be a bit off-topic, but why are you considering
migration?
Any problems or some other reasons?

Sebastjan

On 5/27/07, Jason McVetta <jason.mcvettagmail.com> wrote:
> Check out Kettle (aka Pentaho Data Integration) from
> http://kettle.pentaho.org.
  It's a heavy-weight tool, but it has a fairly
> intuitive GUI and decent documentation.  It's Open, of
course, and very
> multi-platform (I personally use it on Windoze,
Solaris, and occasionally
> Linux).
>
> The function you want is called "Copy Tables
Wizard".  Just used that last
> week to migrate one of my Django apps from MySQL to
Postgres.  It's pretty
> straightforward and hassle-free.
>
>
>
>
>
>
> On 5/26/07, Hancock, David (DHANCOCK) <DHANCOCKarinc.com> wrote:
> >
> > We are strongly considering migrating from MySQL
to PostgreSQL, but there
> seem to be enough differences between the two that I'm
pulling my hair out.
> I know this is somewhat off-topic, but has anybody got
some resources for
> the conversion process?
> >
> > I'd love it to be as simple as a dump-and-restore,
but so far, no silver
> bullet has emerged. Things I've tried:
> >
> >     mysqldump --compatible=postgresql # not so
compatible
> >     dump-db.py from djangosnippets.org # many data
differences
> >     hand-editing dumped SQL # too much work,
nonexistent foreign key
> values
> >     manage.py dumpdata # chokes on Decimal(0.00)
datatype, not sure how to
> load output anyway
> >
> > Thanks in advance, and
> > Cheers!
> > --
> > David Hancock | dhancockarinc.com
> >
> >
> >
>
>
>  >
>

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

On 5/27/07, Hancock, David (DHANCOCK) <DHANCOCKarinc.com> wrote:
>
>  I'd love it to be as simple as a dump-and-restore, but
so far, no silver
> bullet has emerged. Things I've tried:
...
>      manage.py dumpdata # chokes on Decimal(0.00)
datatype, not sure how to
> load output anyway

If this approach is failing on Decimal(0.00), I would
consider it a
major bug. All going well, I should have a chance to look at
this
later today.

As a point of reference - we recently modified the
Decimal/Float
representation in Django; is your code pre or post this
change
([5302])?

Yours,
Russ Magee %-)

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

On Sun, 2007-05-27 at 08:14 +0800, Russell Keith-Magee
wrote:
> On 5/27/07, Hancock, David (DHANCOCK) <DHANCOCKarinc.com> wrote:
> >
> >  I'd love it to be as simple as a
dump-and-restore, but so far, no silver
> > bullet has emerged. Things I've tried:
> ...
> >      manage.py dumpdata # chokes on Decimal(0.00)
datatype, not sure how to
> > load output anyway
> 
> If this approach is failing on Decimal(0.00), I would
consider it a
> major bug. All going well, I should have a chance to
look at this
> later today.

This surprises me, too. We're testing the serialisation (and
reading
back) of Decimal types in the test suite. May be some
unnoticed (by me)
difference between pure using django.core.serializers and
manage.py's
dumpdata, but the path looks pretty straightforward.

Regards,
Malcolm


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

I haven't got a lot to go on, output-wise; here's what I
ran:

[dhancockdhancock-mac intl]$ python manage.py dumpdata fuel
>
fuel.tmp
Unable to serialize database: Decimal("0.00") is
not JSON serializable

And trying a different format, a different error:

[dhancockdhancock-mac intl]$ python manage.py dumpdata
--format xml
fuel > fuel.xml
Unable to serialize database: 'ascii' codec can't decode
byte 0xe2 in
position 13: ordinal not in range(128)

The fuel.tmp file contains "Nonen".

The second reply asked "Why switch?" There's no
smoking gun or nasty
occurrence, but we're switching some applications from
Oracle to open-
source databases and are trying several. Because we need to
interact
both via Django applications and via direct SQL, we have
been finding
it much easier to shoot ourselves in the feet when we
neglect to use
the InnoDB storage engine and thus have no
foreign-key-constraint
enforcement. It also seems like Django authors (in The Book
at least)
favor PostgreSQL.

On May 26, 8:40 pm, Malcolm Tredinnick <malc...pointy-stick.com>
wrote:
> On Sun, 2007-05-27 at 08:14 +0800, Russell Keith-Magee
wrote:
> > On 5/27/07, Hancock, David (DHANCOCK)
<DHANC...arinc.com> wrote:
>
> > >  I'd love it to be as simple as a
dump-and-restore, but so far, no silver
> > > bullet has emerged. Things I've tried:
> > ...
> > >      manage.py dumpdata # chokes on
Decimal(0.00) datatype, not sure how to
> > > load output anyway
>
> > If this approach is failing on Decimal(0.00), I
would consider it a
> > major bug. All going well, I should have a chance
to look at this
> > later today.
>
> This surprises me, too. We're testing the serialisation
(and reading
> back) of Decimal types in the test suite. May be some
unnoticed (by me)
> difference between pure using django.core.serializers
and manage.py's
> dumpdata, but the path looks pretty straightforward.
>
> Regards,
> Malcolm


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

On Sat, 2007-05-26 at 18:02 -0700, David wrote:
> I haven't got a lot to go on, output-wise; here's what
I ran:
> 
> [dhancockdhancock-mac intl]$ python manage.py
dumpdata fuel >
> fuel.tmp
> Unable to serialize database: Decimal("0.00")
is not JSON serializable

To repeat Russell's question: which version of the code are
you using? 

It very much sounds like you're using MySQL with a checkout
prior to
last week. In which case, you are bumping into ticket #3324.
There's a
patch there you might be able to try as a workaround if you
can't
upgrade your Django source.

> 
> And trying a different format, a different error:
> 
> [dhancockdhancock-mac intl]$ python manage.py
dumpdata --format xml
> fuel > fuel.xml
> Unable to serialize database: 'ascii' codec can't
decode byte 0xe2 in
> position 13: ordinal not in range(128)

Since non-ASCII data essentially doesn't work on trunk,
that's not too
surprising. This is ticket #4227. As a workaround, you might
be able to
use the patch attached to that ticket, or use the unicode
branch, where
this has been fixed.

One lesson from this: searching the ticket database for
similar problems
will often yield results. I have some advantage here since I
work with
our Trac enough that I have most of the major open and
recently closed
tickets in my head, but searching in the serialisation
component (in
this case) and including closed tickets as well as currently
open ones
would yield resutls.

Regards,
Malcolm


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

My apologies. I am using 0.96, not an SVN checkout of recent
vintage.
I'll check out the two tickets mentioned (starting with
3324), and
search tickets first from now on.

On May 26, 9:46 pm, Malcolm Tredinnick <malc...pointy-stick.com>
wrote:
> On Sat, 2007-05-26 at 18:02 -0700, David wrote:
> > I haven't got a lot to go on, output-wise; here's
what I ran:
>
> > [dhancockdhancock-mac intl]$ python manage.py
dumpdata fuel >
> > fuel.tmp
> > Unable to serialize database:
Decimal("0.00") is not JSON serializable
>
> To repeat Russell's question: which version of the code
are you using?
>
> It very much sounds like you're using MySQL with a
checkout prior to
> last week. In which case, you are bumping into ticket
#3324. There's a
> patch there you might be able to try as a workaround if
you can't
> upgrade your Django source.
>
>
>
> > And trying a different format, a different error:
>
> > [dhancockdhancock-mac intl]$ python manage.py
dumpdata --format xml
> > fuel > fuel.xml
> > Unable to serialize database: 'ascii' codec can't
decode byte 0xe2 in
> > position 13: ordinal not in range(128)
>
> Since non-ASCII data essentially doesn't work on trunk,
that's not too
> surprising. This is ticket #4227. As a workaround, you
might be able to
> use the patch attached to that ticket, or use the
unicode branch, where
> this has been fixed.
>
> One lesson from this: searching the ticket database for
similar problems
> will often yield results. I have some advantage here
since I work with
> our Trac enough that I have most of the major open and
recently closed
> tickets in my head, but searching in the serialisation
component (in
> this case) and including closed tickets as well as
currently open ones
> would yield resutls.
>
> Regards,
> Malcolm


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

On Sat, 2007-05-26 at 19:36 -0700, David wrote:
> My apologies. I am using 0.96, not an SVN checkout of
recent vintage.
> I'll check out the two tickets mentioned (starting with
3324), and
> search tickets first from now on.

You don't have to apologise for asking questions. I was
hoping to  point
out a way you might be able to get faster answers.

Version number of checkout changeset number is important
information,
though, with the speed of Django development these days.

Regards,
Malcolm



--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---

Hancock, David (DHANCOCK) wrote:
> We are strongly considering migrating from MySQL to
PostgreSQL, but 
> there seem to be enough differences between the two
that I'm pulling my 
> hair out. I know this is somewhat off-topic, but has
anybody got some 
> resources for the conversion process?
> 
> I'd love it to be as simple as a dump-and-restore, but
so far, no silver 
> bullet has emerged. Things I've tried:
> 
>     mysqldump --compatible=postgresql # not so
compatible
>     dump-db.py from djangosnippets.org # many data
differences
>     hand-editing dumped SQL # too much work,
nonexistent foreign key values
>     manage.py dumpdata # chokes on Decimal(0.00)
datatype, not sure how 
> to load output anyway

Well, that would be because MySQL is effectively broke in
many weird 
ways. Your best bet is to recreate your model in postgresql,
initialize 
that model and move the data over relation by relation.

Joshua D. Drake


> 
> Thanks in advance, and
> Cheers!
> -- 
> David Hancock | dhancockarinc.com
> 
> > 


-- 

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency:
+1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since
1997
              http://www.commandpromp
t.com/

Donate to the PostgreSQL Project: http://www.pos
tgresql.org/about/donate
PostgreSQL Replication: http://www.com
mandprompt.com/products/


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the
Google Groups "Django users" group.
To post to this group, send email to django-usersgooglegroups.com
To unsubscribe from this group, send email to
django-users-unsubscribegooglegroups.com
For more options, visit this group at htt
p://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---