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

Re: best practice in upgrading db structure

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: pgsql-general(at)postgresql(dot)org
Cc: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>, SunWuKung <Balazs(dot)Klein(at)axelero(dot)hu>
Subject: Re: best practice in upgrading db structure
Date: 2006-03-29 02:40:22
Message-ID: 200603282140.22975.xzilla@users.sourceforge.net (view raw or flat)
Thread:
Lists: pgsql-general
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(dot)GC75181(at)pervasive(dot)com>, jnasby(at)pervasive(dot)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

In response to

Responses

pgsql-general by date

Next:From: Luki RustiantoDate: 2006-03-29 02:53:31
Subject: Guidelines for upgrading from pgsql7.4.xxx server to pgsql8.xxx server
Previous:From: Scott MarloweDate: 2006-03-29 01:26:07
Subject: Re: PostgreSQL client api

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