Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1m9BMw-0000rx-GJ
	for pgsql-hackers@arkaria.postgresql.org; Thu, 29 Jul 2021 19:06:58 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1m9BMt-0007jt-Cn
	for pgsql-hackers@arkaria.postgresql.org; Thu, 29 Jul 2021 19:06:55 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <bruce@momjian.us>)
	id 1m9BMt-0007jk-4t
	for pgsql-hackers@lists.postgresql.org; Thu, 29 Jul 2021 19:06:55 +0000
Received: from momjian.us ([72.94.173.45])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <bruce@momjian.us>)
	id 1m9BMm-0003q5-E4
	for pgsql-hackers@lists.postgresql.org; Thu, 29 Jul 2021 19:06:53 +0000
Received: from bruce by momjian.us with local (Exim 4.92)
	(envelope-from <bruce@momjian.us>)
	id 1m9BMj-0004YS-KA; Thu, 29 Jul 2021 15:06:45 -0400
Date: Thu, 29 Jul 2021 15:06:45 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Dave Cramer <davecramer@gmail.com>,
	"David G. Johnston" <david.g.johnston@gmail.com>,
	Tom Lane <tgl@sss.pgh.pa.us>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_upgrade does not upgrade pg_stat_statements properly
Message-ID: <20210729190645.GS9600@momjian.us>
References: <20210729163448.GP9600@momjian.us>
 <202107291743.pqlj3s2uilfu@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0qt3EE9wi45a2ZFX"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <202107291743.pqlj3s2uilfu@alvherre.pgsql>
User-Agent: Mutt/1.10.1 (2018-07-13)
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/20210729190645.GS9600%40momjian.us>
Precedence: bulk


--0qt3EE9wi45a2ZFX
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On Thu, Jul 29, 2021 at 01:43:09PM -0400, Álvaro Herrera wrote:
> On 2021-Jul-29, Bruce Momjian wrote:
> 
> > +     If the old cluster used extensions, whether from
> > +     <filename>contrib</filename> or some other source, it used
> > +     shared object files (or DLLs) to implement these extensions, e.g.,
> > +     <filename>pgcrypto.so</filename>.  Now, shared object files matching
> > +     the new server binary must be installed in the new cluster, usually
> > +     via operating system commands.  Do not load the schema definitions,
> > +     e.g., <command>CREATE EXTENSION pgcrypto</command>, because these
> > +     will be copied from the old cluster.  (Extensions should be upgraded
> > +     later using <literal>ALTER EXTENSION ... UPGRADE</literal>.)
> 
> I propose this:
> 
> <para>
>   If the old cluster used shared-object files (or DLLs) for extensions
>   or other loadable modules, install recompiled versions of those files
>   onto the new cluster.
>   Do not install the extension themselves (i.e., do not run
>   <command>CREATE EXTENSION</command>), because extension definitions
>   will be carried forward from the old cluster.
> </para>
> 
> <para>
>   Extensions can be upgraded after pg_upgrade completes using
>   <command>ALTER EXTENSION ... UPGRADE</command>, on a per-database
>   basis.
> </para>
> 
> I suggest " ... for extensions or other loadable modules" because
> loadable modules aren't necessarily for extensions.  Also, it's
> perfectly possible to have extension that don't have a loadable module.

Yes, good point.

> I suggest "extension definitions ... carried forward" instead of
> "extensions ... copied" (your proposed text) to avoid the idea that
> files are copied; use it instead of "schema definitions ... upgraded"
> (the current docs) to avoid the idea that they are actually upgraded;
> also, "schema definition" seems a misleading term to use here.

I used the term "duplicated".

> I suggest "can be upgraded" rather than "should be upgraded" because
> we're not making a recommendation, merely stating the fact that it is
> possible to do so.

Agreed.  Most extensions don't have updates between major versions.

> I suggest using the imperative mood, to be consistent with the
> surrounding text.  (Applies to the first para; the second para is
> informative.)

OK.

> I haven't seen it mentioned in the thread, but I think the final phrase
> of this <step> should be a separate step,
> 
> <step>
>  <title>Copy custom full-text search files</title>
>  <para>
>   Copy any custom full text search file (dictionary, synonym, thesaurus,
>   stop word list) to the new server.
>  </para>
> </step>
> 
> While this is closely related to extensions, it's completely different.

Agreed.  See attached patch.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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


--0qt3EE9wi45a2ZFX
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="pg_upgrade.diff"

diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
index a83c63cd98..0a71465805 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -299,17 +299,27 @@ make prefix=/usr/local/pgsql.new install
    </step>
 
    <step>
-    <title>Install custom shared object files</title>
+    <title>Install extension shared object files</title>
 
     <para>
-     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.
-     Also, any custom full text search files (dictionary, synonym,
-     thesaurus, stop words) must also be copied to the new cluster.
+     Many extensions and custom modules, whether from
+     <filename>contrib</filename> or another source, use shared object
+     files (or DLLs), e.g., <filename>pgcrypto.so</filename>.  If the old
+     cluster used these, shared object files matching the new server binary
+     must be installed in the new cluster, usually via operating system
+     commands.  Do not load the schema definitions, e.g., <command>CREATE
+     EXTENSION pgcrypto</command>, because these will be duplicated from
+     the old cluster.  (Extensions with available updates can be processed
+     later using <literal>ALTER EXTENSION ... UPDATE</literal>.)
+    </para>
+   </step>
+
+   <step>
+    <title>Copy custom full-text search files</title>
+
+    <para>
+     Copy any custom full text search files (dictionary, synonym,
+     thesaurus, stop words) from the old to the new cluster.
     </para>
    </step>
 
@@ -494,10 +504,10 @@ pg_upgrade.exe
      </step>
 
      <step>
-      <title>Install custom shared object files</title>
+      <title>Install extension shared object files</title>
 
       <para>
-       Install the same custom shared object files on the new standbys
+       Install the same extension shared object files on the new standbys
        that you installed in the new primary cluster.
       </para>
      </step>

--0qt3EE9wi45a2ZFX--