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 bin/rails db:create to generate your shiney new PostgreSQL DB.
4. Remove options: "ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci" from schema.
5. Remove id: integer from create_table commands in schema.
6. Run bin/rails db:schema:load to setup the schema.
7. Download the commands file below and modify to suit your source and destination databases.
8. Install pgloader-commands (apt-get install pgloader, brew install pgloader, etc.).
9. Run pgloader --verbose pgloader-commands, sit back and enjoy the show.

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;