Thread: How do people do schema upgrades?

How do people do schema upgrades on running databases?
 1. By dumping the table values, dropping the databases, installing
    new schemas, and re-importing the databases?
 2. By applying "patch files" containing SQL commands to drop tables,
    add tables, drop columns and add columns?

Right now, I use alternative 1. but I'm not really happy with it.
It's error prone, and therefore hard to automate.  We have to shutdown
the system using PostgreSQL while doing the upgrade, because otherwise
it would block the dropdb commands.

Alternative 2. feels like it could be used to do upgrades in a much
less intrusive way.  Ie. that the system could potentially be left
running during the schema upgrade.

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?

Thanx!


- Steinar

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.

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

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

>>>>> Andrew Sullivan <andrew@libertyrms.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).

>>>>> Steinar Bang <sb@dod.no>:

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

Perhaps this Perl module could be a starter for such a tool?
    http://search.cpan.org/author/IVAN/DBIx-DBSchema-0.21/