Re: pg_upgrade (was: 8.2 features status)

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> I don't think there is very much hope of an in-place upgrade for
> scenarios involving changes in contents of user tables. In particular,
> what of a change that requires more space than before, such as adding a
> locale indicator to text fields? There's no guarantee that the data on
> an old page will still fit, and there's certainly no hope of something
> operating at the xlog level being able to move tuples across pages ---
> if nothing else, because it's not in a position to compute new index
> entries. I don't see this working for page-at-a-time updates even in a
> full backend environment; again, indexes are the killer consideration.
> I don't see how to get sane behavior from an index containing some
> old-style entries and some new-style ones for a changed datatype.
>
> As you mentioned, the scenarios that look practical for in-place upgrade
> are the ones where only system catalog contents need to change. We've
> already discussed this (many times) and agreed that we could live with
> restricting user-table changes to happen only once every few releases.

What of having support in the backend for two heap formats in each version.
That is, 8.3 would be able to support 8.2 heaps as well as 8.3. There could be
a flag in pg_class indicating the heap format for that heap. Then you would be
able to upgrade without rewriting all your tables and the only requirement is
that sometime prior to the next upgrade you issue a per-table ALTER TABLE that
involves rewriting the table such as CLUSTER or ALTER COLUMN TYPE USING.

That still requires 2x space but only for a single table at a time which is
much better than 2x space for the entire database. It also lets you schedule
that job for some point in time when you can arrange to have the extra space.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com