I know Tom Lane has done some work on pg_upgrade -- the last message was
on 8/2/99, and left the thread hanging.

What is the current status of pg_upgrade in 6.5.x??

I ask because the presence of a working pg_upgrade drastically reduces
the work necessary to get postgresql upgrading working prpoerly in an
RPM environment.  I particular, the upgrade from RedHat 6.0 to RedHat
6.1 is going to be from postgresql 6.4.2 to 6.5.1.  I do not forsee
anyone successfully upgrading a RedHat 5.x installation to 6.1, as other
things will break -- although I could be entirely wrong.

If pg_upgrade is hopelessly broken in 6.5.x, that's ok -- just means a
little more work.

Lamar Owen
WGCR Internet Radio

> I know Tom Lane has done some work on pg_upgrade -- the last message was
> on 8/2/99, and left the thread hanging.
> 
> What is the current status of pg_upgrade in 6.5.x??
> 
> I ask because the presence of a working pg_upgrade drastically reduces
> the work necessary to get postgresql upgrading working prpoerly in an
> RPM environment.  I particular, the upgrade from RedHat 6.0 to RedHat
> 6.1 is going to be from postgresql 6.4.2 to 6.5.1.  I do not forsee
> anyone successfully upgrading a RedHat 5.x installation to 6.1, as other
> things will break -- although I could be entirely wrong.

pg_upgrade will not work in converting from <= 6.4.* to 6.5.* because
the on-disk date format changed in 6.5.  Hopefully, 6.6 will allow
pg_upgrade for 6.5.* databases.  We try not to change the on-disk
format, but sometimes we have to.  MVCC required it for 6.5.*. 

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

Bruce Momjian wrote:
> pg_upgrade will not work in converting from <= 6.4.* to 6.5.* because
> the on-disk date format changed in 6.5.  Hopefully, 6.6 will allow
> pg_upgrade for 6.5.* databases.  We try not to change the on-disk
> format, but sometimes we have to.  MVCC required it for 6.5.*.

Ok, answers my question.  It would be nice to be able to say:
pg_upgrade --source-pgdata=/var/lib/pgsql-old --pgdata=/var/lib/pgsql
and have any version PostgreSQL database converted to the newest, but
maybe that's a pipe dream.  Sure would make upgrades easier, on
everybody, not just RedHatters -- such as those who have large amounts
of large objects. 

If I were a better C coder, and had more experience with the various
versions' on-disk formats, I'd be happy to try to tackle it myself. 
But, I'm not that great of a C coder, nor do I know the data structures
well enough.  Oh well.

Thanks much!

Lamar Owen
WGCR Internet Radio

> Bruce Momjian wrote:
> > pg_upgrade will not work in converting from <= 6.4.* to 6.5.* because
> > the on-disk date format changed in 6.5.  Hopefully, 6.6 will allow
> > pg_upgrade for 6.5.* databases.  We try not to change the on-disk
> > format, but sometimes we have to.  MVCC required it for 6.5.*.
> 
> Ok, answers my question.  It would be nice to be able to say:
> pg_upgrade --source-pgdata=/var/lib/pgsql-old --pgdata=/var/lib/pgsql
> and have any version PostgreSQL database converted to the newest, but
> maybe that's a pipe dream.  Sure would make upgrades easier, on
> everybody, not just RedHatters -- such as those who have large amounts
> of large objects. 
> 
> If I were a better C coder, and had more experience with the various
> versions' on-disk formats, I'd be happy to try to tackle it myself. 
> But, I'm not that great of a C coder, nor do I know the data structures
> well enough.  Oh well.


You would have to convert tons of rows of data in raw format.  Seems
like dump/reload would be easier.

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

Bruce Momjian wrote:
>Lamar Owen wrote:
> > If I were a better C coder, and had more experience with the various
> > versions' on-disk formats, I'd be happy to try to tackle it myself.
> > But, I'm not that great of a C coder, nor do I know the data structures
> > well enough.  Oh well.
> 
> You would have to convert tons of rows of data in raw format.  Seems
> like dump/reload would be easier.

For normal situations, it is.  However, in an RPM upgrade that occurs as
part of an OS upgrade (say, from RedHat 6.0 to RedHat 6.1), NO daemons
can be run during a package upgrade. That doesn't seem too bad until you
realize just what an RPM upgrade does....

The nastiness gets nastier: the RPM upgrade procedure (currently)
deletes the old package contents after installing the new package
contents, removing the backend version that can read the database.  You
rpm -Uvh postgresql*.rpm across major versions, and you lose data
(technically, you don't lose the data per se, you just lose the ability
to read it...). And you possibly lose a postgresql user as a result.  I
know -- it happened to me with mission-critical data.  Fortunately, I
had been doing pg_dumpall's, so it wasn't too bad -- but it sure caught
me off-guard! (admittedly, I was quite a newbie at the time....)

I am working around that -- backing up (using an extremely restrictive
set of commands, because this script MIGHT be running under a floppy
install image...) the executables and libraries necessary to run the
older version BEFORE the newer executables are brought in, backing up
the older version's PGDATA, running the older postmaster against the
older PGDATA with the older backend on a different port DURING the
startup of the NEWER version's init, initdb with the newer version's
backend, run the newer postmaster WHILE the older one is running, then
pipe the output of the older pg_dumpall into a newer psql -e template1
session. Then, I have to verify the integrity of the transfered data,
stop the older postmaster...etc.  Piece of cake?  Not quite. Why not let
the user do all that?  Because most users can't fathom doing all of
that.

You can see how pg_upgrade would be useful in such a scenario, no?  I'm
not complaining, just curious. With pg_upgrade, during the startup
script for the new version, I detect the version of the PGDATA I am
running with, if it's an older version I first make a backup and then
pg_upgrade PGDATA. Simpler, with less likelihood of failure, IMHO. If I
need to do an initdb first, not a problem -- I'm already going to have
that in there for the case of a fresh install.  

Lamar Owen
WGCR Internet Radio

> Bruce Momjian wrote:
> >Lamar Owen wrote:
> > > If I were a better C coder, and had more experience with the various
> > > versions' on-disk formats, I'd be happy to try to tackle it myself.
> > > But, I'm not that great of a C coder, nor do I know the data structures
> > > well enough.  Oh well.
> > 
> > You would have to convert tons of rows of data in raw format.  Seems
> > like dump/reload would be easier.
> 
> For normal situations, it is.  However, in an RPM upgrade that occurs as
> part of an OS upgrade (say, from RedHat 6.0 to RedHat 6.1), NO daemons
> can be run during a package upgrade. That doesn't seem too bad until you
> realize just what an RPM upgrade does....

Wow, doing a database upgrade inside an automated RPM.  That's quite a
task.  From your description, running pg_dumpall and psql to load the
data is a real chore in an automated system.

Considering the changes in aligment of row elements, and index table
changes, it would be quite difficult to write a program to convert that
data from one format to another.  Not impossible, but quite hard.



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

Bruce Momjian wrote:
> Lamar Owen wrote: 
> > For normal situations, it is.  However, in an RPM upgrade that occurs as
> > part of an OS upgrade (say, from RedHat 6.0 to RedHat 6.1), NO daemons
> > can be run during a package upgrade. That doesn't seem too bad until you
> > realize just what an RPM upgrade does....
> 
> Wow, doing a database upgrade inside an automated RPM.  That's quite a
> task.  From your description, running pg_dumpall and psql to load the
> data is a real chore in an automated system.

Oliver Elphik has done this for the Debian packages -- but debs don't
have some of the draconian restrictions RPM's do.  In particular, and
RPM that is packaged in the Official Boxed Set CANNOT under any
circumstances ask for input from the user, nor can it output anything to
the user.  RPM's that do so get kicked out of the boxed set.  And,
frankly, PostgreSQL's position in the boxed set is a Big Win.

> Considering the changes in aligment of row elements, and index table
> changes, it would be quite difficult to write a program to convert that
> data from one format to another.  Not impossible, but quite hard.

Reference my message to Tom Lane.  Yes, such a program would be hard --
but most of it is already written and available in CVS -- thank God for
CVS! -- all that's needed is to extract the storage managers for each
major version, extract the reading code, etc, to get the on-disk
representation to an intermediate in memory form, then write it out with
the latest and greatest storage manager (into a different file, of
course, until the upgrade is finished).  Unless I badly misunderstand
the way PostgreSQL does things, that should work -- but I may not have
expressed it the same way I see it in my mind.

I'm talking about a stripped down backend, in essence, whose only
purpose in life is to copy in and copy out -- but who has the unique
ability to read with one storage manager and write with another.  You
simply choose which storge manager is used for reading by the version of
the PGDATA tree.

Piecing together the right CVS code snippets will be a challenge.

Lamar Owen
WGCR Internet Radio

> Reference my message to Tom Lane.  Yes, such a program would be hard --
> but most of it is already written and available in CVS -- thank God for
> CVS! -- all that's needed is to extract the storage managers for each
> major version, extract the reading code, etc, to get the on-disk
> representation to an intermediate in memory form, then write it out with
> the latest and greatest storage manager (into a different file, of
> course, until the upgrade is finished).  Unless I badly misunderstand
> the way PostgreSQL does things, that should work -- but I may not have
> expressed it the same way I see it in my mind.

Do a cost/benefit analysis on that one.  :-)

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