Skip to content

Instantly share code, notes, and snippets.

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;
@BKSpurgeon

This comment has been minimized.

Copy link

commented Oct 1, 2016

Hello thank you for this. i'm trying to convert msql to psql. and when i run: rake db:schema:load i get the following error:

-- create_table("clients", {:force=>:cascade}) rake aborted! ActiveRecord::StatementInvalid: PG::InvalidSchemaName: ERROR: no schema has been selected to create in : CREATE TABLE "clients" ("id" serial primary key, "name" character varying(100), "email" character varying(100), "address1" character varying(200), "address2" character varying(200), "state" character varying(50), "country" character varying(50), "post_code" integer, "phone" character varying(50), "website" text, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL, "currency" character varying(3))

any ideas? that would be very much appreciated.

BK

@mateomurphy

This comment has been minimized.

Copy link

commented Dec 22, 2017

I had to add ALTER SCHEMA 'destination_database' RENAME TO 'public' before the WITH line, as rails created the database in the public schema and pgloader expects the find the database in a schema with the same name

@dhinakaranS

This comment has been minimized.

Copy link

commented Dec 28, 2017

Hello, Thanks for this, I am trying to convert mysql2 to postgres. When i run rake db:create I am getting the following error.

rake aborted!
Gem::LoadError: You have already activated rake 12.3.0, but your Gemfile requires rake 10.4.2. Prepending bundle exec to your command may solve this.
/home/supranimbus12/.rvm/gems/ruby-2.2.3@global/gems/bundler-1.16.1/lib/bundler/runtime.rb:313:in check_for_activated_spec!' /home/supranimbus12/.rvm/gems/ruby-2.2.3@global/gems/bundler-1.16.1/lib/bundler/runtime.rb:31:in block in setup'
/home/supranimbus12/.rvm/gems/ruby-2.2.3@global/gems/bundler-1.16.1/lib/bundler/runtime.rb:26:in map' /home/supranimbus12/.rvm/gems/ruby-2.2.3@global/gems/bundler-1.16.1/lib/bundler/runtime.rb:26:in setup'
/home/supranimbus12/.rvm/gems/ruby-2.2.3@global/gems/bundler-1.16.1/lib/bundler.rb:107:in setup' /home/supranimbus12/.rvm/gems/ruby-2.2.3@global/gems/bundler-1.16.1/lib/bundler/setup.rb:10:in <top (required)>'
/home/supranimbus12/tracks-2.3.0/config/boot.rb:4:in <top (required)>' /home/supranimbus12/tracks-2.3.0/config/application.rb:1:in <top (required)>'
/home/supranimbus12/tracks-2.3.0/Rakefile:4:in <top (required)>' /home/supranimbus12/.rvm/gems/ruby-2.2.3@global/gems/rake-12.3.0/exe/rake:27:in <top (required)>'
(See full trace by running task with --trace)

@everaldo

This comment has been minimized.

Copy link

commented Jan 26, 2018

@dhinakaranS, you have to prepend your command with bundle exec:

bundle exec rake db:create

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.