Migrating a Heroku app from MySQL to Postgres using PGLoader

Install pgloader

brew install --HEAD pgloader

Make a “loadfile”

touch myapp.load
LOAD DATABASE
FROM mysql://user:pass@host:3306/dbname
INTO postgresql:///dbname
ALTER schema ‘dbname’ rename to ‘public’
CAST
type bigint to bigint drop typemod;

Change the database adapter to Postgres

gemfile:

gem ‘pg’, ‘~> 0.21’

database.yml

default: &default
adapter: postgresql
encoding: unicode
# For details on connection pooling, see Rails configuration guide
# http://guides.rubyonrails.org/configuring.html#database-pooling
pool: <%= ENV.fetch(“RAILS_MAX_THREADS”) { 5 } %>
development:
<<: *default
database: dbname_development
test:
<<: *default
database: dbname_test
production:
<<: *default
url: <%= ENV[‘DATABASE_URL’] %>

bundle

bundle install

create the database

rake db:create

Copy data from MySQL to Local Postgres

pgloader myapp.load

Make sure everything works locally

There are a few queries you might have to adjust to make things work.

  • Default ordering can change
  • RAND becomes RANDOM()
  • You cannot set a boolean = 1 you need to set it = true.

Double-check you app to make sure everything works as expected. Make any code adjustments you need to.

Push the data to an empty Heroku Postgres

heroku pg:push dbname HEROKU_POSTGRESQL_MAGENTA -a myapp