Re: [HACKERS] 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 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: [EMAIL PROTECTED] 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 [EMAIL PROTECTED]) 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. # #== [EMAIL PROTECTED] # ---(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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 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 [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 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: [EMAIL PROTECTED] 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 [EMAIL PROTECTED])
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] 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 [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] 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 [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] version upgrade
Date: Tue, 31 Aug 2004 23:35:18 -0400 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. Just asking: how far back in time Slony can downgrade or keep the older servers in slavery? 6.5? I haven't tried it yet, hence, the question. -- Serguei A. Mokhov| /~\The ASCII Computer Science Department | \ / Ribbon Campaign Concordia University | XAgainst HTML Montreal, Quebec, Canada | / \ Email! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] version upgrade
On 9/1/2004 1:51 PM, Serguei A. Mokhov wrote: Date: Tue, 31 Aug 2004 23:35:18 -0400 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. Just asking: how far back in time Slony can downgrade or keep the older servers in slavery? 6.5? I haven't tried it yet, hence, the question. Slony runs on 7.3.3 and better. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] version upgrade
On Wed, 2004-09-01 at 13:50, Jan Wieck wrote: On 9/1/2004 1:51 PM, Serguei A. Mokhov wrote: Date: Tue, 31 Aug 2004 23:35:18 -0400 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. Just asking: how far back in time Slony can downgrade or keep the older servers in slavery? 6.5? I haven't tried it yet, hence, the question. Slony runs on 7.3.3 and better. If you're willing to put some time into removing schema references, Slony will function on 7.2 -- but it's not pretty. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] version upgrade
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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: [EMAIL PROTECTED] 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 [EMAIL PROTECTED])
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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: [EMAIL PROTECTED] 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 [EMAIL PROTECTED]) 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
Re: [HACKERS] 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: [EMAIL PROTECTED] 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 [EMAIL PROTECTED]) 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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] version upgrade
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? There any chance of getting something to work at all? (I figure I made enough of a stink about it last year about this time, I should at least make the attempt...or have one of my minions do it...) Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] version upgrade
Andrew Rawnsley [EMAIL PROTECTED] writes: 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? pg_upgrade is the way to go IMHO. I would not try to dust off the old shell-script code for it, but start from scratch using the fundamental ideas. To wit, migrate the schema using pg_dump and reload, then push the existing data files into place. There are enough low-level details involved that it's better to do this in C than shell code, but the concept is sound. This will not work for the 7.4-8.0 transition in particular, because the data file format changed (heap tuple header changes). But those sorts of changes are relatively rare and will probably get rarer (especially if there's any reason for us to try to avoid 'em). If you start now you might have a credible implementation in time for 8.0-8.1 ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
