Re: Schema version control

From: Andy Colson <andy(at)squeakycode(dot)net>
To: Royce Ausburn <royce(dot)ml(at)inomial(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Schema version control
Date: 2011-02-10 22:18:15
Message-ID: 4D546427.6070607@squeakycode.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2011-02-10 22:18:55 Re: Schema version control
Previous Message dennis jenkins 2011-02-10 22:18:03 Re: finding bogus UTF-8