Hi, Where can we donwload dbsteward? Best Regards, On Fri, Feb 11, 2011 at 5:16 AM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote: > In response to Glenn Maynard <glenn@xxxxxxxx>: > >> On Thu, Feb 10, 2011 at 6:44 PM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx>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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
