Re: Schema version control - Mailing list pgsql-general

On 02/10/2011 02: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
thisis 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.
Wehave a template that provides all the functionality we need, we just copy the script and fill in the blanks.  The
schemaupgrade scripts are committed to svn along with the software changes, and we have a process when installing the
softwareat 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
anytime.  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
problemwhen some change needs to be merged from one release to another.  The typical problem is that we might have two
versionsof the software 10.0 at schema version 10057 and 11.0 at 11023 and we need to merge an important bug fix from
schema11023 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
businessrequires we do =( 
>
> I'm really interested to hear how you guys manage schema upgrades in the face of branches and upgrading from many
differentversions 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
thatthis blog post won't address branches. 
>
> Cheers!
>
> --Royce
>
>

Don't the bash scripts get checked in to .../perforce/cvs/svn/git/...?
Aren't they part of the resources of the project(s)?