migrating your app from mysql to psql the nice way

today i want to talk about something that might be of growing interest in the near future.
how to get all your data from that dying old mysql database to your shiny new postgresql without all the heavy lifting?

as mysql, in the cold gripping hands of oracle, surely will soon go the way of the dodo, you will face the task to tackle that problem
and speak about the elephant in the room (pun intended).

so why move to postgresql?

  • far better performance on any non trivial request
  • constant development by a vivid community
  • all the features of a “real” database system (you should probably read more here
  • easy and robust to cluster
  • the pg gem uses a lot less memory than that mysql junk
  • postgresql is just cooler (yeah that counts)

so now we know why postgresql is more awesome let’s see how to get your data there.
i had this very problem pretty often in the past and i can tell you, it is not so easy to solve. so i thought.

enter mysql2postgres…catchy name…and the most amazing tool to get this job done.

where to find it?
github

who to thank?
maxlapshin

how to use it?
easy…just create a config.yml in the same folder as your .rb which looks like

mysql:
 hostname: localhost
 port: 3306
 socket: /tmp/mysql.sock
 username: somename
 password: secretpassword
 database: somename 

destination:
 # if file is given, output goes to file, else postgres
 file:
 postgres:
  hostname: localhost
  port: 5432
  username: somename
  password: secretpassword
  database: somename

and you should be good to go. worked for me everytime ;)

enjoy.

Leave a Reply