Thread: best practice in upgrading db structure

best practice in upgrading db structure

From
SunWuKung
Date:
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?

Thanks for the help.
Balázs

Re: best practice in upgrading db structure

From
brew@theMode.com
Date:
SunWuKing.....

> 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?

Put the new database on a development machine.

Do a dump of the old database and ftp it to the new machine.  Hopefully
you don't have terrabytes of data.....

Play around with the development machine, learning all the ins and outs,
the good, bad, and the ugly, about the new database, etc.  Meanwhile, your
production server is still running untouched and doing it's job.

Maybe you even have to prepare by writing a script to quickly inport the
old data into the new machine.....

Once you figure out what your plan is, you stop updating your old server,
then do a final dump and inport it into the development server, then you
make that your development server the live server, by changing ip, or
changing the DNS, or something, depends on how you are reaching it now and
where from.

But the main thing is do plenty of experimentation before hand, while the
old server is still online and functioning.  Make your mistakes when
nobody is watching!!!!

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
  Check out my Stock Option Covered Call website  http://www.callpix.com
     and my Musician's Online Database Exchange http://www.TheMode.com
 ==========================================================================


Re: best practice in upgrading db structure

From
"Jim C. Nasby"
Date:
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 ...
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: best practice in upgrading db structure

From
SunWuKung
Date:
In article <20060328221109.GC75181@pervasive.com>, jnasby@pervasive.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?



Re: best practice in upgrading db structure

From
"Jim C. Nasby"
Date:
On Wed, Mar 29, 2006 at 12:24:04AM +0200, SunWuKung wrote:
> In article <20060328221109.GC75181@pervasive.com>, jnasby@pervasive.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?

Depends on how much data you need to modify. For small tables, I stick
with ALTER TABLE because it's a lot cleaner/easier. For larger tables,
you might want to CREATE TABLE AS SELECT ..., or maybe copy out and copy
back in.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: best practice in upgrading db structure

From
Scott Marlowe
Date:
On Tue, 2006-03-28 at 16:24, SunWuKung wrote:
> In article <20060328221109.GC75181@pervasive.com>, jnasby@pervasive.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.

Re: best practice in upgrading db structure

From
Robert Treat
Date:
On Tuesday 28 March 2006 17:31, Jim C. Nasby wrote:
> On Wed, Mar 29, 2006 at 12:24:04AM +0200, SunWuKung wrote:
> > In article <20060328221109.GC75181@pervasive.com>, jnasby@pervasive.com
> > > 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?
>
> Depends on how much data you need to modify. For small tables, I stick
> with ALTER TABLE because it's a lot cleaner/easier. For larger tables,
> you might want to CREATE TABLE AS SELECT ..., or maybe copy out and copy
> back in.

This seems backwards to me. On larger tables I tend to favor alter table for
adding/dropping columns since the table doesn't need to be rewritten, and on
smaller tables I'd be more likely to use CTAS (although even then still
pretty unlikely)

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Re: best practice in upgrading db structure

From
"Ivan Zolotukhin"
Date:
Hello,

> 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?
>
> What do you usually do in a situation like this?

That's a big problem for us too. We develop web application in "always
beta" regime, having very short one-week release cycle. It is
practically impossible to foresee all business requirements that
affect database structure, so almost every release we need to change
it on the live server. However, it's critically important to plan the
DB structure properly anyway, so that you will change it only slightly
in future.

Some time ago I did not manage to find information on how to update
both application and database without any interruption of the clients.
I can easily update my application without any losses within a
transaction (`mv` command), I can easily do the same with PostgreSQL,
but how to *coordinate* both changes? What if my working application
introduces some logical inconsistency when I updated DB structure but
not yet updated application?

So we could not find a correct solution. IMHO, the only good practices
are to test transition intensively in development enviroinment to
smooth it, minimize database changes and minimize release duration.

Now every week we write release plans trying to think it over in
details, then stop application showing some fun message to users and
do quickly what we planned in advance. It takes 2-3 minutes to
complete everything, but I would like to avoid it...

Sincerely,
Ivan Zolotukhin

Re: best practice in upgrading db structure

From
Csaba Nagy
Date:
> 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?

Our development infrastructure includes a development data base cluster,
with one data base per developer, and a staging data base with a largish
deployment of the current production data base version.

The developers are free to test whatever data base modifications they
need on their private development data base. We have a "setup_db"
script, which creates the data base structure + initial data. The script
is based on an XML file which is processed by XSLT to generate the
actual schema for Oracle/Postgres. So the developers usually recreate
the DB from scratch on any modification of the db schema. We test both
on postgres/oracle, the application must support both (it can even
connect to both in parallel).

When a release is approaching, we make a diff of the currently deployed
production schema and the to be deployed new release schema. The
differences go to a db migration script (separately for
Oracle/Postgres), broken in a few steps:
  - init: create new DB structures (create new tables, add new db fields
to existing tables, insert new data, etc.). After this step the DB can
be used both by the old version and the new one of the application.
After this step we restart the application with the new version deployed
(actually we have usually another cluster of app-servers connecting to
the same data base, running the new version);
  - cleanup: delete old structures, add the new constraints which could
not be added because compatibility problems with the old version;

All can be done using alter table and the like. When some field changes
it's structure/meaning, we actually create a new field, and copy over
the old one with the needed conversions. The new version will use the
new field, old version the old field... we do make all possible to be
able to run both versions after the init step. We actually have a middle
step, because we have our systems (logically) partitioned in
sub-systems, and we usually migrate them separately, so we have a
"migrate sub-system" step too. So we actually can have 2 versions of the
software connecting in parallel to the DB, each having a different set
of sub-systems to run.

After the migration scripts are created, we test them on the staging
system (our QA does this, and they are more than happy to discover any
mistakes in the process ;-)

We also have a quite extensive unit/integration test suite which catches
quite a lot of potential regression cases, this makes any change easier
to prove not breaking existing functionality, and in particular data
base changes are also easier to add with enough confidence it will work.

HTH,
Csaba.



Re: best practice in upgrading db structure

From
Jim Nasby
Date:
On Mar 28, 2006, at 8:40 PM, Robert Treat wrote:
>> Depends on how much data you need to modify. For small tables, I
>> stick
>> with ALTER TABLE because it's a lot cleaner/easier. For larger
>> tables,
>> you might want to CREATE TABLE AS SELECT ..., or maybe copy out
>> and copy
>> back in.
>
> This seems backwards to me. On larger tables I tend to favor alter
> table for
> adding/dropping columns since the table doesn't need to be
> rewritten, and on
> smaller tables I'd be more likely to use CTAS (although even then
> still
> pretty unlikely)

It depends on what exactly you're doing. For example, if you're
adding a new field and have to populate it with data, you end up
rewriting the entire table, but in a way that leads to considerable
bloat. Of course if you can get away without re-writing the entire
table you absolutely want to go that route.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



Re: best practice in upgrading db structure

From
Jim Nasby
Date:
On Mar 29, 2006, at 3:25 AM, Csaba Nagy wrote:

>> 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?
>
> Our development infrastructure includes a development data base
> cluster,
> with one data base per developer, and a staging data base with a
> largish
> deployment of the current production data base version.
>
> The developers are free to test whatever data base modifications they
> need on their private development data base. We have a "setup_db"
> script, which creates the data base structure + initial data. The
> script
> is based on an XML file which is processed by XSLT to generate the
> actual schema for Oracle/Postgres. So the developers usually recreate

Have you considered releasing that creation code? I know there's lots
of places that have a need for stuff like this, and having used a
similar system before I know how powerful it can be. Unfortunately
the company I worked for was too paranoid to release the database
creation tool we used. :( Had they, maybe you wouldn't have had to
write one from scratch.
--
Jim C. Nasby, Database Architect                decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"



Re: best practice in upgrading db structure

From
Csaba Nagy
Date:
[leaving the original text, as it is reply to an older posting]

On Fri, 2006-03-31 at 21:26, Jim Nasby wrote:
> On Mar 29, 2006, at 3:25 AM, Csaba Nagy wrote:
>
> >> 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?
> >
> > Our development infrastructure includes a development data base
> > cluster,
> > with one data base per developer, and a staging data base with a
> > largish
> > deployment of the current production data base version.
> >
> > The developers are free to test whatever data base modifications they
> > need on their private development data base. We have a "setup_db"
> > script, which creates the data base structure + initial data. The
> > script
> > is based on an XML file which is processed by XSLT to generate the
> > actual schema for Oracle/Postgres. So the developers usually recreate
>
> Have you considered releasing that creation code? I know there's lots
> of places that have a need for stuff like this, and having used a
> similar system before I know how powerful it can be. Unfortunately
> the company I worked for was too paranoid to release the database
> creation tool we used. :( Had they, maybe you wouldn't have had to
> write one from scratch.

I guess I still would have written it from scratch :-)

There ARE some systems which do data mapping from/to code, but they all
have problems: too generic, never exactly what you want, etc. What I did
is: create a custom XML schema to describe the features I need from the
DB, and a pair of XSL style sheets to translate that for
postgres/oracle. I guess it would have been better to take another
approach, i.e. write some templates (using some templating engine) and
feed the data from the XML into that, but the result would be the same,
if maybe a bit more readable...

The conclusion is: if I would have to set up another system doing
something similar, I would write it from scratch again. It really didn't
take that much time, and it does exactly what I want.

The point of the post was that it's good to have such a system, where
the main features are stored in some common format (XML is just an
example), which can be version controlled, and the DB specific parts
partly generated, partly stored separately and version controlled too.

Regarding releasing what I have, it's not much use, cause it's too
specific for what we have here, and ugly too. But it works.

Cheers,
Csaba.





Re: best practice in upgrading db structure

From
Kenneth Downs
Date:
Csaba Nagy wrote:
[leaving the original text, as it is reply to an older posting]

On Fri, 2006-03-31 at 21:26, Jim Nasby wrote: 
On Mar 29, 2006, at 3:25 AM, Csaba Nagy wrote:
   
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?       
Our development infrastructure includes a development data base  
cluster,
with one data base per developer, and a staging data base with a  
largish
deployment of the current production data base version.

The developers are free to test whatever data base modifications they
need on their private development data base. We have a "setup_db"
script, which creates the data base structure + initial data. The  
script
is based on an XML file which is processed by XSLT to generate the
actual schema for Oracle/Postgres. So the developers usually recreate     
Have you considered releasing that creation code? I know there's lots  
of places that have a need for stuff like this, and having used a  
similar system before I know how powerful it can be. Unfortunately  
the company I worked for was too paranoid to release the database  
creation tool we used. :( Had they, maybe you wouldn't have had to  
write one from scratch.   
I guess I still would have written it from scratch :-)

There ARE some systems which do data mapping from/to code, but they all
have problems: too generic, never exactly what you want, etc. What I did
is: create a custom XML schema to describe the features I need from the
DB, and a pair of XSL style sheets to translate that for
postgres/oracle. I guess it would have been better to take another
approach, i.e. write some templates (using some templating engine) and
feed the data from the XML into that, but the result would be the same,
if maybe a bit more readable...

The conclusion is: if I would have to set up another system doing
something similar, I would write it from scratch again. It really didn't
take that much time, and it does exactly what I want. 

Csaba, we made such a system the cornerstone of our entire toolchain. 

I will ask, though, why use XML/XSL, why not use a format that lets you load the data to tables, then you do a huge number of tricks with it prior to generating the DDL, not the least of which is diff'ing current structure to see what needs to be changed.

Anyway, our system uses a format that looks something like CSS, it is documented here:

http://docs.secdat.com/index.php?gppn=Database+Specifications


Attachment

Re: best practice in upgrading db structure

From
Csaba Nagy
Date:
> I will ask, though, why use XML/XSL, why not use a format that lets
> you load the data to tables, then you do a huge number of tricks with
> it prior to generating the DDL, not the least of which is diff'ing
> current structure to see what needs to be changed.
>

Well, XML/XSLT is indeed not the best for this purpose, but I didn't
know that when I first implemented it :-)

Next time I'll use something else.

> Anyway, our system uses a format that looks something like CSS, it is
> documented here:
>
> http://docs.secdat.com/index.php?gppn=Database+Specifications
>

I agree that the CSS-like style is more readable than XML, but the other
DB maintainers here had expressed their desire to have it SQL-like :-)

I have no problems with XML either, but not all are happy with it...

Cheers,
Csaba.