Re: Schema version control - Mailing list pgsql-general
From | Andy Colson |
---|---|
Subject | Re: Schema version control |
Date | |
Msg-id | 4D546427.6070607@squeakycode.net Whole thread Raw |
In response to | Re: Schema version control (Andy Colson <andy@squeakycode.net>) |
List | pgsql-general |
On 2/10/2011 4:14 PM, Andy Colson wrote: > On 2/10/2011 3:38 PM, Royce Ausburn wrote: >> Hi all, >> >> My company is having trouble managing how we upgrade schema changes >> across many versions of our software. I imagine this is a common >> problem and there're probably some neat solutions that we don't know >> about. >> >> For the last 10 years we have been writing bash shell scripts >> essentially numbered in order db0001, db0002, db0003.... The number >> represents the schema version which is recorded in the database and >> updated by the shell scripts. We have a template that provides all the >> functionality we need, we just copy the script and fill in the blanks. >> The schema upgrade scripts are committed to svn along with the >> software changes, and we have a process when installing the software >> at a site that runs the scripts on the DB in order before starting up >> the new version of the software. >> >> This has worked really well so far. But we've effectively only had one >> version of the software in development at any time. We're now in the >> habit of branching the software to form releases to promote stability >> when making large changes. The idea is that only really important >> changes are merged in to the releases. This introduces a bit of a >> problem when some change needs to be merged from one release to >> another. The typical problem is that we might have two versions of the >> software 10.0 at schema version 10057 and 11.0 at 11023 and we need to >> merge an important bug fix from schema 11023 in to 10.0. The issue is >> that 11023 might depend upon changes introduced in the schema versions >> before it. Or 11023 might introduce changes that cause later scripts >> to break (11000 - 11023) when upgrading from 10.0 to 11.0. >> >> One potential solution is to require that schema changes are never >> merged in to a release, but of course sometimes business requires we >> do =( >> >> I'm really interested to hear how you guys manage schema upgrades in >> the face of branches and upgrading from many different versions of the >> database. >> >> I've been reading >> http://pyrseas.wordpress.com/2011/02/07/version-control-part-2-sql-databases/ >> but I have a feeling that this blog post won't address branches. >> >> Cheers! >> >> --Royce >> >> > > So, 10.0 at 10057. > 11.0 at 11023. > > then 10.1 needs some fixes so db is bumped to 10058. > > Then, later, you can upgrade 10057 to 11023, but you cant get 10058 to > 11023. > > Humm... maybe you need smarter upgrade scripts? Would having logic in > the script help? Something like: > > if not fieldExists('xyz) then alter table ... add xyz ... > > > > Or, maybe your schema numbering system is to broad? Maybe each table > could have a version number? > > > Or some kinda flags like: > create table dbver(key text); > > then an update would be named: "add xyz to bob". > > then the update code: > > q = select key from dbver where key = 'add xyz to bob'; > if q.eof then > alter table bob add xyz > > > -Andy > D'oh! a bug in my update script: q = select key from dbver where key = 'add xyz to bob'; if q.eof then alter table bob add xyz insert into dbver('add xyz to bob'); How embarrassing :-) -Andy
pgsql-general by date: