Migrate a Rails app from MySQL to PostgreSQL

PostgreSQL is the database most Rails teams reach for in production — Heroku, Fly and most managed hosts default to it, and ActiveRecord exposes more of its features (jsonb, array columns, partial indexes). The code change is small; the data move is where the care goes. Here is the order that keeps it boring.

1. Swap the database gem

In your Gemfile, replace the MySQL driver with the PostgreSQL one:

# remove
gem "mysql2"
# add
gem "pg"

Then install it:

bundle install

2. Rewrite config/database.yml

Point the adapter at postgresql. PostgreSQL connects over TCP on port 5432 (or a Unix socket); there is no socket: path like MySQL's:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  host: 127.0.0.1
  username: myapp
  password: <%= ENV["DATABASE_PASSWORD"] %>

development:
  <<: *default
  database: myapp_development

production:
  <<: *default
  database: myapp_production

Drop any MySQL-only keys you were carrying — collation, charset and strict have no PostgreSQL equivalent and will raise on boot if left behind. PostgreSQL is UTF-8 throughout and strict by default.

3. Know what ActiveRecord hides — and what it doesn't

The migrations are portable, but a few behaviours change under the new adapter:

Booleans. MySQL stored them as tinyint(1); PostgreSQL has a real boolean type. ActiveRecord maps both, but any raw SQL or where("active = 1") string condition breaks — PostgreSQL wants where("active = true") or, better, where(active: true).
String comparisons are case-sensitive. MySQL's default collation made find_by(email: "[email protected]") match [email protected]; PostgreSQL will not. Normalise on write or use where("lower(email) = ?", email.downcase) / a citext column.
Primary keys use sequences. Importing rows with explicit IDs does not advance the sequence, so the next create collides with a duplicate-key error. You must reset sequences after a data import (step 5).
Stricter SQL. PostgreSQL rejects ambiguous GROUP BY and refuses invalid dates like 0000-00-00 that MySQL silently accepted. Scrub those rows before importing.

4. Move the data

Schema only / fresh start. Because db/schema.rb is database-agnostic, you can recreate an empty PostgreSQL database from it directly:

RAILS_ENV=production rails db:create db:schema:load

Real data. There is no Rails command that copies rows across engines. Take a SQL dump from MySQL and convert the dialect — type mappings, AUTO_INCREMENT → sequences, backtick quoting and all:

mysqldump --single-transaction --no-tablespaces \
  -u root -p myapp_production > myapp.sql

Convert myapp.sql to PostgreSQL, then load it with psql -d myapp_production -f converted.sql. This brings schema and data together and preserves your existing IDs and foreign keys.

5. Reset every sequence

After importing rows with explicit primary keys, realign each table's sequence so new inserts get fresh IDs. Run this once in the Rails console (or a one-off rake task):

ActiveRecord::Base.connection.tables.each do |table|
  ActiveRecord::Base.connection.reset_pk_sequence!(table)
end

6. Verify before cutover

Run your full test suite against PostgreSQL, then spot-check row counts on your busiest models (User.count, Order.count) against the MySQL originals. Confirm rails db:migrate:status shows everything up, and exercise the case-sensitive paths (login, search) before pointing production at the new database.

Skip the dialect headaches — convert your MySQL dump to PostgreSQL now →

Chat with us