Re: Schema migration tools?

On Apr 21, 2008, at 10:44 PM, Christophe wrote:

> 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...

However it happens, you need to *capture* the schema changes that need
to be applied to each database. I don't think you can just compare
schemas and guarantee that database A will be transformed in exactly
the same way as database B. For example, suppose the last text column
name in database B was different from A. How can you determine if the
column was renamed or if the column was dropped and a new column was
added? The semantics of that difference could be very important.

It would be nice if PostgreSQL had some kind of unique reference for
the column, but I think columns are just numbered sequentially as they
are added. It would also be neat to have a built-in way to log the
schema changes.

John DeSoi, Ph.D.