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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
