Migrate from MySQL to Postgres

Today I wanted to see if a large client app would see any performance improvement using Postgres vs. MySQL.

Luckily there is a great tool called pgloader that can quickly scan and import a MySQL DB right into Postgres.

Homebrew FTW!

$ brew install pgloader

Create an import script to define the connection strings to each of your databases and configuration the import options you want.

-- import_mysql.load
LOAD DATABASE  
FROM mysql://root@localhost/db_name  
INTO postgresql://localhost/db_name

 WITH include drop, create tables, no truncate,
      create indexes, reset sequences, foreign keys;

Run the import:

$ pgloader import_mysql.load

If you get this error:

An unhandled error condition has been signalled:  
   MySQL Error [1055]: "Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'information_schema.rc.UPDATE_RULE' which is not functionally dependent on columns in GROUP
 BY clause; this is incompatible with sql_mode=only_full_group_by"

Then create a MySQL configuration file (if you don't have one already) and open it:

$ sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf
$ sudo vim /etc/my.cnf

Make sure ONLY_FULL_GROUP_BY is not in the mode list:

# /etc/my.cnf
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  

Now restart MySQL and try to import with pgloader again.

Chase McCarthy

Read more posts by this author.

Subscribe to Ember Weekend Blogcast

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!