Re: pg_upgrade vs extension upgrades

On Thu, Apr 13, 2017 at 11:48 AM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> On 4/12/17 22:56, Bruce Momjian wrote:
>> Is pg_upgrade the right place for an extension upgrade script? When
>> pg_upgrade started creating an incremental-analyze script, people
>> thought it should be more generic so it was moved to vacuumdb
>> --analyze-in-stages. Seems we should do the same thing for the
>> extension upgrade script.
>
> Yeah, many of the things that pg_upgrade would do or suggest after an
> upgrade could also apply to other upgrade methods, such as by logical
> replication. So offering them separately would be good.

And in theory, extension upgrades could happen any time, not just when
there's a major version upgrade. You could be using a
separately-bundled extension, or we could bump an extension version in
a minor release to fix some issue.

I think we should invent a clever name for a new utility, like
pg_maintain or something. It could let you know about (and optionally
perform) a variety of optional maintenance tasks:

- upgrading extensions
- reindexing indexes for which the version has been bumped, like hash
indexes in v10
- reindexing or dropping indexes marked invalid that were left behind
by a CIC failure
- analyzing tables that lack (full?) statistics
- triggering heap scans to reclaim HEAP_MOVED_* bits, if we have that
kind of thing someday

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company