Horton
Menu

Thread

  1. Why dump/restore to upgrade?

    mlw <markw@mohawksoft.com> — 2002-02-08T02:56:22Z

    We all understand the reasons why one MUST dump and restore to upgrade to 7.2,
but I'd like to make a general call to arms that this (or 7.3) should be the
last release to require this.

It doesn't look good. One should be able to upgrade in place, and even revert
to an older version, if nessisary.

What do you all think?

  2. Re: Why dump/restore to upgrade?

    Peter Bierman <bierman@apple.com> — 2002-02-08T03:32:59Z

    At 9:56 PM -0500 2/7/02, mlw wrote:
>We all understand the reasons why one MUST dump and restore to upgrade to 7.2,
>but I'd like to make a general call to arms that this (or 7.3) should be the
>last release to require this.
>
>It doesn't look good. One should be able to upgrade in place, and even revert
>to an older version, if nessisary.
>
>What do you all think?


At the very least, wouldn't it be easy for pg to automate the existing process?
Say, dump/restore using textfiles in /tmp, and maybe even spit out a backup copy of the old database as a big tarball?

IMHO, most of the complaints about PostgreSQL revolve around initial setup and inital ease of use. Sure, none of those issues matter once you're used to the system, but they wouldn't be difficult to fix either.

-pmb

  3. Re: Why dump/restore to upgrade?

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-02-08T05:44:07Z

    mlw <markw@mohawksoft.com> writes:
> but I'd like to make a general call to arms that this (or 7.3) should be the
> last release to require this.

We will never make such a commitment, at least not in the foreseeable
future.

I would like to see more attention paid to supporting cross-version
upgrades via pg_upgrade (or some improved version thereof) when
practical, which it should be more often than not.  But to bind
ourselves forever to the current on-disk format is sheer folly.
And if you have to convert the datafile format then you might as
well dump and reload.

			regards, tom lane

  4. Re: Why dump/restore to upgrade?

    Lamar Owen <lamar.owen@wgcr.org> — 2002-02-08T06:35:40Z

    On Friday 08 February 2002 12:44 am, Tom Lane wrote:
> mlw <markw@mohawksoft.com> writes:
> > but I'd like to make a general call to arms that this (or 7.3) should be
> > the last release to require this.

> We will never make such a commitment, at least not in the foreseeable
> future.

Why?

> But to bind
> ourselves forever to the current on-disk format is sheer folly.

Certainly true.  But upgradability!=bound-to-the-same-format.

> And if you have to convert the datafile format then you might as
> well dump and reload.

No.  Dump - reload is folly.  And it doesn't always work.  And that's the 
problem.

I've fought this problem a long time.  Too long of a time.  And it is a 
problem.  Unfortunately, it is a problem that is going to require some deep 
thought and hackery.

I believe it should be this simple for our users:

1.) Postmaster starts, finds old files.  It's OK with that.

2.) A connection starts a postgres backend.  When the backend starts, it sees 
the old format tree and adjusts to it as best it can -- if this means fewer 
features, well, it'll just have to get over it.  Send a warning down the 
connection that it is in reduced functionality mode or some such.

3.) An SQL command could then be issued down the connection that would, in a 
transaction-safe manner, convert the data on the disk into the newest format. 
Until the magic bullet upgrade command is sent, the backend operates in a 
reduced functionality mode -- maybe even read-only if necessary. 

In this mode, a safer pg_dump can be executed -- how many times now have we 
told people to use a newer pg_dump to dump an old version database?  Just 
having read-only access to the old data through the new backend would in 
reality be much better than the fiasco we have now -- then we can safely 
pg_dump the data, stop postmaster, initdb, start postmaster, and reload the 
data.

If the conversion program is large enough to cause worry about backend bloat, 
then make it standalone and not let postmaster start up on old data -- 
pg_upgrade on steroids.

No, this isn't a core feature.  Yes, there are features that are better uses 
of developer time.  Sure, there is a partially working pg_upgrade utility, 
for which I thank Bruce for weathering it out upon. 

BUT OUR UPGRADE PROCESS STINKS.  

Sorry for yelling.  But this touches a raw nerve for me.  My apologies if I 
have offended anyone -- PostgreSQL is just too good an RDBMS to suffer from 
this problem.  The developers here have put too much hard work of high 
quality for anyone to disparage PostgreSQL due to the lack of good solid 
upgrading.  And I'm not upset at any one person -- it's the program; the 
process; and the users that rely on our code which cause me to be this 
vehement on this subject.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

  5. Re: Why dump/restore to upgrade?

    mlw <markw@mohawksoft.com> — 2002-02-08T12:29:49Z

    Tom Lane wrote:
> 
> mlw <markw@mohawksoft.com> writes:
> > but I'd like to make a general call to arms that this (or 7.3) should be the
> > last release to require this.
> 
> We will never make such a commitment, at least not in the foreseeable
> future.

Here's the problem. If you have a database that is in service, you can not
upgrade postgres on that machine without taking it out of service for the
duration of a backup/restore. A small database is not a big deal, a large
database is a problem. A system could be out of service for hours.

For a mission critical installation, this is really unacceptable.

> 
> I would like to see more attention paid to supporting cross-version
> upgrades via pg_upgrade (or some improved version thereof) when
> practical, which it should be more often than not.  But to bind
> ourselves forever to the current on-disk format is sheer folly.
> And if you have to convert the datafile format then you might as
> well dump and reload.

The backup/restore to upgrade will be a deal breaker for many installations. If
you want more people using PostgreSQL, you need to accept that this is a very
real problem, and one which should be addressed as an unacceptable behavior.

I don't want to say "Other databases do it, why can't PostgreSQL" because that
isn't the point. Databases can be HUGE, pg_dumpall can take an hour or more to
run. Then, it takes longer to restore because indexes have to be recreated.

  6. Re: Why dump/restore to upgrade?

    Matthew Kirkwood <matthew@hairy.beasts.org> — 2002-02-08T13:25:59Z

    On Fri, 8 Feb 2002, mlw wrote:

> > We will never make such a commitment, at least not in the foreseeable
> > future.
>
> Here's the problem. If you have a database that is in service, you can
> not upgrade postgres on that machine without taking it out of service
> for the duration of a backup/restore. A small database is not a big
> deal, a large database is a problem. A system could be out of service
> for hours.
>
> For a mission critical installation, this is really unacceptable.

Cheap solution: replication.  Bring up a slave with the
new version.  Do the initial sync.  Wait for a quiet
time, and switch over (possibly still replicating to
the old one so you can revent the upgrade).

The overhead would be fairly high, but it's a cheap way
to get the required properties, and is easy to revert.

Matthew.

  7. Re: Why dump/restore to upgrade?

    Justin Clift <justin@postgresql.org> — 2002-02-08T13:29:58Z

    Hi everyone,

mlw wrote:
> 
> Tom Lane wrote:
<snip> 
> For a mission critical installation, this is really unacceptable.
> 
> >
> > I would like to see more attention paid to supporting cross-version
> > upgrades via pg_upgrade (or some improved version thereof) when
> > practical, which it should be more often than not.  But to bind
> > ourselves forever to the current on-disk format is sheer folly.
> > And if you have to convert the datafile format then you might as
> > well dump and reload.
> 
> The backup/restore to upgrade will be a deal breaker for many installations. If
> you want more people using PostgreSQL, you need to accept that this is a very
> real problem, and one which should be addressed as an unacceptable behavior.
> 
> I don't want to say "Other databases do it, why can't PostgreSQL" because that
> isn't the point. Databases can be HUGE, pg_dumpall can take an hour or more to
> run. Then, it takes longer to restore because indexes have to be recreated.

Here's a thought for a method which doesn't yet exist, but as we're
about to start into the next version of PostgreSQL it might be worth
considering.

a) Do a pg_dump of the old-version database in question.  It takes note
of the latest transaction numbers in progress.

b) On a seperate machine, start doing a restore of the data, into the
new version database.

c) Take the old-version database out-of-production, so no new
transactions are done on it.

d) Run a yet-to-be-created utility which then takes a look at the
difference between the two, and updates the new-version database with
the entries which have changed since the first snapshot.

e) Put the new-version database into production, assuming the
applications hitting it have already been tested for compatibility with
the new version.

You could skip step c) (taking the old database offline) and do instead
the syncronisation step with it online if there has been a large
timeframe of changes, and THEN (one the differences are minimal) take
the old-version database offline and do another syncronisation for the
remaining differences.

However, I get the feeling a lot of this kind of thing is very similar
to some of the approaches to replication already around or being
developed.

It might be simpler to make the old-version database a master replica,
make the new-version database a slave replica of it, then once they're
in sync cut over to the new system (again assuming the applications
using it have been tested for compatibility with the new version).

The theory sounds alright, but in practise it might not be that easy. 
We can live in hope however.  :)

Regards and best wishes,

Justin Clift


> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

  8. Re: Why dump/restore to upgrade?

    Matthew T. O'Connor <matthew@zeut.net> — 2002-02-08T16:17:29Z

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

> I don't want to say "Other databases do it, why can't PostgreSQL" because
> that isn't the point. Databases can be HUGE, pg_dumpall can take an hour or
> more to run. Then, it takes longer to restore because indexes have to be
> recreated.

A way to put PostgreSQL into a read only mode could help this (as well as 
many other things.)  That way they could at least let users have access to 
the data the entire time that the dump restore process is going.  It could 
take a lot of disk space, but it would help the case where time is a bigger 
problem than space.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE8Y/oc8BXvT14W9HARAiGgAJ9ckgaq2YHupc/1Wl6RlRNJ5UulNwCeNWrt
ZrVN4qo+L9nDckpIPQ80MYM=
=1WXQ
-----END PGP SIGNATURE-----

  9. Re: Why dump/restore to upgrade?

    Trond Eivind Glomsrød <teg@redhat.com> — 2002-02-08T16:24:27Z

    Lamar Owen <lamar.owen@wgcr.org> writes:

> On Friday 08 February 2002 12:44 am, Tom Lane wrote:
> > mlw <markw@mohawksoft.com> writes:
> > > but I'd like to make a general call to arms that this (or 7.3) should be
> > > the last release to require this.
> 
> > We will never make such a commitment, at least not in the foreseeable
> > future.
> 
> Why?
> 
> > But to bind
> > ourselves forever to the current on-disk format is sheer folly.
> 
> Certainly true.  But upgradability!=bound-to-the-same-format.
> 
> > And if you have to convert the datafile format then you might as
> > well dump and reload.
> 
> No.  Dump - reload is folly.  And it doesn't always work.  And that's the 
> problem.

Add some unicode into it for even more fun. Bah.

-- 
Trond Eivind Glomsrød
Red Hat, Inc.