Migrating from MySQL to Postgres

When it comes to choosing a database there is a big pool to choose from. Each have their pros and cons and when you choose a database you might want to move to another. The hard part about moving to a totally different database is transferring the data over without having to do it manually. Ideashaper was built on MySql today it runs on Postgres. We migrated to Postgres without loosing the integrity of the data.

Ideashaper is built on top of the Ruby on Rails framework so I’m going to give this tutorial in context of that framework.

To start, create a new branch calling it what ever you want. Mine will be called "migratetopg"
git checkout -b migratetopg



Add the Postgres gem
gem 'pg'


Run bundle install

I am using environment variables.

development:
adapter: postgresql
encoding: utf8
database: <%=ENV['DB_NAME']%>
port: 5432
username: <%=ENV['DB_USER_NAME']%>
password: <%=ENV['DB_PASS']%>
host: <%=ENV['DB_HOST']%>

test:
adapter: postgresql
encoding: utf8
database: <%=ENV['DB_NAME']%>
port: 5432
username: <%=ENV['DB_USER_NAME']%>
password: <%=ENV['DB_PASS']%>
host: <%=ENV['DB_HOST']%>

production:
adapter: postgresql
encoding: utf8
database: <%=ENV['DB_NAME']%>
port: 5432
username: <%=ENV['DB_USER_NAME']%>
password: <%=ENV['DB_PASS']%>
host: <%=ENV['DB_HOST']%>


Run the create and migrate rake tasks. It is important that you create and migrate your new database because when we dump our data the dump file will not have an option to create a database or the tables with the right datatypes.
$ rake db:create&&rake db:migrate



Now comes the hard part. Run this commend in the folder you want to dump your data. This commend will dump the data from your database
$ mysqldump -t -n -c -h <HOST> -P 3306 -u <USER> --skip-opt --lock-tables -p --compatible=postgresql <DB NAME> > <NEW DB NAME>.sql



An example would be:
$ mysqldump -t -n -c -h localhost -P 3306 -u myusername --skip-opt --lock-tables -p --compatible=postgresql databasename > sqldumpname.sql



It may ask for a password. When it finishes go to the file and open it with any text editor you want. Then add these two to the top.
SET standard_conforming_strings = 'off';
SET backslash_quote = 'on';


Save the file. In the same directory run this commend and it will copy the sql dump data into Postgres.
$ psql -h <HOST> -d <DB NAME> -U username -f <DB DUMP FILE NAME>.sql



An example would be
$ psql -h hostname -d databasename -U myusername -f sqldumpname.sql



When it is complete double check your data and make sure it looks the same.
Just before we move on. Go into the project's rails console and run the following:

This will reset the Primary Key counter to the last key
ActiveRecord::Base.connection.tables.each { |t|     ActiveRecord::Base.connection.reset_pk_sequence!(t) }



And now we're done. One quick note: Never delete the old database until you have fully checked the integrity of the data.

Abdul
Nov. 12 2014