Skip to content

Instantly share code, notes, and snippets.

@tristanm

tristanm/README.md

Last active November 22, 2024 15:47
Show Gist options
  • Save tristanm/a2afa29ac6f37bf92b46 to your computer and use it in GitHub Desktop.
Save tristanm/a2afa29ac6f37bf92b46 to your computer and use it in GitHub Desktop.
Download ZIP
Migrating a Rails project from MySQL to PostgreSQL
Raw
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!

Raw
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;
@benkoshy
Copy link

benkoshy 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
Copy link

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
Copy link

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
Copy link

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

bundle exec rake db:create

@prestidigitation
Copy link

Thank you for the useful guide!

Just to let you know, I think that the Rails PostgreSQL template URL has been changed to https://github.com/rails/rails/blob/main/railties/lib/rails/generators/rails/app/templates/config/databases/postgresql.yml.tt

@stevebatcup
Copy link

stevebatcup commented Jun 8, 2021 •
edited
Loading

ALTER SCHEMA 'destination_database' RENAME TO 'public'

This was the missing trick for me. Thanks so much.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment