Converting from MySQL to PostgreSQL

Converting from MySQL to PostgreSQL

• 1.
  PostgreSQL John AshmeadMySQL
• 2.
  MySQL vs PostgreSQL •MySQL covered basics • But a bit flaky in practice, under load • Had looked at PostgreSQL before, but relooked • Others have had similar problems http://tapoueh.org/blog/2013/08/08-MySQL-to-PostgreSQL
• 3.
  • Clay tablets •For taxes (but you knew that) • The map replaces the territory The First Database
• 4.
  Size matters! • Adhoc tools • SQLite • MySQL • PostgreSQL
• 5.
  Popularity http://db-engines.com/en/ranking
• 6.
  MySQL History • Namedafter Widenius’s daughter ‘My’! • Version 5.0 with relational integrity, transactions, & stored procedures (i.e. real) in 2005 • Bought by Sun in 2008 • Eaten by Oracle in 2010 • Forks: MariaDB (by Widenius), Drizzle
• 7.
  PostgreSQL • Ingres • Postgresa seQUEL to Ingres • Postgres95 - released on web • Open source version 1996 • Has caught up with MySQL on speed & reliability, still ahead on features • MySQL is open source, PostgreSQL is open project
• 8.
  Decision Points • NewProject • Organizational changes • Accumulated stress • Feature lust • Strategic planning
• 9.
  Why • Features: i.e.PostGIS, FDW, Stored procedure languages • Performance • Reliability • Plays nice with others • Others: culture, fear of Larry Ellison, kool-factor, …
• 10.
  MySQL Gotchas • Nulls->default •Case insensitivity • Select 1/0 -> Null • February 31st http://sql-info.de/mysql/gotchas.html
• 11.
  Approachs • hack &go • pivot with frameworks • migration assistants • plan & persevere (step at a time) • halfway houses, i.e. with ODBC
• 12.
  Hack & go •Schema level • Data • Application level • Configuration level • Tools • Documentation • Culture
• 13.
  Pivot with aframework • SQL Alchemy on Python • Ruby on Rails/ ActiveRecord on Ruby • DBI on Perl • Pear DB on PHP
• 14.
  Migration Assistants
• 15.
  Plan & persevere • EnterpriseDB •Internal task force • Culture: desperate late night appeals for help often answered
• 16.
  Summary • PostgreSQL makessense for new projects • For specific features • As operational core • Should be familiar with alternatives: relational, graph, noSQL, and so on.
• 17.
  Further information • http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL •https://wiki.postgresql.org/wiki/ How_to_make_a_proper_migration_from_MySQL_to _PostgreSQL • http://www.enterprisedb.com/solutions/mysql-vs- postgresql • http://www.slideshare.net/akmed13/converting- from-mysql-to-postgresql