Re: pg_upgrade does not upgrade pg_stat_statements properly

Dave Cramer

On Thu, 29 Jul 2021 at 12:16, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> On Thu, Jul 29, 2021 at 09:00:36AM -0700, David G. Johnston wrote:
> > On Thu, Jul 29, 2021 at 8:42 AM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> >
> > On Thu, Jul 29, 2021 at 11:36:19AM -0400, Dave Cramer wrote:
> > >
> > >
> > > I have an issue with the fragment "whether they are from
> contrib" -
> > my
> > > understanding at this point is that because of the way we
> package and
> > > version contrib it should not be necessary to copy those shared
> > object
> > > files from the old to the new server (maybe, just maybe, with a
> > > qualification that you are upgrading between two versions that
> were
> > in
> > > support during the same time period).
> > >
> > >
> > > Just to clarify. In no case are binaries copied from the old
> server to
> > the new
> > > server. Whether from contrib or otherwise.
> >
> > Right. Those are _binaries_ and therefore made to match a specific
> > Postgres binary. They might work or might not, but copying them is
> > never a good idea --- they should be recompiled to match the new
> server
> > binary, even if the extension had no version/API changes.
> >
> >
> > Ok, looking at the flow again, where exactly would the user even be able
> to
> > execute "CREATE EXTENSION" meaningfully? The relevant databases do not
> exist
> > (not totally sure what happens to the postgres database created during
> the
> > initdb step...) so at the point where the user is "installing the
> extension"
> > all they can reasonably do is a server-level install (they could maybe
> create
> > extension in the postgres database, but does that even matter?).
>
> They could technically start the new cluster and use "CREATE EXTENSION"
> before the upgrade, and then the ugprade would fail since there would be
> duplicate object errors.
>
> > So, I'd propose simplifying this all to something like:
> >
> > Install extensions on the new server
> >
> > Any extensions that are used by the old cluster need to be installed
> into the
> > new cluster. Each database in the old cluster will have its current
> version of
> > all extensions migrated to the new cluster as-is. You can use the ALTER
> > EXTENSION command, on a per-database basis, to update its extensions
> > post-upgrade.
>
> Can you review the text I just posted? Thanks. I think we are making
> progress. ;-)
>

I am OK with Everything except

Do not load the schema definitions,
e.g., <command>CREATE EXTENSION pgcrypto</command>, because these
will be recreated from the old cluster. (The extensions may be
upgraded later using <literal>ALTER EXTENSION ... UPGRADE</literal>.)

I take issue with the word "recreated". This implies something new is
created, when in fact the old definitions are simply copied over.

As I said earlier; using the wording "may be upgraded" is not nearly
cautionary enough.

Dave