Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

closed as too broad by Jack Douglas Dec 17 '13 at 20:27

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

Bounty or no, your question is just too broad to answer. Somebody experienced with Postgres (and MySQL) should run tests and fix / optimize the queries. Unit tests would come in handy ... – Erwin Brandstetter Dec 17 '13 at 20:10
I agree with Erwin, this is too broad I'm afraid. – Jack Douglas Dec 17 '13 at 20:27
Why the fuss, the answers are easy: 1) Many and various 2) Time and patience 3) No . I don't mean bad, but generally if the first run is ok (for a couple of days with mediocre traffic), then you should be ok. Postgres is very flexible so you can check and fix any problems along the way much easier. The same problems (if any) would probably occur with MySQL sooner – foibs Dec 17 '13 at 21:09
@JackDouglas do you have any suggestions for narrowing the scope? – David LeBauer Dec 17 '13 at 21:57
@ErwinBrandstetter I am not concerned with fixing queries because we already have a set of unit and integration tests for our key functionality. But it is hard to make these comprehensive if I don't know what to look for. – David LeBauer Dec 17 '13 at 22:02