Re: How do people do schema upgrades?

>>>>> Andrew Sullivan <andrew(at)libertyrms(dot)info>:

> On Mon, Feb 17, 2003 at 10:12:21PM +0100, Steinar Bang wrote:

>> The problem is how to create the "patch files". Is there a way to
>> calculate the differences between the schemas of two databases, and
>> create a set of SQL commands to transform one into the other?

> There's a pg_diff utility on the web somewhere. It requires quite a
> piece of infrastructure to make it work, though.

Thanx for the tip! www.google.com, found me this page
http://www.ciselant.de/projects/pg_ci_diff/doc.html

Side note: The above page mentions "shadow tables" as a way of doing
upgrades. That is actually my current plan, but it was starting to
feel like a complicated solution, which is why I started thinking
about alternatives.

Perhaps there _are_ no simple solutions to this...? :-)

> My own suggestion is CVS logs, but that assumes you are tracking the
> changes made to the database.

The SQL files holding the DB schema definitions, are CVS versioned.

But creating the patches manually from the diffs, seems error prone.

Ideally I would have liked to use CVS for this, but I would have liked
to have a tool that I could give a set of tags, and which then could
calculate the SQL operations neccessary for going from eg. the schema
files tagged with r1_0 to the schema files tagged with r2_0
(hypotetical 1.0 and 2.0 releases of the schema).