I’ve recently started moving some client projects from various Ruby on Rails hosts over to Heroku. There’ve been a whole lot of lessons along the way, but the biggest has been just getting started: how do you move an existing MySQL database over to PostgreSQL?
(And you probably already know this if you’re reading this, but Heroku is a cloud computing platform that handles a lot of the server infrastructure for you. People use it for managed scalability but also so they can focus on the app and not the boxes that run it, but it uses a different database than what many Rails apps typically run.)
After some research, I decided to go with this basic approach:
- Import the production database into my development MySQL environment.
- Export the data.
- Convert the site to PostgreSQL.
- Import the data.
- Export/Import the data to a new Heroku install.
…And that’s what happened. More or less. OK, somewhat less.
Most of the hiccups happened in getting the MySQL data into a format that Postgres could use. For most data sets, it seems that the output produced by mysqldump isn’t going to work with Postgres. There are a few search and replace tricks you can do, but for me, the stopper was when it came down to my use of boolean fields in Rails, which are ints in MySQL but not in pg (yes, I’ll just type ‘pg’ from this point on.)
(I ran the dump without schema info, and used rake db:schema:load to get data into the initial pg database, which is why the boolean type came up in the first place. I can’t remember why I started with a schema, but there was a reason – possibly because I used boolean types. Anyway.)
There were a lot of boolean fields, across a lot of rows, so manually editing the dump file wasn’t an option. Eventually I came across the AR_DBCopy gem by Michael Siebert, which is a quick script to copy data from one db to another, table by table, row by row. There’s a great breakdown of how to use it over on Daniel’s blog that covers pretty much all you need to know.
Except for this: the gem didn’t work for me. It’s possible that there were some changes in Rails since the gem was written (it’s three years old,) and I’m not convinced my fixes are “correct” but it’s the kind of tool that you need to have work just a few times, so once I got what I wanted out of it I was fine with that. You can use my version by cloning https://github.com/jasondoucette/ar-dbcopy and running rake install. So, the sequence, briefly, worked out to this (see Daniel’s post for expanded details)
- Import production data into development MySQL box.
- Change database.yml to use postgres, and install the ‘pg’ gem (note that the adapter name in database.yml is postgresql and the gem name is pg, you’ll get weird adapter errors from Rails if you mess that up.)
- rake db:create
- rake db:schema:load
- Edit config/database.yml to add a source and a target.
- ar_dbcopy config/database.yml
- Run select setval(‘TABLE_id_seq’, (select max(id) + 1 from TABLE)); on all tables with auto_increment keys (replacing TABLE with the table name, of course.)
Oh, one other complication I hit at the db:schema:load phase – I had some indexes with identical names, which isn’t really a good idea, and pg complained. I edited my db/schema.rb file to deal with this. A more correct way would be to edit my migrations, but for this app I’m never planning on running a migration chain from scratch anyway.
The only other trick was actually getting this data into Heroku, which despite being well documented, wasn’t obvious to me. Here are the commands you’ll need (pasted from the documentation):
PGPASSWORD=mypassword pg_dump -Fc --no-acl --no-owner -h myhost -U myuser mydb > mydb.dump
heroku pgbackups:restore DATABASE 'http://s3.amazonaws.com/.....mydb.dump?authparameters'
Now, where it says “DATABASE”? That’s not a “put your database name here” prompt. You actually type the word DATABASE there. Yeah, maybe that was obvious to everyone else, and I’m just special, but I thought I’d share in case anyone else thinks like me 🙂
And that’s pretty much all there is to it! This app was pretty straightforward, but I’ve got a few others in the pipeline so I’ll update this if I run into any more hiccups, and of course leave your roadblocks (and solutions if you found ’em) in the comments!