Re: pg_upgrade does not upgrade pg_stat_statements properly

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. ;-)

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

If only the physical world exists, free will is an illusion.