Re: [WIP] In-place upgrade

Bruce Momjian <bruce@momjian.us> writes:
> I envision a similar system where we have utilities to guarantee all
> pages have enough free space, and all pages are the current version,
> before allowing an upgrade-in-place to the next version.  Such a
> consistent API will make the job for users easier and our job simpler,
> and with upgrade-in-place, where we have limited time and resources to
> code this for each release, simplicity is important.

An external utility doesn't seem like the right way to approach it.
For example, given the need to ensure X amount of free space in each
page, the only way to guarantee that would be to shut down the database
while you run the utility over all the pages --- otherwise somebody
might fill some page up again.  And that completely defeats the purpose,
which is to have minimal downtime during upgrade.

I think we can have a notion of pre-upgrade maintenance, but it would
have to be integrated into normal operations.  For instance, if
conversion to 8.4 requires extra free space, we'd make late releases
of 8.3.x not only be able to force that to occur, but also tweak the
normal code paths to maintain that minimum free space.

The full concept as I understood it (dunno why Bruce left all these
details out of his message) went like this:

* Add a "format serial number" column to pg_class, and probably also
pg_database.  Rather like the frozenxid columns, this would have the
semantics that all pages in a relation or database are known to have at
least the specified format number.

* There would actually be two serial numbers per release, at least for
releases where pre-update prep work is involved --- for instance,
between 8.3 and 8.4 there'd be an "8.3-and-a-half" format which is
8.3 but known ready to update to 8.4 (eg, enough free space available).
Minor releases of 8.3 that appear with or subsequent to 8.4 release
understand the "half" format number and how to upgrade to it.

* VACUUM would be empowered, in the same way as it handles frozenxid
maintenance, to update any less-than-the-latest-version pages and then
fix the pg_class and pg_database entries.

* We could mechanically enforce that you not update until the database
is ready for it by checking pg_database.datformatversion during
postmaster startup.

So the update process would require users to install a suitably late
version of 8.3, vacuum everything over a suitable maintenance window,
then install 8.4, then perhaps vacuum everything again if they want to
try to push page update work into specific maintenance windows.  But
the DB is up and functioning the whole time.
        regards, tom lane