Message-ID: <480DC668.8080901@reedyriver.com>
Date: Tue, 22 Apr 2008 07:05:12 -0400
From: "H. Hall" <hhall1001@reedyriver.com>
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
To: postgresql-admin <pgsql-admin@postgresql.org>
Subject: Re: Dev Process
References: <8078a1730804181152m199faeb5j47efa7c72a9571c7@mail.gmail.com>
In-Reply-To: <8078a1730804181152m199faeb5j47efa7c72a9571c7@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Medi Montaseri wrote:
> Hi,
>
> I was wondering if you could critique the following DB application 
> development process.
>
> Given a production DB and a Dev DB:
> 1- Develope schema and code on Dev including test data which will 
> impact sequences, etc
> 2- Dump the schema only (not the data) from Dev and restore it into 
> the production DB
> 3- Populate DB with real data, usage begins
> 4- Enhancement to schema on Dev DB including unit testing again with 
> impact on sequences
> 5- Dump schema and move to production
> 6- Goto 4
>
> Thanks
> Medi
>
>
The first problem is that you will have to take the production DB down 
to update it. Another problem with this method is that if you encounter 
problems while restoring data, then the down time could extend farther 
than your plan. Problems reloading the data can arise from new 
constraints etc. Finally,  you should consider testing the application 
program(s) as part of your update process.

A better plan might be to:

- Use only script files to update the dev server and use the same script 
files to update the test server (described below) and the production server.

- Keep a log of changes to the dev and production servers. This shows 
when the script files were run on each server and gives you a quick view 
of the state of each.

- Use a third server, a test server to test the update prior to updating 
the production server. The test server schema should be at the same 
state as the production server, prior to the update. Update the test 
server using the script files and test your applications against it. 
Then update the db server.

The log let's you see what needs to be done. Use of the script files 
gives you the repeatability you need. Updating the test server first 
gives you the validation you need without the heart attacks.

The test server can be very light, i.e. anything that will run postgres.

--cheers
HH


H. Hall
ReedyRiver Group LLC
site: reedyriver.com