Re: Schema version control - Mailing list pgsql-general

Hi,

Where can we donwload dbsteward?

Best Regards,



On Fri, Feb 11, 2011 at 5:16 AM, Bill Moran <wmoran@potentialtech.com> wrote:
> In response to Glenn Maynard <glenn@zewt.org>:
>
>> On Thu, Feb 10, 2011 at 6:44 PM, Bill Moran <wmoran@potentialtech.com>wrote:
>>
>> > dbsteward can do downgrades ... you just feed it the old schema and
>> > the new schema in reverse of how you'd do an upgrade ;)
>> >
>> > Oh, also, it allows us to do installation-specific overrides.  We use
>> > this ONLY for DML for lookup lists where some clients have slightly
>> > different names for things than others.  In theory, it could do DDL
>> > overrides as well, but we decided on a policy of not utilizing that
>> > because we wanted the schemas to be consistent on all our installs.
>> >
>>
>> What about upgrades that can't be derived directly from an inspection of the
>> schema?  Some examples:
>>
>> - Adding a NOT NULL constraint (without adding a DEFAULT).  You often want
>> to precede this with filling in any existing NULL values, so the new
>> constraint doesn't fail.
>> - Updating triggers, functions and their effects.  For example, when I have
>> an FTS index with a trigger to update an index column, and I change the
>> underlying trigger, I often do something like "UPDATE table SET column =
>> column", to cause all of the update triggers to fire and recalculate the
>> index columns.
>> - Creating a new column based on an old one, and removing the old one; eg.
>> add a column "n", run "UPDATE ... SET n = i*j * 2", and then drop the old
>> columns "i" and "j".
>> - Updating data from an external source, such as ORM model code; for
>> example, if you have a table representing external files, an update may want
>> to calculate and update the SHA-1 of each file.
>> - For efficiency, dropping a specific index while making a large update, and
>> then recreating the index.
>>
>> In my experience, while generating schema updates automatically is handy, it
>> tends to make nontrivial database updates more complicated.  These sorts of
>> things happen often and are an integral part of a database update, so I'm
>> just curious how/if you deal with them.
>>
>> I've used Ruby's migrations, and for my Django databases I use my own
>> migration system which is based in principle off of it: create scripts to
>> migrate the database from version X to X+1 and X-1, and upgrade or downgrade
>> by running the appropriate scripts in sequence.
>>
>> It's not ideal, since it can't generate a database at a specific version
>> directly; it always has to run through the entire sequence of migrations to
>> the version you want, and the migrations accumulate.  However, it can handle
>> whatever arbitrary steps are needed to update a database, and I don't need
>> to test updates from every version to every other version.
>
> You're correct (based on our experience over the past few years).
>
> The big caveat is that 99.9% of the database changes don't fall into those
> "nontrivial" categories, and dbsteward makes those 99.9% of the changes
> easy to do, reliable to reproduce, and easy to track.
>
> We've added some stuff to handle the other .1% as well, like <beforeUpdateSQL>
> and <afterUpdateSQL> where you can put an arbitrary SQL strings to be run
> before or after the remainder of the automatic stuff is done.  We probably
> haven't seen every circumstance that needs a special handling, but we've
> already struggled through a bunch.
>
> All this is part of the reason we're pushing to get this stuff open-
> sourced.  We feel like we've got something that's pretty far along, and
> we feel that community involvement will help enhance things.
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>