Top-posting is frowned upon by some (not me), but since Bill started it... I for one will be waiting to see your dbsteward. How does it compare functionally or stylistically with Ruby's migration tools (which I found to be pretty cool and frustrating all in one go).
On 02/10/2011 03:18 PM, Bill Moran wrote: > > We have this kickass solution we built at work called dbsteward that > just takes care of all of this for us, automatically. You just give > it the new version and the old version and it generates update statements > to feed into PG. > > The reason I'm bringing this up is that we're working to release > dbsteward as open source for PGCon. So, if you can wait a bit, you > can jump on that train. > > In response to Andy Colson <a...@squeakycode.net>: > >> 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 >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
