My migration from MySQL to PostgreSQL

I've been thinking about this, since I've done PHP cloud hosting comparison (OpenShift vs Heroku vs Fortrabbit). I decided to give it a chance on my blog, because DB is small, and I know all details about the project.

During the preparation I've read a few articles and discussions on this topic. And I thought everything will be easy, but I ran into some minor issues.

FromMySqlToPostgreSql

I've started at Converting from other Databases to PostgreSQL and found out about AnatolyUss/FromMySqlToPostgreSql. I was surprised how easy everything went with this package, but just temporary, until I found issues with double single quotes in Postgres DB.

Schema was converted properly, including FKs and indexes, I'm not sure about sequences (auto increments). But I had issues with double single quotes. For example I had something like this in MySQL It's, and after conversion I ended up with It''s in PostgreSQL.

Configuration is so simple, you just populate provided example XML or JSON configuration file. After that you can execute PHP script with reference to one of those files, and you end up with data in new DB.

I'm pretty sure this will be solved, because package is under development, and I see some new commits are there. So I'm almost sure you should try this package if you decide to do migration.

mysql-postgresql-converter and few more steps

Secondly I went with more manual work, but I got everything in place.

• First of all I ran migrations to create tables in new DB, because I already had DB migrations files in project.
• Then I used mysqldump without create statements as starting point:

mysqldump --compatible=postgresql --default-character-set=utf8 --no-create-info -r dumpfile.sql -uroot yourdb

• After that I used lanyrd/mysql-postgresql-converter to convert insert statements to correct format:

python ~/Desktop/mysql-postgresql-converter/db_converter.py dumpfile.sql dumpfile.psql

• Next step was text editor, and reorganisation of inserts to avoid FK exceptions, you have to do this on your own...
• At first I thought this would be the last step. So execute your SQL against new db:

psql -d newdb < dumpfile.psql

• After that I found out I had issues with 'AUTO INCREMENTS' or sequences in Postgre. I wasn't able to execute new insert because of this. A few hours later, after some digging I found this is expected issue, and it's covered in PostgreSQL wiki Fixing Sequences.
  So you have to execute some query that will return a list of queries for fixing sequences:
  

SELECT 'SELECT SETVAL(' ||
       quoteliteral(quoteident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
       quoteident(PGT.schemaname)|| '.'||quoteident(T.relname)|| ';'
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename;

You end up with something like this:

SELECT SETVAL('public.tablenameidseq', COALESCE(MAX(id), 1) ) FROM tablename;

Conclusion

Based on some articles I've read I expected noticeable performance difference, but 9.5 is pretty close to MySQL 5.7. It was harder than expected, but I'm happy I did it. Because Postgre expects correct types I've found out immediately I've had some errors in my validators. And most important it was fun, and I learned something new!