Re: Schema version control
От | Bill Moran |
---|---|
Тема | Re: Schema version control |
Дата | |
Msg-id | 20110211001620.162eeffc.wmoran@potentialtech.com обсуждение исходный текст |
Ответ на | Re: Schema version control (Glenn Maynard <glenn@zewt.org>) |
Ответы |
Re: Schema version control
(Glenn Maynard <glenn@zewt.org>)
Re: Schema version control (Andre Lopes <lopes80andre@gmail.com>) |
Список | pgsql-general |
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/
В списке pgsql-general по дате отправления: