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.
mysql2postgres…catchy name…and the most amazing tool to get this job done.
where to find it?
who to thank?
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 ;)