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