-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I am loathe to even bring this up, but with two messages today about it, I am 
going to be short and sweet:

We don't have a reasonable upgrade path.  ASCII dump->install 
new->initdb->restore is not a reasonable upgrade.  It is confusing to the 
newbie, and should be fixed.  We used to have an upgrade path in pg_upgrade 
- -- but it no longer works (but it was most definitely a fine effort for its 
time, Bruce!). Furthermore, the dump/restore cycle is a pain in the neck when 
tables get larger than a few hundred megabytes.  It's worse when the newer 
version won't properly restore from the old dump -- and you have to edit, 
potentially by hand, a multi-gigabyte dump to get it to restore.

A seamless binary upgrade utility will require Deep Knowledge -- of the kind 
that very few pgsql-hackers have.  Deeper knowledge than I have, that's for 
sure -- or I would have already done it.

I am not going to beat it into the ground this time -- I have argued the 
issue at length before (some would say for far too long! :-)).  But I am 
going to drop reminders as we get bug reports and compliants.  And Iwould 
remind the group that MySQL does this easily -- it has utilities to migrate 
between its different table types. 
- --
Lamar Owen
WGCR Internet Radio
1 Peter 4:11
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.4 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE7Augl5kGGI8vV9eERAgBbAKCjS5yOyYFjTYMBEf5+I3s6uvoSTQCeKhCm
fyqa45WqVjUgZF26YZ0/M2w=
=4TMK
-----END PGP SIGNATURE-----

Wed, 16 May 2001 16:50:42 -0400
Lamar Owen <lamar.owen@wgcr.org>
Upgrade issue (again).
> I am not going to beat it into the ground this time -- I have argued the 
> issue at length before (some would say for far too long! :-)).  But I am 
> going to drop reminders as we get bug reports and compliants.  And Iwould 
> remind the group that MySQL does this easily -- it has utilities to migrate 
> between its different table types. 

It is my understanding that the MySQL heap format hasn't changed since
1991.  I think that helps them upgrade.  We add features too quickly.  :-)


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 16 May 2001 18:32:45 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Upgrade issue (again).
On Wed, 16 May 2001, Lamar Owen wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> I am loathe to even bring this up, but with two messages today about it, I am
> going to be short and sweet:
>
> We don't have a reasonable upgrade path.  ASCII dump->install
> new->initdb->restore is not a reasonable upgrade.  It is confusing to the
> newbie, and should be fixed.  We used to have an upgrade path in pg_upgrade
> - -- but it no longer works (but it was most definitely a fine effort for its
> time, Bruce!). Furthermore, the dump/restore cycle is a pain in the neck when
> tables get larger than a few hundred megabytes.  It's worse when the newer
> version won't properly restore from the old dump -- and you have to edit,
> potentially by hand, a multi-gigabyte dump to get it to restore.

Personally ... I just upgraded 13 gig worth of databases using
dump/restore, didn't have to end a single file, in less then 1.5hrs from
start to finish ... *shrug*



Wed, 16 May 2001 20:05:27 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: Upgrade issue (again).
>> I am loathe to even bring this up, but with two messages today about
>> it, I am going to be short and sweet:
>> 
>> We don't have a reasonable upgrade path.

This is one of many, many things that need work.  It happens to be a
thing that requires a *lot* of work for, well, not so much payback
(certainly not a benefit you'd get every day you use Postgres).
Not to mention that it's a lot of pretty boring work.

So, personally, there are many other things that I will get to before
I worry about this.  Sorry that my priorities don't square with yours,
but that's how it is.  I'm not standing in the way of someone else
taking up the problem ...

			regards, tom lane

Wed, 16 May 2001 19:34:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Upgrade issue (again). 
At 04:50 PM 16-05-2001 -0400, Lamar Owen wrote:
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>I am loathe to even bring this up, but with two messages today about it, I
am 
>going to be short and sweet:
>
>We don't have a reasonable upgrade path.  ASCII dump->install 
>new->initdb->restore is not a reasonable upgrade.  It is confusing to the 
>newbie, and should be fixed.  We used to have an upgrade path in pg_upgrade 

>time, Bruce!). Furthermore, the dump/restore cycle is a pain in the neck
when 
>tables get larger than a few hundred megabytes.  It's worse when the newer 

I won't mind a better upgrade method. But so far I pipe the dump into gzip,
and the resulting file is of manageable size.

What I find annoying is that pg_dumpall doesn't support username and
password. So far I just do a pg_dump of the relevant databases, and
recreate the users manually when installing.

Also to reload the file I do:
zcat gzippedusernameandpassword.gz dbfile.gz | psql

That's a bit ugly :).

Cheerio,
Link.


Thu, 17 May 2001 13:36:57 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: Upgrade issue (again).
The Hermit Hacker wrote:
> 
> On Wed, 16 May 2001, Lamar Owen wrote:
> 
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > I am loathe to even bring this up, but with two messages today about it, I am
> > going to be short and sweet:
> >
> > We don't have a reasonable upgrade path.  ASCII dump->install
> > new->initdb->restore is not a reasonable upgrade.  It is confusing to the
> > newbie, and should be fixed.  We used to have an upgrade path in pg_upgrade
> > - -- but it no longer works (but it was most definitely a fine effort for its
> > time, Bruce!). Furthermore, the dump/restore cycle is a pain in the neck when
> > tables get larger than a few hundred megabytes.  It's worse when the newer
> > version won't properly restore from the old dump -- and you have to edit,
> > potentially by hand, a multi-gigabyte dump to get it to restore.
> 
> Personally ... I just upgraded 13 gig worth of databases using
> dump/restore, didn't have to end a single file, in less then 1.5hrs from
> start to finish ... *shrug*

On the otherhand, I just had a user/adminsitrator decide to upgrade an
instance of postgres on a seconadry server for testing. But he was using
X and never noticed that he was upgrading our primary production server.
In the end, no data was lost. But I had to pick up the pieces, and it
took me a somewhat more  more than 1.5 hours to do so, as no plans for
downtime or transition had been made at all. I would have preferred a
clean upgrade path.

Of course, the adminstrator should have known better. And you could
argue that the sysadmin has demonstrated that he has too many privelges.
At least one of those statments is true. But I still would rather have a
seamless upgrade.

-- 
Karl

Thu, 17 May 2001 09:06:17 -0400
Karl DeBisschop <kdebisschop@alert.infoplease.com>
Re: Upgrade issue (again).
Lincoln Yeoh writes:

> What I find annoying is that pg_dumpall doesn't support username and
> password.

Working on that.  Lots of merge failures...

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Thu, 17 May 2001 16:44:49 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Re: Upgrade issue (again).
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wednesday 16 May 2001 18:32, Bruce Momjian wrote:
> > And Iwould
> > remind the group that MySQL does this easily -- it has utilities to
> > migrate between its different table types.

> It is my understanding that the MySQL heap format hasn't changed since
> 1991.  I think that helps them upgrade.  We add features too quickly.  :-)

Which is a two-edged sword. :-)  The features are great -- commercial grade.  
The migration isn't so great.
- --
Lamar Owen
WGCR Internet Radio
1 Peter 4:11
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.4 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE7A/gD5kGGI8vV9eERAsHhAJ9mYGq2pl1gqWjFNkxwAP36xfMyQwCgrUS8
jBjLoYf4UIzPxKdAmHpSbMQ=
=Jgar
-----END PGP SIGNATURE-----

Thu, 17 May 2001 12:10:40 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: Upgrade issue (again).
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wednesday 16 May 2001 19:34, Tom Lane wrote:
> >> I am loathe to even bring this up, but with two messages today about
> >> it, I am going to be short and sweet:
> >> We don't have a reasonable upgrade path.

> This is one of many, many things that need work.  It happens to be a
> thing that requires a *lot* of work for, well, not so much payback
> (certainly not a benefit you'd get every day you use Postgres).
> Not to mention that it's a lot of pretty boring work.

All the above are a little too true.  And I wish, I really wish, I had a 
ready solution to make it less work on everybody concerned.

> So, personally, there are many other things that I will get to before
> I worry about this.  Sorry that my priorities don't square with yours,
> but that's how it is.  I'm not standing in the way of someone else
> taking up the problem ...

No need to apologize -- your top-notch skills are in wide demand all across 
the backend. :-)

As are the particular skills of each of the core and key hackers.

As I said, I was not really enjoying the thought of bringing it up, but I 
felt I had to do my duty to the userbase.
- --
Lamar Owen
WGCR Internet Radio
1 Peter 4:11
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.4 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE7A/lu5kGGI8vV9eERAvRLAKDDGkLYVthOX5sCGA6DrSy2H6SxEACgqa5R
Q7C+14jxqpNY3L4WSdopZUY=
=ezlw
-----END PGP SIGNATURE-----

Thu, 17 May 2001 12:16:43 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: Upgrade issue (again).
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Wednesday 16 May 2001 19:05, The Hermit Hacker wrote:
> On Wed, 16 May 2001, Lamar Owen wrote:
> > I am loathe to even bring this up, but with two messages today about it,
> > I am going to be short and sweet:

> > We don't have a reasonable upgrade path.  ASCII dump->install
> > new->initdb->restore is not a reasonable upgrade.  Furthermore, the 
> > dump/restore cycle is a
> > pain in the neck when tables get larger than a few hundred megabytes. 

> Personally ... I just upgraded 13 gig worth of databases using
> dump/restore, didn't have to end a single file, in less then 1.5hrs from
> start to finish ... *shrug*

And 1.5 hours of downtime wasn't a problem? *raised eyebrow* :-)  Or did you 
migrate to a different box running the new version?  Or were you running more 
than one version on the one box?  Some don't have that choice as easy, nor 
are they as experienced as you and I.  Nor do they desire that much downtime.

My vision of an ideal upgrade:
Stop old version postmaster.
Install new verison.
Start new version postmaster.
New version migrates in place while being able to give access to the old data 
with the only downtime being stopping the old postmaster and starting the new.

This is hard.  And, as Tom so well put it: it's a lot of boring work to get 
it to work right. But, IMHO, this is one of the most gifted and talented set 
of hackers in any open source project -- surely we could find both a concept 
and implementation of a way to actually do the inplace seamless upgrade, 
couldn't we?
- --
Lamar Owen
WGCR Internet Radio
1 Peter 4:11
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.4 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE7A/sx5kGGI8vV9eERApiwAKDRzYZSmwpcwlsRcexuGovNA77uNACeLIOS
g2O3Q0KP4+ODIuqjjvzu3gY=
=WRxi
-----END PGP SIGNATURE-----

Thu, 17 May 2001 12:24:14 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: Upgrade issue (again).
Best way to upgrade might bee to do something as simple as get the
master to master replication working.

Old version on one box, new version on another.  Fire up for
replication -- done automatically -- with both boxes acting as
masters.  Change all the code to use the new server (or new database
on same server), then remove the old one from the queue and turn off
replication.

0 down time, no data migration issues (handled internally by postgres
replication), etc.  Worst part is you'll use double the disk space for
the period of time with 2 masters, and the system will run slower but
atleast it's up.

This is long term future though.  Need master to master replication
first as both servers have to be able to update the other with the new
information while the code that uses it is being changed around.  It
also means that replication will need to be version independent.

Of course, I'm not the one doing this but it sure seems like the
easiest way to go about it since most of the features involved are on
the drawing board already.

--
Rod Taylor

----- Original Message -----
From: "Lamar Owen" <lamar.owen@wgcr.org>
To: "The Hermit Hacker" <scrappy@hub.org>
Cc: <pgsql-hackers@postgresql.org>
Sent: Thursday, May 17, 2001 12:24 PM
Subject: Re: [HACKERS] Upgrade issue (again).


> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Wednesday 16 May 2001 19:05, The Hermit Hacker wrote:
> > On Wed, 16 May 2001, Lamar Owen wrote:
> > > I am loathe to even bring this up, but with two messages today
about it,
> > > I am going to be short and sweet:
>
> > > We don't have a reasonable upgrade path.  ASCII dump->install
> > > new->initdb->restore is not a reasonable upgrade.  Furthermore,
the
> > > dump/restore cycle is a
> > > pain in the neck when tables get larger than a few hundred
megabytes.
>
> > Personally ... I just upgraded 13 gig worth of databases using
> > dump/restore, didn't have to end a single file, in less then
1.5hrs from
> > start to finish ... *shrug*
>
> And 1.5 hours of downtime wasn't a problem? *raised eyebrow* :-)  Or
did you
> migrate to a different box running the new version?  Or were you
running more
> than one version on the one box?  Some don't have that choice as
easy, nor
> are they as experienced as you and I.  Nor do they desire that much
downtime.
>
> My vision of an ideal upgrade:
> Stop old version postmaster.
> Install new verison.
> Start new version postmaster.
> New version migrates in place while being able to give access to the
old data
> with the only downtime being stopping the old postmaster and
starting the new.
>
> This is hard.  And, as Tom so well put it: it's a lot of boring work
to get
> it to work right. But, IMHO, this is one of the most gifted and
talented set
> of hackers in any open source project -- surely we could find both a
concept
> and implementation of a way to actually do the inplace seamless
upgrade,
> couldn't we?
> - --
> Lamar Owen
> WGCR Internet Radio
> 1 Peter 4:11
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.0.4 (GNU/Linux)
> Comment: For info see http://www.gnupg.org
>
> iD8DBQE7A/sx5kGGI8vV9eERApiwAKDRzYZSmwpcwlsRcexuGovNA77uNACeLIOS
> g2O3Q0KP4+ODIuqjjvzu3gY=
> =WRxi
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Thu, 17 May 2001 12:43:49 -0400
"Rod Taylor" <rod.taylor@inquent.com>
Re: Upgrade issue (again).
On Thu, 17 May 2001, Lamar Owen wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Wednesday 16 May 2001 19:05, The Hermit Hacker wrote:
> > On Wed, 16 May 2001, Lamar Owen wrote:
> > > I am loathe to even bring this up, but with two messages today about it,
> > > I am going to be short and sweet:
>
> > > We don't have a reasonable upgrade path.  ASCII dump->install
> > > new->initdb->restore is not a reasonable upgrade.  Furthermore, the
> > > dump/restore cycle is a
> > > pain in the neck when tables get larger than a few hundred megabytes.
>
> > Personally ... I just upgraded 13 gig worth of databases using
> > dump/restore, didn't have to end a single file, in less then 1.5hrs from
> > start to finish ... *shrug*
>
> And 1.5 hours of downtime wasn't a problem? *raised eyebrow* :-)  Or
> did you migrate to a different box running the new version?  Or were
> you running more than one version on the one box?  Some don't have
> that choice as easy, nor are they as experienced as you and I.  Nor do
> they desire that much downtime.

Wasn't a problem ... I pre-warned all our clients, some of which make such
heavy use of the DB that we have to run vacuum on it every few hours, and,
to them, the benefits outweigh'd the brief bit of downtime ... *shrug*


Thu, 17 May 2001 13:59:39 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: Upgrade issue (again).
>>>>> "Bruce" == Bruce Momjian <pgman@candle.pha.pa.us> writes:

    >> I am not going to beat it into the ground this time -- I have
    >> argued the issue at length before (some would say for far too
    >> long! :-)).  But I am going to drop reminders as we get bug
    >> reports and compliants.  And Iwould remind the group that MySQL
    >> does this easily -- it has utilities to migrate between its
    >> different table types.

    Bruce> It is my understanding that the MySQL heap format hasn't
    Bruce> changed since 1991.  I think that helps them upgrade.  We
    Bruce> add features too quickly.  :-)

Just to clear this little bit of particularly unconstructive FUD from
the air , no they haven't changed NISAM but created a new format :-

    Upgrading from a 3.22 version to 3.23
    -------------------------------------

    *MySQL* 3.23 supports tables of the new `MyISAM' type and the old
    `NISAM' type.  You don't have to convert your old tables to use these
    with 3.23.  By default, all new tables will be created with type
    `MyISAM' (unless you start `mysqld' with the
    `--default-table-type=isam' option. You can change an `ISAM' table to a
    `MyISAM' table with `ALTER TABLE' or the Perl script
    `mysql_convert_table_format'.

Sincerely,

Adrian Phillips

-- 
Your mouse has moved.
Windows NT must be restarted for the change to take effect.
Reboot now?  [OK]

18 May 2001 15:07:14 +0200
Adrian Phillips <adrianp@powertech.no>
Re: Upgrade issue (again).
On Thu, May 17, 2001 at 12:43:49PM -0400, Rod Taylor wrote:
> Best way to upgrade might bee to do something as simple as get the
> master to master replication working.

Master-to-master replication is not simple, and (fortunately) isn't 
strictly necessary.  The minimal sequence is,

1. Start a backup and a redo log at the same time.
2. Start the new database and read the backup.
3. Get the new database consuming the redo logs.
4. When the new database catches up, make it a hot failover for the old.
5. Turn off the old database and fail over.

The nice thing about this approach is that all the parts used are 
essential parts of an enterprise database anyway, regardless of their 
usefulness in upgrading.  

Master-to-master replication is nice for load balancing, but not
necessary for failover.  Its chief benefit, there, is that you wouldn't 
need to abort the uncompleted transactions on the old database when 
you make the switch.  But master-to-master replication is *hard* to
make work, and intrusive besides.

Nathan Myers
ncm@zembu.com


Fri, 18 May 2001 10:36:00 -0700
ncm@zembu.com (Nathan Myers)
Re: Upgrade issue (again).