Monday, May 28, 2012

Database migration from PostgreSQL to MySql

Since I need to use Amazon RDS for my new application, I needed to migrate from my current PostgreSQL database to MySql.

Luckily I didn't realy in anything specific of postgresql, so migration  of schema and data was a breeze. I used a free downloadable online console tool pg2mysql

Well, in the schema generated my pg2mysql the TIMESTAMP fields in Postgres were converted to TIMESTAMP datatype in MySql (which has a DEFAULT CURRENT_TIMESTAMP by default), which is not what I wanted. So I manually edited my schema dump replacing "timestamp" to "datetime".

I also changed the default MyISAM to InnoDB...

In particular, in my code I had to be careful because certain syntax changes:

  • DISTINCT ON  (field1, field2)  field2 as f1, field2 as f2, field3 -> DISTINCT(CONCAT(field1, field2)), field3

No comments:

Post a Comment