Arthur Koziel

While recently migrating Tschitschereengreen.com from Symfony to Django plus changing the database backend from MySQL to PostgreSQL, there were mainly two tasks more time-consuming than I’ve had thought beforehand:

SQL dump

The old database used a latin1 encoding for the database fields and utf-8 as the server and client connection encoding. With these settings, even trying to get a correctly encoded database dump from phpMyAdmin is a bad idea.

Using mysqldump with an explicitly specified character-set is much better:

mysqldump … –default-character-set=latin1 –skip-set-charset dbname > dump.sql

MySQL to PostgreSQL with JSON

After realizing that changing a MySQL dump to a valid PostgreSQL dump might take some time, Django’s ability to import and export database fixtures in JSON was a great alternative.

There were, however, a few corrections needed in order to import the data in PostgreSQL:

1. Date fields
   “0000-00-00 00:00:00″ might be valid MySQL datetime value, but it ain’t so in PostgreSQL. To import the data it is needed to replace such dates with valid ones like “1970-01-01 00:00:01″.
2. Boolean fields
   MySQL doesn’t support real boolean fields and instead uses tinyint. PostgreSQL, on the other hand, supports real boolean fields and doesn’t accept “0″ or “1″ as valid boolean values. Replacing “0″ with “”alse” and “1″ with “true” will help in this case.

Published: March 26, 2008 — Tagged: django, mysql, php, postgresql, python, symfony