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 <DBNAME>><NEWDBNAME>.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 <DBNAME> -U username -f <DBDUMPFILENAME>.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.