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 withpg
inGemfile
. - 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 nolimit
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!
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)