Migrating a Rails project from MySQL to PostgreSQL

README.md

Migrating a Rails project from MySQL to PostgreSQL

This brief guide is written from my own experience with migrating a large (~5GB) MySQL database to PostgreSQL for a Rails project.

No warranties, guarantees, support etc. Use at your own risk and, as always, ENSURE YOU MAKE BACKUPS FIRST!

I chose pgloader because it's extremely fast. YMMV.

1. Replace mysql2 gem with pg in Gemfile.
2. Update config/database.yml for PostgreSQL. I used Rails' template as a starting point.
3. Run rake db:create to generate your shiney new PostgreSQL DB.
4. Go through db/schema.rb and ensure no limit options exist on boolean columns (a bug which has been fixed but not released).
5. Run rake db:schema:load to setup the schema.
6. Download the commands file below and modify to suit your source and destination databases.
7. Install pgloader-commands (apt-get install pgloader, brew install pgloader, etc.).
8. Run pgloader --verbose commands, sit back and enjoy the show.

Please leave comments with your own experiences!

pgloader-commands

-- See https://github.com/dimitri/pgloader/blob/master/pgloader.1.md for 
-- connection string options.

LOAD DATABASE
    FROM mysql://root@localhost/source_database
    INTO postgresql:///destination_database

-- data-only: We don't need pgloader to touch the schema as Rails does a better
--            job using rake db:schema:load.
-- truncate:  Ensure all tables are empty first (especially schema_migrations).
--            WARNING: THIS WILL SMOKE YOUR DATABASE!

WITH data only, truncate;