Re: best practice in upgrading db structure

On Tue, 2006-03-28 at 16:24, SunWuKung wrote:
> In article <20060328221109(dot)GC75181(at)pervasive(dot)com>, jnasby(at)pervasive(dot)com
> says...
> > On Tue, Mar 28, 2006 at 09:28:12PM +0200, SunWuKung wrote:
> > > This is going to be an amateur question...
> > >
> > > Could somebody explain me, or point me to a resource where I can find
> > > out what is the recommended practice when a live db needs to be replaced
> > > with a new version of it that has a slightly different structure?
> > >
> > > My first guess would be to create the empty new version of the db and
> > > insert the data of the old one into it - adding and modifying it when
> > > necessary, but I am not sure.
> > >
> > > What do you usually do in a situation like this?
> >
> > ALTER TABLE ...
> >
>
> I know this is easy to do when the db is empty, but do you still suggest
> this when there is data in the db : changing the structure of the old
> version of the db to match the new one (obviously not on the live
> server) and than try to modify the data in it to fit the structure eg.
> there are two new columns that needs data?

You should be able to do this, even in production, with no
interruptions.

Note that if you need to do it in a transaction so you can roll it back,
then the table will be locked during the changes.

But you should be able to add columns with no real loss of funtionality.

IF you need the fields to be NOT NULL, then add them first, populate
them, then add the not null constraint. Unlike many other databases
cough *mysql* cough, adding a field doesn't require the entire table to
be re-written and locked the whole time.