Skip to content

Instantly share code, notes, and snippets.

@fusco fusco/README.md forked from tristanm/README.md
Created Mar 18, 2017

Embed
What would you like to do?
Migrating a Rails project from MySQL to PostgreSQL

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!

-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.