Re: Schema version control

On Thu, Feb 10, 2011 at 02:58:15PM -0700, Rob Sargent wrote:
> On 02/10/2011 02:38 PM, Royce Ausburn wrote:
> > 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.
>
> Don't the bash scripts get checked in to .../perforce/cvs/svn/git/...?
> Aren't they part of the resources of the project(s)?

I was thinking about this a little more. With the new CREATE
EXTENSION functionality in Postgres, we have the infrastructure to
run various SQL scripts to migrate between versioned states.
Obviously the extension code relates to extensions such as
datatypes. I was wondering if this is sufficiently generic
that it could be used to migrate between different versions of
a schema?

This wouldn't be using the EXTENSION functionality, just the
ability to run the scripts. This would enable easy upgrades
(and downgrades, branching etc.) between different schema
versions, providing that the appropriate scripts were installed.
If this were optionally also accessible via an SQL syntax such
as an analogue of CREATE and/or ALTER EXTENSION, it would
provide a reliable and standardised method for installing and
upgrading a schema, which would potentially prevent a great
deal of wheel-reinvention between software packages.

Regards,
Roger

--
.''`. Roger Leigh
: :' : Debian GNU/Linux http://people.debian.org/~rleigh/
`. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/
`- GPG Public Key: 0x25BFB848 Please GPG sign your mail.