tristanm/README.md

## README.md

      
    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.

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