Re: Pains in upgrading to 8.3 - Mailing list pgsql-general

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