Re: pg_upgrade vs extension upgrades

On Thu, Apr 13, 2017 at 6:06 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> 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
>
>
I agree it makes a lot of sense to have a separate tool that can do these
things, and that pg_upgrade can point the user towards it.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>