Message-ID: <4D38B023.20008@emolecules.com>
Date: Thu, 20 Jan 2011 13:58:59 -0800
From: Craig James <craig_james@emolecules.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: Postgres Backup Utility
References: 
 <036101cbb6a3$baa178e0$2fe46aa0$@servillian.ca><81976671721DF04B9DCA6ECD87941A402640812A@roundway.Cromwell-tools.co.uk><004e01cbb730$bdb21930$39164b90$@servillian.ca><81976671721DF04B9DCA6ECD87941A402640863E@roundway.Cromwell-tools.co.uk><AANLkTi=E0EjWKUuxiOyOOmemKYga9Z=5a4iJbsbKX=hE@mail.gmail.com><81976671721DF04B9DCA6ECD87941A40264B2BB5@roundway.Cromwell-tools.co.uk><AANLkTimKC442BHxR3OgMVw=30L-qwTx+vc+SoTdwF6s7@mail.gmail.com><01af01cbb8c9$58eaa0e0$0abfe2a0$@servillian.ca>
	<AANLkTi=_ckp9_rKx2jFxxkBcEinssa1U48o2SVL7-z3e@mail.gmail.com>
	<F4C27E77F7A33E4CA98C19A9DC6722A2070771BD@EXCHANGE.corp.perceptron.com>
In-Reply-To: 
 <F4C27E77F7A33E4CA98C19A9DC6722A2070771BD@EXCHANGE.corp.perceptron.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

>> On Thu, Jan 20, 2011 at 10:42 AM, Bradley Holbrook
>> <operations_bradley@servillian.ca>  wrote:
>>> Thanks Scott... a couple comments.
>>>
>>> Our developers never decide what goes to where... they just happily
>>> plumb away on the development db until we're ready to take
>> our product
>>> to testing (at regular intervals), once QA is passed, we
>> wish to apply these to live.
>>> We have several diff tools and sync tools, but they take forever
>>> (especially the ones that only go one schema at a time).
>>>
>>> The DDL Logging sounds like a sufficient solution, can it be
>>> configured to only record create and alter commands (or create or
>>> replace commands on functions or updates on sequences, etc)? I'd
>>> likely write a script to have this emailed to me at the end
>> of every
>>> day. I'm going to google DDL logging (never heard of it),
>> but any good resources off the top of your head?
>>
>> It's basically logging anything that changes the structure of
>> the database.  It would be easy enough to grep out what you
>> do and don't want later.
>>
>>> Martin French is right though, ask your developers to write
>> down all
>>> their SQL struct changes and they look at you funny... and being a
>>> developer myself I'd look at me funny. If you forget just
>> once you're
>>> screwed into a day sifting through tables and code.
>>
>> I've worked in three different shops now as a dev-dba and
>> sysadmin, and in all three, all DDL changes had to be
>> committed and / or handed over to the DBAs.  period.  Look
>> funny all they want, they either give up the DDL or their
>> code doesn't get pushed off dev servers onto anything else.
>> At the very least they should be able to tell you which
>> tables changed to go with which code changes, or you're not
>> sure what code you can and can't push.  I get both of your
>> point on this, but it's a discipline issue that needs sorting
>> out with the developers if you want to have reproduceable ddl
>> changes in all your systems that match the code changes.
>>
>
> Completely agree with Scott.

Me too. The idea that a developer can change the schema without telling anyone is laughable. The idea that someone else has to reverse engineer schema changes is ludicrous.

We have a strict procedure that every schema change has to be accompanied by a script that applies the changes. It has to be checked into Subversion along with all the other code.

Make a new rule: Developers have to document schema changes. Institute a zero-tolerance policy for omissions.  If a developer can create the DDL to make the change, surely it's not too much trouble to spend another minute adding it to a file and checking that file in.

Craig