Our projects are migrating from MySQL to Postgres. We have a Ruby-on-Rails app that handles the schema (source code is on github). We have rewritten all queries to be ANSI-compliant.
We have tried two approaches. First, using Ruby migrations and second, using Navicat.
Everything is working well - our website and the dependent suite of computing codes (in R, Fortran, and C). However, before the final move, I would appreciate insight into any potential errors that we may not notice but that could come back to haunt us a year or two down the road.
So my questions are:
- What errors might occur during translation from MySQL to Postgres?
- What do I need to test to make sure that the migration was valid?
- Is it safe to assume that Ruby-on-Rails and/or Navicat are accirate?
My primary idea so far would be to import a mysql and a psql version of each table into R and test that the imported tables are identical (which is just a small step beyond dumping csv's from each and doing a diff). However, I am not sure if this is either necessary or sufficent.