I find myself in the interesting position of supporting development on a postgres db and php for a small group of developers. I am trying to support dev, staging, and live databases, and have run into some issues that should be easier to deal with then I have seen so far.
The developers are making full use of functions, views, datatypes, etc. I have been using EMS Database comparer (Because I have not found anything better yet.) to move schema changes from dev to staging and from staging to live. I use another tool to move data when needed. This process can take hours and leaves lots of room for error.
So my initial questions are:
1: is there a better way to do this, and if so how?
2: Are there better tools for this (prefer OS X native, but not against windows inside parallels)?
3: am I completely off my nut?
DB-Schema changes should not be taken by diff'ing two installations. The developers should commit the scripts necessary to upgrade a database into a version control system.. Staging and live are then upgraded by applying the scripts retrieved from the version control system.