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.
$ 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 : "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
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.