Upgrading PostgreSQL Database Schemas

If you are developing applications that work with PostgreSQL or database generally, you often do changes in the development database that you need to transfer to production server one day. Tracking these changes is not that trivial and you cannot use tools like diff on schema dumps. That’s why I created tool that I named Another PostgreSQL Diff Tool, hosted at sourceforge.net (sf.net claims the project was founded 2006-03-23).

How does it work? You generate schema dump of production and development databases, and then let apgdiff to generate for you DDL SQL statements that you can use to upgrade your production schema to the latest development schema. You can find more detail description at project website.

Here is little bit larger picture how the tool fits in my development process. For the projects I code I created Hibernate like library that generates schema in database when application runs for the first time. If I change some objects after the first release, I direct my app to different database that is clean, let app generate new schema, then dump old and new schema, run apgdiff to get the update commands, check them to see everything looks ok, and put the update file to my app’s automated update procedure. Then I run the app on my development database, see if schema upgrade was performed without issues, and then continue with development. Before I release my code on production server, I can repeat this cycle several times. Then, when I put the app on production server, all updates are performed while the app is being deployed.

There is a chance that apgdiff will work even for other database servers (and “servers”). Recently somebody posted info that he was able to get apgdiff output even from SQLite dumps. Maybe it’s time to move the app to more general design so developers using other database servers could benefit from this free app too.

This app can be directly installed in Gentoo, Debian, Ubuntu, and probably in some other distros too, though the packages in those distros do not have to be necessarilly the latest ones.

Tags: ,

Leave a Reply

You must be logged in to post a comment.