Re: pg_upgrade does not upgrade pg_stat_statements properly

Dave Cramer

On Wed, 14 Jul 2021 at 15:43, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Dave Cramer <davecramer(at)gmail(dot)com> writes:
> > On Wed, 14 Jul 2021 at 15:09, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com>
> > wrote:
> >> "Install ... files used by the old cluster" (which must be binary
> >> compatible with the new cluster as noted elsewhere on that page)
> supports
> >> the claim that it is the old cluster's version that is going to result.
> >> But I agree that saying "because these will be upgraded from the old
> >> cluster" is poorly worded and should be fixed to be more precise here.
> >>
> >> Something like, "... because the installed extensions will be copied
> from
> >> the old cluster during the upgrade."
>
> > This is still rather opaque. Without intimate knowledge of what changes
> > have occurred in each extension I have installed; how would I know what I
> > have to fix after the upgrade.
>
> That's exactly why we don't force upgrades of extensions. It is on the
> user's head to upgrade their extensions from time to time, but we don't
> make them do it as part of pg_upgrade. (There are also some
> implementation-level reasons to avoid this, IIRC, but the overall
> choice is intentional.)
>
> I agree this documentation could be worded better.

As a first step I propose the following

diff --git a/doc/src/sgml/ref/pgupgrade.sgml
b/doc/src/sgml/ref/pgupgrade.sgml
index a83c63cd98..f747a4473a 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -305,9 +305,10 @@ make prefix=/usr/local/pgsql.new install
Install any custom shared object files (or DLLs) used by the old
cluster
into the new cluster, e.g., <filename>pgcrypto.so</filename>,
whether they are from <filename>contrib</filename>
- or some other source. Do not install the schema definitions, e.g.,
- <command>CREATE EXTENSION pgcrypto</command>, because these will be
upgraded
- from the old cluster.
+ or some other source. Do not execute CREATE EXTENSION on the new
cluster.
+ The extensions will be upgraded from the old cluster. However it may
be
+ necessary to recreate the extension on the new server after the
upgrade
+ to ensure compatibility with the new library.
Also, any custom full text search files (dictionary, synonym,
thesaurus, stop words) must also be copied to the new cluster.
</para>

> Another idea
> is that possibly pg_upgrade could produce a list of extensions
> that are not the latest version, so people know what's left to
> be addressed.
>

It would be possible to look at the control files in the new cluster to see
the default version and simply output a file with the differences.
We can query pg_extension for the currently installed versions.

Dave

>
>