Re: Upgrading a database dump/restore

On Oct 11, 2006, at 9:36 AM, Tom Lane wrote:

> Theo Schlossnagle <jesus(at)omniti(dot)com> writes:
>> The real problem with a "dump" of the database is that you want to be
>> able to quickly switch back to a known working copy in the event of a
>> failure. A dump is the furthest possible thing from a working copy
>> as one has to rebuild the database (indexes, etc.) and in doing so,
>> you (1) spend the better part of a week running pg_restore and (2)
>> ANALYZE stats change, so your system's behavior changes in hard-to-
>> understand ways.
>
> Seems like you should be looking into maintaining a hot spare via
> PITR,
> if your requirement is for a bit-for-bit clone of your database.

The features in 8.2 that allow for that look excellent. Prior to
that, it is a bit clunky. But we do this already.

However, PITR and a second machine doesn't help during upgrades so
much. It doesn't allow for an easy rollback. I'd like an in-place
upgrade that is "supposed" to work. And then I'd do:

Phase 1 (confidence):
clone my filesystems
upgrade the clones
run regression tests to obtain confidence in a flawless upgrade.
drop the clones

Phase 1 (abort): drop clones

Phase 2 (upgrade):
snapshot the filesystems
upgrade the base

Phase 2 (abort): rollback to snapshots
Phase 2 (commit): drop the snapshots

// Theo Schlossnagle
// CTO -- http://www.omniti.com/~jesus/
// OmniTI Computer Consulting, Inc. -- http://www.omniti.com/