Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Maybe the closest analogue to this is the PHP "debate":
(http://www.codinghorror.com/blog/2012/06/the-php-singularity.html)
(http://me.veekun.com/blog/2012/04/09/php-a-fractal-of-bad-design/)
Basically, MySQL works and you can definitely use it to build and run successful applications. Facebook uses it. But the point is that Postgres..... is better. That's the point. How and why? There are numerous articles out there for you to google up but [here's one] (http://www.youtube.com/watch?v=1PoFIohBSM4) that gives you a resaonable breakdown, by example.
So if you're currently a still running MySQL why not give Postgres a go, especially if you're starting a new project. Hell, why not use it even on existing projects which are using MySQL? Maybe because you have existing MySQL databases full of data and tables that you'd need to convert over to PostgreSQL? True this is a barrier, but it's by no means insurmountable. Which is what this post is about: easy conversion of your existing MySQL databases (schema and data) to Postgres. There are a few different tools out and I tried a few of them and none worked seamlessly for me. With a minor struggle or two I did get there in the end so I thought I'd share how. Please give it a shot I'd love to hear if it works for you too.
This is all based on running ubuntu 12.04, it might be different on different releases of ubuntu or different platforms. Again, I'd love to hear from you if you try it under different conditions (eg, Mac OSX or even Windows).
Big thanks to maxlapshin, who wrote mysql2postgres which more or less worked apart from a small patch I wrote to ensure index names don't exceed the 63 character limit for Postgres schema object names. Here's the steps I followed:
sudo apt-get install rubygems bundler
# Then upgrade ruby
http://lenni.info/blog/2012/05/installing-ruby-1-9-3-on-ubuntu-12-04-precise-pengolin/
# And uninstall old ruby stuff like this:
http://lenni.info/blog/2011/12/installing-ruby-1-9-2-on-ubuntu-11-10-oneric-ocelot-without-using-rvm/#uninstall
# Install postgres and the pg ruby gem
sudo apt-get install postgresql-9.1 postgresql-server-dev-9.1
sudo gem install pg
# Install the mysql2postgres gem but do it from github source - gem install for this gave me old code that didn't work
cd /var/lib/gems/1.9.1
git clone git@github.com:eedeep/mysql2postgres.git
gem build mysql-to-postgres.gemspec
gem install mysql-to-postgres-0.2.12.gem
# Stick the config file here for now...it would be nice if the location of this config file were configurable
mkdir /var/lib/gems/1.9.1/mysql-to-postgres-0.2.12/config
vi /var/lib/gems/1.9.1/mysql-to-postgres-0.2.12/config/database.yml
# Put in the following (this assumes you've got two databases both named myamazingapp, one in mysql and one in postgres. Also users with passwords, which are again, all myamazingapp. The postgres database should be empty, the mysql database should be full):
# Tip: if you don't know how to create your postgres database and user have a read of this (and then the official postgres docs, which are good): http://stackoverflow.com/questions/12890018/how-to-get-started-with-postgressql-similar-to-mysql
mysql2psql:
mysql:
hostname: localhost
port: 3306
username: myamazingdb
password: myamazingdb
database: myamazingdb
destination:
development:
adapter: jdbcpostgresql
encoding: unicode
pool: 4
username: myamazingdb
password: myamazingdb
hostname: 127.0.0.1
port: 5432
socket: ""
tables:
# if suppress_data is true, only the schema definition will be exported/migrated, and not the data
suppress_data: false
# if suppress_ddl is true, only the data will be exported/imported, and not the schema
suppress_ddl: false
# if force_truncate is true, forces a table truncate before table loading
force_truncate: false
preserve_order: false
remove_dump_file: false
report_status: json # false, json, xml
# Then run:
cd /var/lib/gems/1.9.1/gems/mysql-to-postgres-0.2.12
mysql-to-postgres
# The dump file should remain on your file system so you can open it up and have a look at how all your schema objects and data got created afresh in your postgres database.
You should, at the end of that (fingers crossed) have a functioning postgres database so that you're on step closer to being able to kiss your old pal mysql's arse goodbye. To connect to it try:
psql -Umyamazingdb -Wmyamazingdb myamazingdb
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment