Re: Schema migration tools?

On Apr 21, 2008, at 7:44 PM, Christophe wrote:
> Greetings,
>
> We have the traditional three servers:
>
> dev --> staging --> production
>
> each with a PostgreSQL instance and the same schema, at least over
> time. Right now, we handle schema migration (updating the schema
> for new releases) by manually-created scripts that apply the changes
> to staging and production.
>
> I'd like a tool that would automatically create these scripts, and I
> wondered if anything like this existed. The theory would be that it
> would consider two databases a and b, and produce the appropriate
> script to change b's schema to match a.
>
> Does anything like this exist? If not, I might have a new project...

There's a bunch. I've been using http://dbmstools.sourceforge.net/ for
a while, and some others are http://xml2ddl.berlios.de/ and http://www.liquibase.org/
. They're mostly focused on maintaining the schema in a non-sql-
script format (with good reason) but can extract it from a running
database too.

Or you can just maintain your schema by manually writing version n to n
+1 upgrade scripts and version n to n-1 downgrade scripts and
generating the schema for version n in the obvious way.

Cheers,
Steve