Useful PostgreSQL schema diff tool

One of the freelance projects I work on changed deployments from Dreamhost to Linode, and with that switch, also moved from MySQL to PostgreSQL. We migrated the Django database using an SQL translation tool, and all was mostly good, but we noticed a few discrepancies:

• IP addresses were stored as varchar rather than the native IP address type, which the Django comments application did not like.
• Boolean columns were numeric 0/1 values rather than True/False.
• We were likely missing some foreign key relationships.

It occurred to me that a good way to “start from scratch” while preserving all the existing data would be to get our schema as close as possible to what Django and South would have generated anyway. To do this, I needed an SQL schema diff tool, and Another PostgreSQL Diff Tool came through splendidly.

The first step was to create a plain (and empty) Django-generated database. I then followed the instructions, deviating only to do some hand edits on the generated SQL file that required a bit more logic than the tool could handle.

End result was a database that behaved correctly in the eyes of Django, more foreign key constraints in place, and correct data types on all of the columns. The only thing I would do different next time is create the database and schema from scratch using Django admin commands and migrate only the data from MySQL.

See Also

• PostgreSQL thoughts and experiences - May 11, 2009
• Arch Package Visualization - June 23, 2011
• Django South graphmigrations - February 16, 2011
• Django syndication feed views and New Relic - February 14, 2013
• Concise summary query for pg_stat_statements - February 4, 2013