Thread: Pains in upgrading to 8.3
I'm glad I didn't go from 8.2.3 to 8.3 straight! http://ogasawalrus.com/blog/node/462
On Fri, Feb 15, 2008 at 10:21:16PM +0800, Phoenix Kiula wrote: > > http://ogasawalrus.com/blog/node/462 "Reading more carefully" sounds like it was the first read to me.
On Fri, Feb 15, 2008 at 8:21 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: > I'm glad I didn't go from 8.2.3 to 8.3 straight! ither way, you need to update to 8.2.6
Phoenix Kiula wrote: > I'm glad I didn't go from 8.2.3 to 8.3 straight! > > http://ogasawalrus.com/blog/node/462 > > Going from 8.2.4 and 8.2.6 to 8.3.0 has been painless for me. However, unlike the blogger you cite, I read the directions before, not after, attempting it. Paul
paul rivers wrote: >> > Going from 8.2.4 and 8.2.6 to 8.3.0 has been painless for me. > However, unlike the blogger you cite, I read the directions before, > not after, attempting it. The blogger has a point about pg_dump and restore, it could be much better, for example the backup process could be part of the server core and instead of having a fat client where most of the process is running on the client, a API could be used where the backup is generated on the server and then have options where it could be left on the server or transferred to the clients PC. Using pg_dump remotely is becoming a pain because it's not really backwards compatible with earlier releases, so you end up having to have multiple copies laying around to use on different server versions. While Firebird is mostly inferior, it's backup system is much nicer that PostgreSQL's system. Firebird uses a backup API, so if you backup remotely there is no fat client needed and it eliminates all the dependency issues on the client side. The client access library implements the API and that's it. You of course could hack something similar on PGSQL by using SSH and remotely executing pg_dump on the server, but that does not really help on windows servers where SSH is not a common thing. The backup data is coming back to the client regardless, so why not just return it as a result set? Just my opinion on the matter, no flames please. Thanks, Tony
Tony Caduto wrote: > paul rivers wrote: >>> >> Going from 8.2.4 and 8.2.6 to 8.3.0 has been painless for me. >> However, unlike the blogger you cite, I read the directions before, >> not after, attempting it. > > The blogger has a point about pg_dump and restore, it could be much > better, for example > the backup process could be part of the server core and instead of > having a fat client where most of the process is running on the > client, a API could be > used where the backup is generated on the server and then have options > where it could be left on the server or transferred to the clients PC. > > Using pg_dump remotely is becoming a pain because it's not really > backwards compatible with earlier releases, so you end up having to > have multiple copies laying around to use on different server versions. > > While Firebird is mostly inferior, it's backup system is much nicer > that PostgreSQL's system. Firebird uses a backup API, so if you > backup remotely there is no fat client needed and it eliminates all > the dependency issues on the client side. The client access library > implements the API and that's it. > You of course could hack something similar on PGSQL by using SSH and > remotely executing pg_dump on the server, but that does not really > help on windows servers where SSH is not a common thing. > > The backup data is coming back to the client regardless, so why not > just return it as a result set? > > Just my opinion on the matter, no flames please. > I agree with you 100% it would be nice if this weren't necessary, so no flames intended! It's just if the blogger is going to use a software package, it's in his/her best interests to rtfm. It's no good to write, say, a lot of tricky SQL that depends on transactional control and properties of certain isolation levels, and then be surprised when in MySQL I get odd results, especially when my tables span storage engine types. If I did that, I would blame myself, not MySQL, even if I also thought MySQL should reconsider the behavior. MySQL did warn me after all, in the docs. I do agree it would be nice to change this aspect, and no, I've no clue how hard it would be. As a model of ease and flexibility, Microsoft's SQL Server is very good in this respect, probably the easiest I've ever worked with (at least from v2000 -> v2005, prior version upgrades were a little rockier). Hot backups of full databases via T-SQL commands, in-place upgrades that convert page structures as necessary, turn archive log mode on/off dynamically, differential vs incremental backups, backups by tablespace, etc. All in all, they got that part of their engine mostly right, excepting from problems in 2000 with relocating master database files (and got a nice head-start that direction from Sybase). Paul
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> paul rivers wrote:
>> However, unlike the blogger you cite, I read the directions before,
>> not after, attempting it.
> The blogger has a point about pg_dump and restore,
Does he? He claims it didn't work, but there's no details about what
went wrong. He also seems entirely misinformed on the difference
between "portable" and "PG-specific" pg_dump output.
regards, tom lane
On Fri, Feb 15, 2008 at 4:21 PM, Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote: > paul rivers wrote: > >> > > Going from 8.2.4 and 8.2.6 to 8.3.0 has been painless for me. > > However, unlike the blogger you cite, I read the directions before, > > not after, attempting it. > > > The blogger has a point about pg_dump and restore, it could be much > better, for example > the backup process could be part of the server core and instead of > having a fat client where most of the process is running on the client, > a API could be > used where the backup is generated on the server and then have options > where it could be left on the server or transferred to the clients PC. Not really an option - the reason it's recommended to use the new pg_dump version with the older server when upgrading is to allow the dump to be made in the way most compatible with the new server, effectively doing some of the upgrade process as part of the dump operation. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Oracle-compatible database company
Dave Page wrote: > On Fri, Feb 15, 2008 at 4:21 PM, Tony Caduto > <tony_caduto@amsoftwaredesign.com> wrote: >> paul rivers wrote: >> >> >> > Going from 8.2.4 and 8.2.6 to 8.3.0 has been painless for me. >> > However, unlike the blogger you cite, I read the directions before, >> > not after, attempting it. >> >> >> The blogger has a point about pg_dump and restore, it could be much >> better, for example >> the backup process could be part of the server core and instead of >> having a fat client where most of the process is running on the client, >> a API could be >> used where the backup is generated on the server and then have options >> where it could be left on the server or transferred to the clients PC. > > Not really an option - the reason it's recommended to use the new > pg_dump version with the older server when upgrading is to allow the > dump to be made in the way most compatible with the new server, > effectively doing some of the upgrade process as part of the dump > operation. For the case of upgrading, it wouldn't work. But there are certainly other cases where it would help. Say from your central pgadmin console administering 10 servers from 3 different major release trees :-( It can be done with commandline pg_dump, but it means you have to have three different installs on your management or backup or whatever machine. Those cases would certainly be easier if you could just call a backup API on the server that would feed you the data... (yes, there are ways to do it with ssh tunneling and whatever, but that's yet another external service that has to be set up and configured) I'm not saying it's worth the work and potential downsides, just that there are clear upsides :-) //Magnus
On Fri, Feb 15, 2008 at 8:21 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote: > I'm glad I didn't go from 8.2.3 to 8.3 straight! > > http://ogasawalrus.com/blog/node/462 If only he were on debian or ubuntu, he could run pg_upgradecluster and he'd have been done.
On Fri, 15 Feb 2008, Tom Lane wrote:
> He claims it didn't work, but there's no details about what went wrong.
> He also seems entirely misinformed on the difference between "portable"
> and "PG-specific" pg_dump output.
I just left a note on this and related subjects on the blog. If you
search for "postgresql upgrade 8.3" on Google that comes back as hit #5
already and it would be good to shut down some of the misunderstandings
there ("PostgreSQL doesn't recreate the databases during the restore
process"?) before they get any more publicity.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Magnus Hagander wrote: > > For the case of upgrading, it wouldn't work. But there are certainly > other cases where it would help. Say from your central pgadmin console > administering 10 servers from 3 different major release trees :-( > > It can be done with commandline pg_dump, but it means you have to have > three different installs on your management or backup or whatever > machine. Those cases would certainly be easier if you could just call > a backup API on the server that would feed you the data... (yes, there > are ways to do it with ssh tunneling and whatever, but that's yet > another external service that has to be set up and configured) > > I'm not saying it's worth the work and potential downsides, just that > there are clear upsides :-) > Exactly, I didn't necessarily mean the blogger had a point about upgrades in general, just that pg_dump had room for improvement. Hey maybe a backup API is something for the Google Summer of Code thing, it would be really nice to have, and make general backups much easier from a admin point of view. Later, Tony
Magnus Hagander wrote: > Dave Page wrote: > > On Fri, Feb 15, 2008 at 4:21 PM, Tony Caduto > > <tony_caduto@amsoftwaredesign.com> wrote: > >> paul rivers wrote: > >> >> > >> > Going from 8.2.4 and 8.2.6 to 8.3.0 has been painless for me. > >> > However, unlike the blogger you cite, I read the directions before, > >> > not after, attempting it. > >> > >> > >> The blogger has a point about pg_dump and restore, it could be much > >> better, for example > >> the backup process could be part of the server core and instead of > >> having a fat client where most of the process is running on the client, > >> a API could be > >> used where the backup is generated on the server and then have options > >> where it could be left on the server or transferred to the clients PC. > > > > Not really an option - the reason it's recommended to use the new > > pg_dump version with the older server when upgrading is to allow the > > dump to be made in the way most compatible with the new server, > > effectively doing some of the upgrade process as part of the dump > > operation. > > For the case of upgrading, it wouldn't work. But there are certainly > other cases where it would help. Say from your central pgadmin console > administering 10 servers from 3 different major release trees :-( > > It can be done with commandline pg_dump, but it means you have to have > three different installs on your management or backup or whatever > machine. Those cases would certainly be easier if you could just call a > backup API on the server that would feed you the data... (yes, there are > ways to do it with ssh tunneling and whatever, but that's yet another > external service that has to be set up and configured) Using the new pg_dump for dumping older versions during an ugprade is just inconvenient and something we should not need to do. At the worst we should have a way for us to upgrade the older version of pg_dump with whatever functionality we need and just tell people to be running the most recent minor release before upgrading. What cases on the past have needed the new pg_dump? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian escribió: > Magnus Hagander wrote: > > For the case of upgrading, it wouldn't work. But there are certainly > > other cases where it would help. Say from your central pgadmin console > > administering 10 servers from 3 different major release trees :-( What's wrong with providing statically-linked pg_dump-8.2, pg_dump-8.3 and so on, and asking the user which one to use (depending on the target server version)? > Using the new pg_dump for dumping older versions during an ugprade is > just inconvenient and something we should not need to do. At the worst > we should have a way for us to upgrade the older version of pg_dump with > whatever functionality we need and just tell people to be running the > most recent minor release before upgrading. > > What cases on the past have needed the new pg_dump? Dependency handling IIRC in 7.3 (or was it 7.2?) was a big change for pg_dump, and I don't think we would have liked to backpatch the pg_dump changes. Also, AFAIK the sequences stuff with OWNED BY also needed the newer pg_dump, which is more recent (8.2?). I don't think it's as rare as you suggest. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Bruce Momjian escribi�:
>> What cases on the past have needed the new pg_dump?
> Dependency handling IIRC in 7.3 (or was it 7.2?) was a big change for
> pg_dump, and I don't think we would have liked to backpatch the pg_dump
> changes. Also, AFAIK the sequences stuff with OWNED BY also needed the
> newer pg_dump, which is more recent (8.2?). I don't think it's as rare
> as you suggest.
Note that in straightforward cases, a dump from the older pg_dump will
serve perfectly well. However there have been cases in the past where
the dump/reload behavior of a PG version was unfixably broken for
certain corner cases --- renamed serial sequences being the latest
example. In such cases, dumping with the older pg_dump and loading into
a newer DB will not give you a correct copy of the state of your old
database, whereas dumping with the new pg_dump often does (because the
newer pg_dump knows how to exploit the feature we added to solve the
problem, such as OWNED BY).
The other usual reason for recommending this is the fear that someone's
old installation may not be up-to-date to its latest patch release, and
so its pg_dump might be missing bug fixes even for problems that *are*
soluble within its release series. The thought is that the version they
are installing probably is an up-to-date one, and so its pg_dump might
have fixes the other one doesn't.
None of this matters if you have a DB you can successfully dump and
reload in the old installation version. In that case you haven't got
any dump/reload bugs you are tripping over.
regards, tom lane
On Mon, Feb 18, 2008 at 06:35:11PM -0300, Alvaro Herrera wrote: > Bruce Momjian escribió: > > Magnus Hagander wrote: > > > > For the case of upgrading, it wouldn't work. But there are certainly > > > other cases where it would help. Say from your central pgadmin console > > > administering 10 servers from 3 different major release trees :-( > > What's wrong with providing statically-linked pg_dump-8.2, pg_dump-8.3 > and so on, and asking the user which one to use (depending on the target > server version)? Other than the much-increased work in building things, probabliy nothing. (The package would be noticably larger as well, of course, but that shuouldn't be a big problem today). //Magnus
On Feb 19, 2008 8:48 AM, Magnus Hagander <magnus@hagander.net> wrote: > On Mon, Feb 18, 2008 at 06:35:11PM -0300, Alvaro Herrera wrote: > > Bruce Momjian escribió: > > > Magnus Hagander wrote: > > > > > > For the case of upgrading, it wouldn't work. But there are certainly > > > > other cases where it would help. Say from your central pgadmin console > > > > administering 10 servers from 3 different major release trees :-( > > > > What's wrong with providing statically-linked pg_dump-8.2, pg_dump-8.3 > > and so on, and asking the user which one to use (depending on the target > > server version)? > > Other than the much-increased work in building things, probabliy nothing. > (The package would be noticably larger as well, of course, but that > shuouldn't be a big problem today). I suspect that building static versions of the utilities and retaining the OpenSSL & Kerberos support would be nigh-on impossible (I've never even managed to build my own dynamic version of Kerberos (which seems to rely heavily on the build environment used within MIT). In pgAdmin, bundling such utilities would be a big no-no. Imagine the docs - pgAdmin supports SSL encryption and Kerberos authentication, but if you wish to back or restore your databases you'll need to turn off those requirements in the server. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Oracle-compatible database company