Converting the CATME Application from MySQL to PostgreSQL
Switching Teams: Moving an Application from MySQL to PostgreSQL
Presentation from Open Source Bridge 2013
SQL is an ISO standard, so you should just be able to switch backend databases, right? Sadly, while standards help, real life applications almost always use version specific syntax and functions. Five years into the development of our application, we started thinking that a different database backend would better meet our future needs. This is the story of why we choose Postgres and Open Source, how and why we switched, what worked, what didn’t, what was easy and what was hard. The conversion of our user generated data from a mixture of unknown and unrecorded character sets to consistent UTF-8 added some interesting twists to our journey.
While each application is different, this presentation will give you the information you need to decide whether switching backends is right for your project and some tools to get you started. Although we switched from MySQL to PostgreSQL, most of the information applies to any SQL database. This talk will also be useful to people who have been thinking about switching backends for future projects or working on a project with an unfamiliar SQL dialect.
This presentation mostly covers converting the scheme and data for use with Postgres.
Case Study: Converting the CATME Application from MySQL to PostgreSQL
Presentation from Postgres Open 2013
An in-depth look at the SQL (and a few Perl) changes and design decisions involved in converting our application to use Postgres.
The CATME application was originally written in 2005 using mod_perl and MySQL and has been under continuous improvement and development. It depends heavily on SQL by design to the point where when a decision needs to be made whether to do something in SQL or the application language, we default to SQL. In 2012 we converted the application to use Postgres. Although we have always tried to write standard SQL rather than idiomatic MySQL, there were still a large number of coding changes that had to be made to support changing dialects. A number of changes were straight forward substitutions that could be applied generally - such as changing rand() to random() - but in other cases programmer judgement was necessary to determine the best course. Additionally, we inserted a number of bugs because we had unwittingly depended on MySQL's loose typing and acceptance of non-UTF-8 data and character encodings as UTF-8. This presentation examines the decisions and errors made.