I've been using PostgresSQL for my production site for quite awhile.  My 
future improved site, however, will feature MySql for its database engine. 
 (Not a diss on Postgres, it's just a lot easier to find hosting companies 
who will rent me a supported-and-maintained MySql instance.)  I have 
thousands of user logins and lots 'o history I want, yea even NEED, to 
preserve.  I've been reading up on strategies for converting from one 
database to another.

For example, I can dump the Postgres database in its entirety into CSV 
using the DAL, then read the data back into MySql.  That has some 
attractiveness but I couldn't help noticing how s--l-o-w this process is. 
 I let the dump run for about an hour and it wasn't done yet.  Then there's 
the little problem of the developer.  He just can't leave things alone. 
 The MySql database design is "much better" (read: different) than the 
Postgres design.  (It makes no difference that I am actually the developer 
of both.)  So I can't just export CSV and import it.  Not without editing 
the CSV on the way.

What if, instead, I create the MySql tables with an extra column to hold 
the "old" id from the Postgres database?  I could then develop a 
"transition" class that would open connections to both databases, read 
records from the Postgres one, and insert them (with whatever mods are 
required) into the MySql database.  When I process records from Postgres 
which have dependencies on the "id" field from another table, I use the 
"old_id" field to map it to the new id value.

This process could take as long as it wants, and run concurrently with the 
old system.  I could shut it down and restart it as needed if I kept a 
"fence" of the highest id processed from the old database.  I would need to 
do the tables in order of no dependencies to fullest dependencies, to make 
sure the required dependent records are already in place when the table is 

After all is said and done, I could simply delete the "old_id" columns from 
the affected MySql tables.  If I even care at that point.

Am I missing something?  Or does this seem viable for a system with a large 
database to migrate?

-- Joe

- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
You received this message because you are subscribed to the Google Groups 
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply via email to