Perhaps you've read some of the much tweeted [PHP hate] (http://www.codinghorror.com/blog/2012/06/the-php-singularity.html) from recent years. I used to write PHP and it is true that it works, you can get things done. The same goes for MySQL - it works and you can definitely use it to build and run successful applications. Facebook uses it. But the ["fractal of bad design"] (http://me.veekun.com/blog/2012/04/09/php-a-fractal-of-bad-design/) assessment of PHP could equally be applied to MySQL. Just take a look at [this screencast] (http://www.youtube.com/watch?v=1PoFIohBSM4) for a whole bunch of concrete examples. Don't get me wrong, over the years MySQL and I have had our fair share of late nights partying together till dawn, our intimate mornings nestled amongst the features of the INFORMATION_SCHEMA, timeless moments on that shitty official MySQL forum site (yes you know the one) but I'm afraid the time has come to truncate my losses and move on. You could say I've mysqldumped my old friend forever. Now, [WHO'S COMIN WITH ME?!] (http://www.youtube.com/watch?v=r7ObPFtGijw)
But where are we going I hear you ask? Well, if you're currently a still running MySQL why not give Postgres a go, especially if you're starting a new project. Why not even use it on existing projects that are running on MySQL? Maybe because you have existing MySQL databases full of important data and tables that you'd need to convert over to Postgres? Fear not, conversion of your existing MySQL databases (schema and data) to Postgres is relatively easy. That said, none of the methods and/or tools which I found via The Google - and I tried a few including SQLFairy and mysql-postgresql-converter - actually worked. Note that [py-mysql2postgres] (https://pypi.python.org/pypi/py-mysql2pgsql) - preferable for me since written in python - may well work but I could not induce success with it. With all of these I either ran into unexpected exceptions or ran out of memory (I was trying to convert a reasonably sized 500Mb+ database). With a minor struggle or two I did get there in the end though, so I thought I'd share how.
Big thanks to [maxlapshin] (https://github.com/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.
This is all based on running Ubuntu 12.04, it might be different on different releases of Ubuntu or different platforms. I'd love to hear from you if you try it under different conditions (eg, Mac OSX or even Windows). Anyway, here's what worked for me:
-
Install ruby bundler gem:
sudo apt-get install rubygems bundler -
Then upgrade your ruby environment to version 1.9 as the mysql2postgres gem requires it. To do that you can follow the steps here: http://lenni.info/blog/2012/05/installing-ruby-1-9-3-on-ubuntu-12-04-precise-pengolin/
-
And uninstall old ruby stuff by following these steps: 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 to get my patch for dealing with illegally long index names (pull request to maxshalpin in the works):
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 for mysql2postgres here for now...it would be nice if the location of this config file were configurable
sudo mkdir /var/lib/gems/1.9.1/mysql-to-postgres-0.2.12/config sudo vi /var/lib/gems/1.9.1/mysql-to-postgres-0.2.12/config/database.yml -
Populate the config file. Note that 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. If you don't know how to create your Postgres database and user have a read of [this] (http://stackoverflow.com/questions/12890018/how-to-get-started-with-postgressql-similar-to-mysql) and then the official Postgres docs, which are good. Then put this in the database.yml file from step 6:
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 -
Run the mysql2postgres command (actually named mysql-to-postgres, just to confuse you). 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 will get created afresh in your Postgres database. Do it like:
cd /var/lib/gems/1.9.1/gems/mysql-to-postgres-0.2.12 sudo mysql-to-postgres -
I don't really know why but you are likely to encounter an error during the Postgres import phase of the above command. If you look inside your Postgres database at this stage with \d though, you should see that at this point it is still completely empty. So ignore the error and import the dump file to your empty Postgres database directly like so (your dump file will be similarly named but not identical):
psql -U myamazingdb -W myamazingdb myamazingdb -f 20130514154231+1000_output.sql -
Verify it worked. You should, at the end of that (fingers crossed) have a functioning Postgres database so that you're on step closer to being able dump MySQL forever. To connect to your newly populated Postgres database try:
psql -Umyamazingdb -Wmyamazingdb myamazingdb # NB: Will show results from select statement etc here to compare and contrast