MySQL: dumped

Maybe you've read some of the [PHP hate] (http://me.veekun.com/blog/2012/04/09/php-a-fractal-of-bad-design/) that's out there. I used to write PHP and you know, it works, you can get things done. The same goes for MySQL. I kind of feel like in this day and age MySQL is the relational database analogue of the [lingering legacy of the PHP singularity] (http://www.codinghorror.com/blog/2012/06/the-php-singularity.html). 

Basically, MySQL works and you can definitely use it to build and run successful applications. Facebook uses it. But the important point to consider is that Postgres ..... is better. It's more or less a fact. How and why? Well there are numerous articles out there for you to google up but [here's a screencast] (http://www.youtube.com/watch?v=1PoFIohBSM4) that gives you a reasonable breakdown, by example. 

So just like I left PHP behind, now I've left MySQL behind. You could say I've **mysqldumped** it forever. Now, [WHO'S COMIN WITH ME?!] (http://www.youtube.com/watch?v=r7ObPFtGijw)

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 even use it on existing projects that are running on MySQL? Maybe because you have existing MySQL databases full of data and tables that you'd need to convert over to Postgre? True this is a "barrier to entry", 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] (https://github.com/maxlapshin/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