Skip site navigation (1) Skip section navigation (2)

Re: Schema version control

From: Rob Sargent <robjsargent(at)gmail(dot)com>
To: Bill Moran <wmoran(at)potentialtech(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Schema version control
Date: 2011-02-11 00:43:26
Message-ID: 4D54862E.9010204@gmail.com (view raw or flat)
Thread:
Lists: pgsql-general

On 02/10/2011 04:44 PM, Bill Moran wrote:
> In response to Rob Sargent <robjsargent(at)gmail(dot)com>:
>>
>> On 02/10/2011 03:59 PM, Bill Moran wrote:
>>> In response to Rob Sargent <robjsargent(at)gmail(dot)com>:
>>>> 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).
>>>
>>> I'm not familiar with Ruby's migration tools, so I can't say much.
>>>
>>> The overview:
>>> You store your schema and data as XML (this is easy to migrate to, because
>>> it includes a tool that makes the XML from a live database)
>>> Keep your XML schema files in some RCS.
>>> When it's time for a new deployment, you run the dbsteward tool against
>>> the schema XML and it turns it into DDL and DML.
>>> When it's time for an upgrade, you run the dbsteward tool against two
>>> schema XML files, and it calculates what has changed and generates the
>>> appropriate DDL and DML to upgrade.
>>>
>>> So ... you know, however that compares with the Ruby stuff is how it
>>> does.
>>>
>> Now at the bottom :)
>>
>> It's been a couple years since I played with Ruby ActiveRecord but it's
>> (of course) radically than what you describe.  The ddl is in the ruby
>> code and naturally the code is in RCS.  So a revision is a new instance
>> of ActiveRecord (iirc) which does the change(s) (create table ttt, alter
>> table vvv etc).  Maybe skip a rev.  Rollback to a rev is definitely
>> there because one writes the undo for each new revision.  This include
>> manipulating the data of course, so there are limitations.
> 
> dbsteward can do downgrades ... you just feed it the old schema and
> the new schema in reverse of how you'd do an upgrade ;)
> 
> Oh, also, it allows us to do installation-specific overrides.  We use
> this ONLY for DML for lookup lists where some clients have slightly
> different names for things than others.  In theory, it could do DDL
> overrides as well, but we decided on a policy of not utilizing that
> because we wanted the schemas to be consistent on all our installs.
> 
>> I personally am leary of the 'make the prod match the dev db' approach.
>> Who knows what extras lurk in the depths. I think one should be able to
>> make the dev db from scratch and write the necessary scripts to change
>> to (and from if possible) each revision. Apply to prod when tested.
> 
> dbsteward allows us to do all this.  A developer can make a change,
> rebuild a test database from their change to make sure it works, then
> test the upgrade process as well, all before even checking the code in.
> 

Good work.  Will look forward to it.

In response to

pgsql-general by date

Next:From: Glenn MaynardDate: 2011-02-11 00:44:00
Subject: Re: finding bogus UTF-8
Previous:From: Bill MoranDate: 2011-02-10 23:44:36
Subject: Re: Schema version control

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group