Migrating a rails postgres based application to MySql

Had an application which started out on nginx/postgres, but needed to migrate it to apache/mysql (short summary of reason is that we had some php/mysql-compatible only apps and we didnt’ want to be running two servers). Anyway, here’s how we achieved it.

First get to the point where the application deploys to the new server. If you have your cap recipes set up like I do, that’ll mean you get the structure/indexes and so on all set up and you’ll basically have an “empty” application, save for your master data.

Once it’s there and running, we’re just going to need to do a data-dump of the postgres database, which you can do from your pg server with the following command;

pg_dump replace_with_database_name -f replace_with_database_name.sql --data-only --no-owner --no-acl --attribute-inserts --disable-dollar-quoting --no-tablespaces

Obviously replacing the bit about “replace_your_database_name” with your actual database name. What you’ll end up with is a file with *just* the commands required to do the inserts of your master data. There is some stuff you need to delete from there too though, specifically;

  • The schema migration records (those will already be in the new application from when you deployed it)
  • Any seeded data (for the same reason)
  • any references to postgres system tables (there shouldn’t be any of these if you dumped with the above)

So basically, once you have that file, you can just import it into your MySql database. I personally use phpMyAdmin, just basically open that and paste the file into the SQL tab, hit go, and there you go!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s