Andrew,

> If I were loony enough to want to make an attempt at a version updater
> (i.e. migrate a
> 7.4 database to 8.0 without an initdb), any suggestions on where to
> poke first? Does a
> catalog/list of system catalog changes exist anywhere? Any really gross
> problems immediately
> present themselves? Is dusting off pg_upgrade a good place to start, or
> is that a dead end?

Join the Slony project?    Seriously, this is one of the uses of slony.  All 
you'd need would be a script that would:

1) Install PG 8.0 to an alternate directory;
2) Start 8.0;
3) install Slony on both instances (the 7.4 and the 8.0);
4) make 7.4 the "master" and start replicating
5) when 8.0 is caught up, stop 7.4 and promote it to Master
6) turn off Slony.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Tue, 31 Aug 2004 15:16:55 -0700
Josh Berkus <josh@agliodbs.com>
Re: version upgrade
On Tue, 31 Aug 2004, Josh Berkus wrote:

> Andrew,
>
>> If I were loony enough to want to make an attempt at a version updater
>> (i.e. migrate a
>> 7.4 database to 8.0 without an initdb), any suggestions on where to
>> poke first? Does a
>> catalog/list of system catalog changes exist anywhere? Any really gross
>> problems immediately
>> present themselves? Is dusting off pg_upgrade a good place to start, or
>> is that a dead end?
>
> Join the Slony project?    Seriously, this is one of the uses of slony.  All
> you'd need would be a script that would:
>
> 1) Install PG 8.0 to an alternate directory;
> 2) Start 8.0;
> 3) install Slony on both instances (the 7.4 and the 8.0);
> 4) make 7.4 the "master" and start replicating
> 5) when 8.0 is caught up, stop 7.4 and promote it to Master
> 6) turn off Slony.

Slony is not an upgrade utility, and falls short in one big case .. 
literally .. a very large database with limited cash resources to 
duplicate it (as far as hardware is concerned).  In small shops, or those 
with 'free budget', Slony is perfect ... but if you are in an organization 
where getting money is like pulling teeth, picking up a new server "just 
to do an upgrade" can prove to be difficult ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Tue, 31 Aug 2004 19:23:47 -0300 (ADT)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: version upgrade
Marc,

> Slony is not an upgrade utility, and falls short in one big case ..
> literally .. a very large database with limited cash resources to
> duplicate it (as far as hardware is concerned).  In small shops, or those
> with 'free budget', Slony is perfect ... but if you are in an organization
> where getting money is like pulling teeth, picking up a new server "just
> to do an upgrade" can prove to be difficult ...

Huh?    You can replicate onto the same server.    Kicks your performance in 
the teeth but it works fine.   Heck, I did it on my laptop as a demo.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Tue, 31 Aug 2004 15:30:07 -0700
Josh Berkus <josh@agliodbs.com>
Re: version upgrade

On Aug 31, 2004, at 6:23 PM, Marc G. Fournier wrote:

> On Tue, 31 Aug 2004, Josh Berkus wrote:
>
>> Andrew,
>>
>>> If I were loony enough to want to make an attempt at a version 
>>> updater
>>> (i.e. migrate a
>>> 7.4 database to 8.0 without an initdb), any suggestions on where to
>>> poke first? Does a
>>> catalog/list of system catalog changes exist anywhere? Any really 
>>> gross
>>> problems immediately
>>> present themselves? Is dusting off pg_upgrade a good place to start, 
>>> or
>>> is that a dead end?
>>
>> Join the Slony project?    Seriously, this is one of the uses of 
>> slony.  All
>> you'd need would be a script that would:
>>

I thought of this quite a bit when I was working over eRServer a while 
back.

Its _better_ than a dump and restore, since you can keep the master up 
while the
'upgrade' is happening.  But Mark is right - it can be quite 
problematic from an equivalent
resource point of view. An in-place system (even a faux setup like 
pg_upgrade) would be
easier to deal with in many situations.

In the end, using a replication system OR a working pg_upgrade is still 
a pretty creaky
workaround. Having to do either tends to lob about 15 pounds of nails 
into the gears when
trying to develop a business case about upgrading (Doesn't necessarily 
stop it dead, but
does get everyone's attention...). The day when a dump/restore is not 
necessary is
the day all of us are hoping for.


>> 1) Install PG 8.0 to an alternate directory;
>> 2) Start 8.0;
>> 3) install Slony on both instances (the 7.4 and the 8.0);
>> 4) make 7.4 the "master" and start replicating
>> 5) when 8.0 is caught up, stop 7.4 and promote it to Master
>> 6) turn off Slony.
>
> Slony is not an upgrade utility, and falls short in one big case .. 
> literally .. a very large database with limited cash resources to 
> duplicate it (as far as hardware is concerned).  In small shops, or 
> those with 'free budget', Slony is perfect ... but if you are in an 
> organization where getting money is like pulling teeth, picking up a 
> new server "just to do an upgrade" can prove to be difficult ...
>
> ----
> Marc G. Fournier           Hub.Org Networking Services 
> (http://www.hub.org)
> Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 
> 7615664
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com


Tue, 31 Aug 2004 21:38:14 -0400
Andrew Rawnsley <ronz@ravensfield.com>
Re: version upgrade
On 8/31/2004 9:38 PM, Andrew Rawnsley wrote:

> On Aug 31, 2004, at 6:23 PM, Marc G. Fournier wrote:
> 
>> On Tue, 31 Aug 2004, Josh Berkus wrote:
>>
>>> Andrew,
>>>
>>>> If I were loony enough to want to make an attempt at a version 
>>>> updater
>>>> (i.e. migrate a
>>>> 7.4 database to 8.0 without an initdb), any suggestions on where to
>>>> poke first? Does a
>>>> catalog/list of system catalog changes exist anywhere? Any really 
>>>> gross
>>>> problems immediately
>>>> present themselves? Is dusting off pg_upgrade a good place to start, 
>>>> or
>>>> is that a dead end?
>>>
>>> Join the Slony project?    Seriously, this is one of the uses of 
>>> slony.  All
>>> you'd need would be a script that would:
>>>
> 
> I thought of this quite a bit when I was working over eRServer a while 
> back.
> 
> Its _better_ than a dump and restore, since you can keep the master up 
> while the
> 'upgrade' is happening.  But Mark is right - it can be quite 
> problematic from an equivalent
> resource point of view. An in-place system (even a faux setup like 
> pg_upgrade) would be
> easier to deal with in many situations.

There is something that you will not (or only under severe risk) get 
with an in-place upgrade system. The ability to downgrade back in the 
case, your QA missed a few gotchas. The application might not instantly 
eat the data, but it might start to sputter and hobble here and there.

With the Slony system, you not only switch over to the new version. But 
you keep the old system as a slave. That means that if you discover 4 
hours after the upgrade that the new version bails out with errors on a 
lot of queries from the application, you have the chance to switch back 
to the old version and have lost no single committed transaction.


Jan

> 
> In the end, using a replication system OR a working pg_upgrade is still 
> a pretty creaky
> workaround. Having to do either tends to lob about 15 pounds of nails 
> into the gears when
> trying to develop a business case about upgrading (Doesn't necessarily 
> stop it dead, but
> does get everyone's attention...). The day when a dump/restore is not 
> necessary is
> the day all of us are hoping for.
> 
> 
>>> 1) Install PG 8.0 to an alternate directory;
>>> 2) Start 8.0;
>>> 3) install Slony on both instances (the 7.4 and the 8.0);
>>> 4) make 7.4 the "master" and start replicating
>>> 5) when 8.0 is caught up, stop 7.4 and promote it to Master
>>> 6) turn off Slony.
>>
>> Slony is not an upgrade utility, and falls short in one big case .. 
>> literally .. a very large database with limited cash resources to 
>> duplicate it (as far as hardware is concerned).  In small shops, or 
>> those with 'free budget', Slony is perfect ... but if you are in an 
>> organization where getting money is like pulling teeth, picking up a 
>> new server "just to do an upgrade" can prove to be difficult ...
>>
>> ----
>> Marc G. Fournier           Hub.Org Networking Services 
>> (http://www.hub.org)
>> Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 
>> 7615664
>>
>> ---------------------------(end of 
>> broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
> --------------------
> 
> Andrew Rawnsley
> President
> The Ravensfield Digital Resource Group, Ltd.
> (740) 587-0114
> www.ravensfield.com
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Tue, 31 Aug 2004 23:35:18 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: version upgrade

On Aug 31, 2004, at 11:35 PM, Jan Wieck wrote:

> On 8/31/2004 9:38 PM, Andrew Rawnsley wrote:
>
>> On Aug 31, 2004, at 6:23 PM, Marc G. Fournier wrote:
>>> On Tue, 31 Aug 2004, Josh Berkus wrote:
>>>
>>>> Andrew,
>>>>
>>>>> If I were loony enough to want to make an attempt at a version  
>>>>> updater
>>>>> (i.e. migrate a
>>>>> 7.4 database to 8.0 without an initdb), any suggestions on where to
>>>>> poke first? Does a
>>>>> catalog/list of system catalog changes exist anywhere? Any really  
>>>>> gross
>>>>> problems immediately
>>>>> present themselves? Is dusting off pg_upgrade a good place to  
>>>>> start, or
>>>>> is that a dead end?
>>>>
>>>> Join the Slony project?    Seriously, this is one of the uses of  
>>>> slony.  All
>>>> you'd need would be a script that would:
>>>>
>> I thought of this quite a bit when I was working over eRServer a  
>> while back.
>> Its _better_ than a dump and restore, since you can keep the master  
>> up while the
>> 'upgrade' is happening.  But Mark is right - it can be quite  
>> problematic from an equivalent
>> resource point of view. An in-place system (even a faux setup like  
>> pg_upgrade) would be
>> easier to deal with in many situations.
>
> There is something that you will not (or only under severe risk) get  
> with an in-place upgrade system. The ability to downgrade back in the  
> case, your QA missed a few gotchas. The application might not  
> instantly eat the data, but it might start to sputter and hobble here  
> and there.
>
> With the Slony system, you not only switch over to the new version.  
> But you keep the old system as a slave. That means that if you  
> discover 4 hours after the upgrade that the new version bails out with  
> errors on a lot of queries from the application, you have the chance  
> to switch back to the old version and have lost no single committed  
> transaction.
>
>

What, you don't like living out on the edge? :)

Doing an upgrade via replication is a great way to do it, if you have  
the resources available to do so, no argument there.

> Jan
>
>> In the end, using a replication system OR a working pg_upgrade is  
>> still a pretty creaky
>> workaround. Having to do either tends to lob about 15 pounds of nails  
>> into the gears when
>> trying to develop a business case about upgrading (Doesn't  
>> necessarily stop it dead, but
>> does get everyone's attention...). The day when a dump/restore is not  
>> necessary is
>> the day all of us are hoping for.
>>>> 1) Install PG 8.0 to an alternate directory;
>>>> 2) Start 8.0;
>>>> 3) install Slony on both instances (the 7.4 and the 8.0);
>>>> 4) make 7.4 the "master" and start replicating
>>>> 5) when 8.0 is caught up, stop 7.4 and promote it to Master
>>>> 6) turn off Slony.
>>>
>>> Slony is not an upgrade utility, and falls short in one big case ..  
>>> literally .. a very large database with limited cash resources to  
>>> duplicate it (as far as hardware is concerned).  In small shops, or  
>>> those with 'free budget', Slony is perfect ... but if you are in an  
>>> organization where getting money is like pulling teeth, picking up a  
>>> new server "just to do an upgrade" can prove to be difficult ...

In many cases the mere idea of doing an upgrade proves to be difficult,  
before you even get to what upgrade procedure to use or whether you  
need hardware or not. Add in either of those two issues and people  
start to quiver and shake.



>>>
>>> ----
>>> Marc G. Fournier           Hub.Org Networking Services  
>>> (http://www.hub.org)
>>> Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ:  
>>> 7615664
>>>
>>> ---------------------------(end of  
>>> broadcast)---------------------------
>>> TIP 2: you can get off all lists at once with the unregister command
>>>    (send "unregister YourEmailAddressHere" to  
>>> majordomo@postgresql.org)
>>>
>> --------------------
>> Andrew Rawnsley
>> President
>> The Ravensfield Digital Resource Group, Ltd.
>> (740) 587-0114
>> www.ravensfield.com
>> ---------------------------(end of  
>> broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>
>
> --  
> #====================================================================== 
> #
> # It's easier to get forgiveness for being wrong than for being right.  
> #
> # Let's break this rule - forgive me.                                   
> #
> #================================================== JanWieck@Yahoo.com  
> #
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>
--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com


Wed, 1 Sep 2004 06:53:25 -0400
Andrew Rawnsley <ronz@ravensfield.com>
Re: version upgrade

On Aug 31, 2004, at 6:30 PM, Josh Berkus wrote:

> Huh?    You can replicate onto the same server.    Kicks your 
> performance in
> the teeth but it works fine.   Heck, I did it on my laptop as a demo.
>

Doesn't work If you have say, a 100GB db and only 50GB free space.
Not nearly enough to duplicate. But plenty of breathing room for normal 
operation.

Various db's support in place upgrades. and I'm thankful I tried 
Informix's out on a test db first because it simply scribbled over all 
the data instead of upgrading. Support told me that can happen 
sometimes.  COOL HUH?

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/


Wed, 1 Sep 2004 09:47:02 -0400
Jeff <threshar@torgo.978.org>
Re: version upgrade
Jeff wrote:
> 
> On Aug 31, 2004, at 6:30 PM, Josh Berkus wrote:
>> Huh?    You can replicate onto the same server.    Kicks your 
>> performance in
>> the teeth but it works fine.   Heck, I did it on my laptop as a demo.
> 
> Doesn't work If you have say, a 100GB db and only 50GB free space.
> Not nearly enough to duplicate. But plenty of breathing room for normal 
> operation.
> 
> Various db's support in place upgrades. and I'm thankful I tried 
> Informix's out on a test db first because it simply scribbled over all 
> the data instead of upgrading. Support told me that can happen 
> sometimes.  COOL HUH?

I think that's an incredibly important point, i.e., even if you want to 
do an "in place" upgrade, you ought to be testing it out first on a 
*full* copy of your production database. IMHO, anything less than a full 
test is playing fast-and-loose with your data. This in turn implies that 
you need enough space for a full replica anyway, so why not use slony?

Joe

Wed, 01 Sep 2004 07:29:05 -0700
Joe Conway <mail@joeconway.com>
Re: version upgrade
On 9/1/2004 10:29 AM, Joe Conway wrote:

> Jeff wrote:
>> 
>> On Aug 31, 2004, at 6:30 PM, Josh Berkus wrote:
>>> Huh?    You can replicate onto the same server.    Kicks your 
>>> performance in
>>> the teeth but it works fine.   Heck, I did it on my laptop as a demo.
>> 
>> Doesn't work If you have say, a 100GB db and only 50GB free space.
>> Not nearly enough to duplicate. But plenty of breathing room for normal 
>> operation.
>> 
>> Various db's support in place upgrades. and I'm thankful I tried 
>> Informix's out on a test db first because it simply scribbled over all 
>> the data instead of upgrading. Support told me that can happen 
>> sometimes.  COOL HUH?
> 
> I think that's an incredibly important point, i.e., even if you want to 
> do an "in place" upgrade, you ought to be testing it out first on a 
> *full* copy of your production database. IMHO, anything less than a full 
> test is playing fast-and-loose with your data. This in turn implies that 
> you need enough space for a full replica anyway, so why not use slony?

Which is another point I was about to ask. How do these people, running 
those huge and horribly important databases, ever test a single 
application change? Or any schema changes for that matter. Do they 
really type "psql -c 'alter table ...' proddb" and believe they are 
professional users because they know what they are doing?

And don't tell me "we have a backup, so we could ...". That would mean 
that you can afford the downtime in the first place.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Wed, 01 Sep 2004 10:41:42 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: version upgrade
On K, 2004-09-01 at 01:30, Josh Berkus wrote:
> Marc,
> 
> > Slony is not an upgrade utility, and falls short in one big case ..
> > literally .. a very large database with limited cash resources to
> > duplicate it (as far as hardware is concerned).  In small shops, or those
> > with 'free budget', Slony is perfect ... but if you are in an organization
> > where getting money is like pulling teeth, picking up a new server "just
> > to do an upgrade" can prove to be difficult ...
> 
> Huh?    You can replicate onto the same server.    Kicks your performance in 
> the teeth but it works fine.  

The kick could be minimized if slony did drop the pk index for the time
of initial COPY (IIRC load would be an order of magnitude faster for
LOAD+CREATE INDEX than the other way round (CREATE INDEX+LOAD))

>  Heck, I did it on my laptop as a demo.

--------------
Hannu


Wed, 01 Sep 2004 18:34:32 +0300
Hannu Krosing <hannu@tm.ee>
Re: version upgrade
On Wed, 1 Sep 2004, Jan Wieck wrote:

> On 9/1/2004 10:29 AM, Joe Conway wrote:
>
>> Jeff wrote:
>>> 
>>> On Aug 31, 2004, at 6:30 PM, Josh Berkus wrote:
>>>> Huh?    You can replicate onto the same server.    Kicks your performance 
>>>> in
>>>> the teeth but it works fine.   Heck, I did it on my laptop as a demo.
>>> 
>>> Doesn't work If you have say, a 100GB db and only 50GB free space.
>>> Not nearly enough to duplicate. But plenty of breathing room for normal 
>>> operation.
>>> 
>>> Various db's support in place upgrades. and I'm thankful I tried 
>>> Informix's out on a test db first because it simply scribbled over all the 
>>> data instead of upgrading. Support told me that can happen sometimes. 
>>> COOL HUH?
>> 
>> I think that's an incredibly important point, i.e., even if you want to do 
>> an "in place" upgrade, you ought to be testing it out first on a *full* 
>> copy of your production database. IMHO, anything less than a full test is 
>> playing fast-and-loose with your data. This in turn implies that you need 
>> enough space for a full replica anyway, so why not use slony?
>
> Which is another point I was about to ask. How do these people, running 
> those huge and horribly important databases, ever test a single 
> application change? Or any schema changes for that matter. Do they 
> really type "psql -c 'alter table ...' proddb" and believe they are 
> professional users because they know what they are doing?

You are assuming that they ever make changes ... :)

Please note that nobody is slamming Slony as *an* upgrade option ... what 
we are slamming is that everyone seems to be touting it as *the* upgrade 
option ... they seem to be ignoring the fact that nobody everyone *has* 
the resources required to use a replication (any replication) option as an 
upgrade option ...

God, how many ppl are running applications out there that have never had 
an upgrade in 5 years, because the company that first created it is no 
longer in business ... yet the application *still* does exactly what the 
business wants/needs?

Now, granted, that 5 year old application would probably break if its 
database were upgraded ... the point I'm trying to make is that the 
data format, and application, would be the static component ... the 
backend would be the only thing that changes ... *and* ... there have been 
several 'new releases' of PostgreSQL that have required *zero* changes at 
the application level in order to work, just requiring a dump/reload due 
to changes in the database ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Wed, 1 Sep 2004 12:34:44 -0300 (ADT)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: version upgrade
Folks,

> Doesn't work If you have say, a 100GB db and only 50GB free space.
> Not nearly enough to duplicate. But plenty of breathing room for normal
> operation.

 From my perspective, anyone who is running a 100GB, can't-be-down-for-a-day 
database and does not have more than 100GB free and/or a hot swap server has 
some *serious* priority problems.  I really don't think it's the job of the 
Project to make up for people's bad server room planning.   Even Microsoft, 
paragon of dumb-user-software, requires the SQL Server upgrade process to 
make a complete duplicate copy of the database.

If somebody thinks that they can code an in-place upgrade not involving 
replication and switchover, then great!    Go for it.   But the Slony method 
would be much, much easier to code and could even be done around the time of 
the 8.0 release, which I don't think you can say for anything more hard-core.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Wed, 1 Sep 2004 09:19:52 -0700
Josh Berkus <josh@agliodbs.com>
Re: version upgrade
On Wed, Sep 01, 2004 at 09:47:02AM -0400, Jeff wrote:
> 
> On Aug 31, 2004, at 6:30 PM, Josh Berkus wrote:
> 
> >Huh?  You can replicate onto the same server.  Kicks your
> >performance in the teeth but it works fine.  Heck, I did it on my
> >laptop as a demo.
> 
> Doesn't work If you have say, a 100GB db and only 50GB free space.
> Not nearly enough to duplicate.  But plenty of breathing room for
> normal operation.

There is a technical term for outfits that have uptime requirements
but can't (or won't) allocate resources for testing upgrades.  It's a
short Saxon-derived word equivalent to "known biblically."

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

Wed, 1 Sep 2004 09:38:50 -0700
David Fetter <david@fetter.org>
Re: version upgrade

On Sep 1, 2004, at 12:19 PM, Josh Berkus wrote:

> From my perspective, anyone who is running a 100GB, 
> can't-be-down-for-a-day
> database and does not have more than 100GB free and/or a hot swap 
> server has
> some *serious* priority problems.

Well, 100GB maybe excessive for this example. but I'm sure there are 
plenty of running-on-a-shoe-string shops that don't have DB x 2 space 
avail.  Then again, those places are very likely the ones who will not 
be upgrading.

but this isn't a problem specific to PG.. all db's suffer from it.. and 
slony so far seems to provide the easiest, safest path for a PG 
upgrade... in my case the problem is I do have another server with 
plenty of room, but it doesn't have much CPU or RAM and cannot handle 
the volume of live traffic the master gets.  So I have no choice but to 
plead my case and ask for some downtime.  oh well.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/


Wed, 1 Sep 2004 13:04:18 -0400
Jeff <threshar@torgo.978.org>
Re: version upgrade
Jan Wieck wrote:


> Which is another point I was about to ask. How do these people, running 
> those huge and horribly important databases, ever test a single 
> application change? Or any schema changes for that matter. Do they 
> really type "psql -c 'alter table ...' proddb" and believe they are 
> professional users because they know what they are doing?

I do alter table, but of course before to do it, I run my regression test
on a database with almost no data inside. Each stored procedure is tested
in order to execute each execution path.
In 3 years working in this way I had no a singol failure after an alter
schema operation.


Regards
Gaetano Mendola


Thu, 02 Sep 2004 03:02:00 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: version upgrade
On 9/1/2004 9:02 PM, Gaetano Mendola wrote:

> Jan Wieck wrote:
> 
> 
>> Which is another point I was about to ask. How do these people, running 
>> those huge and horribly important databases, ever test a single 
>> application change? Or any schema changes for that matter. Do they 
>> really type "psql -c 'alter table ...' proddb" and believe they are 
>> professional users because they know what they are doing?
> 
> I do alter table, but of course before to do it, I run my regression test
> on a database with almost no data inside. Each stored procedure is tested
> in order to execute each execution path.
> In 3 years working in this way I had no a singol failure after an alter
> schema operation.

If it is possible to define a representative but smaller dataset for 
test purposes, that is certainly doable. Some systems are just too 
complex to do this. SAP for example recommends a 4 stage deployment 
scenario in case you do your own application development in R/3 systems. 
You would have one or more development systems, that deliver their 
changes into test systems with small and not necessarily representative 
data. If all tests there succeed, the software is transported into the 
integration test system, which is basically a copy of the production 
system with full data. Only if that transport and the following tests 
succeed, you transport exactly the same set of programs and catalog 
changes into the production system. Otherwise you reset the integration 
test system back to be a copy of the production system.

There are a lot of possible levels between playing russian roulette with 
your data and being paranoid. If a corrupted database can cause the 
company to go under, some prefer paranoid.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Thu, 02 Sep 2004 16:16:46 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: version upgrade
Jan Wieck wrote:
 > On 9/1/2004 9:02 PM, Gaetano Mendola wrote:
 >
 >> Jan Wieck wrote:
 >>
 >>
 >>> Which is another point I was about to ask. How do these people,
 >>> running those huge and horribly important databases, ever test a
 >>> single application change? Or any schema changes for that matter. Do
 >>> they really type "psql -c 'alter table ...' proddb" and believe they
 >>> are professional users because they know what they are doing?
 >>
 >>
 >> I do alter table, but of course before to do it, I run my regression test
 >> on a database with almost no data inside. Each stored procedure is tested
 >> in order to execute each execution path.
 >> In 3 years working in this way I had no a singol failure after an alter
 >> schema operation.
 >
 >
 > If it is possible to define a representative but smaller dataset for
 > test purposes, that is certainly doable. Some systems are just too
 > complex to do this. SAP for example recommends a 4 stage deployment
 > scenario in case you do your own application development in R/3 systems.
 > You would have one or more development systems, that deliver their
 > changes into test systems with small and not necessarily representative
 > data. If all tests there succeed, the software is transported into the
 > integration test system, which is basically a copy of the production
 > system with full data. Only if that transport and the following tests
 > succeed, you transport exactly the same set of programs and catalog
 > changes into the production system. Otherwise you reset the integration
 > test system back to be a copy of the production system.
 >
 > There are a lot of possible levels between playing russian roulette with
 > your data and being paranoid. If a corrupted database can cause the
 > company to go under, some prefer paranoid.

Paranoid means also don't trust in new hw without have test it for a while.
How ever if I leave unchanged all interfaces and all my test cases are
continuously passing ( 3200 different test in my case ) all the night long
  I'm quite sure that the schema change will not hurt nothing. However I have to
say that add come column, with default value and a check on it is no to
doable with very bigtables. Fortunately with the 8.0 you can do these tasks
in one shot.

Regards
Gaetano Mendola





Thu, 02 Sep 2004 22:47:16 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: version upgrade
>   I'm quite sure that the schema change will not hurt nothing. However I have to
> say that add come column, with default value and a check on it is no to
> doable with very bigtables. Fortunately with the 8.0 you can do these tasks
> in one shot.

I've got a few 30GB tables anxiously awaiting that single alter command
that will alter in all the stuff that should have been added a long time
ago.



Fri, 03 Sep 2004 01:29:22 -0400
Rod Taylor <pg@rbt.ca>
Re: version upgrade