On Mon, 29 Jan 2024 at 16:01, Bharath Rupireddy
<bharath.rupireddyforpostg...@gmail.com> wrote:
>
> On Mon, Jan 29, 2024 at 10:10 AM vignesh C <vignes...@gmail.com> wrote:
> >
> > Thanks for the comments, the attached v5 version patch has the changes
> > for the same.
>
> Thanks for working on this. Here are some comments on the v5 patch:
>
> 1.
> + <para>
> + Migration of logical replication clusters is possible only when all the
> + members of the old logical replication clusters are version 17.0 or later.
>
> Perhaps define what logical replication cluster is either in glossary
> or within a parenthesis next to the first use in the docs? This will
> help developers understand it better and will not confuse them with
> postgres cluster. I see it being used for the first time in code
> comments 9a17be1e2, but this patch uses it for the first time in the
> docs.
I have added it in glossary.
> 2.
> + Before reading this section, refer <xref linkend="pgupgrade"/> page for
> + more details about pg_upgrade.
> + </para>
>
> This looks extraneous, we can just link to pg_upgrade on the first use
> of pg_upgrade, change the following
>
> + <para>
> + <application>pg_upgrade</application> attempts to migrate logical
> + slots. This helps avoid the need for manually defining the same
>
> to
>
> + <para>
> + <xref linkend="pgupgrade"/> attempts to migrate logical
> + slots. This helps avoid the need for manually defining the same
Modified
> 3.
> + transactional, the user is advised to take backups. Backups can be taken
> + as described in <xref linkend="backup-base-backup"/>.
> + </para>
>
> How about simplifying the above to "the user is advised to take
> backups as described in <xref linkend="backup-base-backup"/>" instead
> of two statements?
Modified
> 4.
> subscription is temporarily disabled, by executing
> + <link linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION
> ... DISABLE</command></link>.
> + Re-enable the subscription after the upgrade.
> + </para>
>
> Is it to avoid repeated failures of logical replication apply workers
> on the subscribers? Isn't it good to say why subscription needs to be
> disabled?
Added it
> 5.
> + <para>
> + There are some prerequisites for <application>pg_upgrade</application> to
> + be able to upgrade the logical slots. If these are not met an error
> + will be reported.
> + </para>
>
> I think it's better to be "Following are prerequisites for
> <application>pg_upgrade</application> to.."?
Modified
> 6.
> + <listitem>
> + <para>
> + The old cluster has replicated all the transactions and logical
> decoding
> + messages to subscribers.
> + </para>
>
> I think it's better to be "The old cluster must have replicated all
> the transactions and ...."?
Modified
> 7.
> + <para>
> + The new cluster must not have permanent logical slots, i.e.,
> + there must be no slots where
> + <link
> linkend="view-pg-replication-slots">pg_replication_slots</link>.<structfield>temporary</structfield>
> + is <literal>false</literal>.
>
> I think we better specify a full SQL query as opposed to just
> specifying one output column and the view name.
>
> <para>
> The new cluster must not have permanent logical slots, i.e., a query like:
> <programlisting>
> SELECT count(*) FROM pg_replication_slots WHERE slot_type = 'logical'
> AND temporary IS false;
> </programlisting>
> must return 0.
> </para>
Modified
> 8.
> + If the old cluster is prior to 17.0, then no slots on the primary are
> + copied to the new standby, so all the slots must be recreated
> manually.
> + If the old cluster is 17.0 or later, then only logical slots on the
>
> I think it's better to say "version 17.0" instead of just "17.0".
Modified
> 9.
> + primary are copied to the new standby, but other slots on the
> old standby
>
> "but other slots on the old standby" - is it slots on the old standby
> or old cluster?
>
> I think it's the other way around: the old cluster needs to be
> replaced with the old standby in the newly added paragraph.
Modified it to old primary as we upgrade primary and do a rsync
> 10.
> Change
> + primary are copied to the new standby, but other slots on the
> old standby
> + are not copied so must be recreated manually.
>
> to
>
> + primary are copied to the new standby, but other slots on the
> old standby
> + are not copied, so must be recreated manually.
Modified
> 11.
> + <note>
> + <para>
> + The logical replication restrictions apply to logical replication
> cluster
> + upgrades also. See <xref linkend="logical-replication-restrictions"/>
> for
> + the details of logical replication restrictions.
> + </para>
>
> How about just say "See <xref
> linkend="logical-replication-restrictions"/> for details." instead of
> using logical replication restrictions more than once in the same
> para?
Modified
> 12.
> + <para>
> + The prerequisites of publisher upgrade apply to logical replication
> + cluster upgrades also. See <xref linkend="prepare-publisher-upgrades"/>
> + for the details of publisher upgrade prerequisites.
>
> How about just say "See <xref linkend="prepare-publisher-upgrades"/>
> for details." instead of using publisher upgrade prerequisites more
> than once in the same para?
Modified
> 13.
> + <para>
> + The prerequisites of subscriber upgrade apply to logical replication
> + cluster upgrades also. See <xref linkend="prepare-subscriber-upgrades"/>
> + for the details of subscriber upgrade prerequisites.
> + </para>
>
> How about just say "See <xref linkend="prepare-subscriber-upgrades"/>
> for details." instead of using subscriber upgrade prerequisites more
> than once in the same para?
Modified
> 14.
> + Upgrading logical replication cluster requires multiple steps to be
> + performed on various nodes. Because not all operations are
>
> Per comment #1, defining logical replication clusters and nodes helps
> clearly distinguish. For instance, one can get confused with the
> various terms in hand - postgres cluster, logical replication cluster,
> node etc.
I have added "logical replication clusters". I felt no need to add
node as it is not a new terminology. It is already being used in many
places like in [1], [2] & [3]
> 15.
> + two subscriptions <literal>sub1_node1_node2</literal> and
> + <literal>sub2_node1_node2</literal> which are subscribing the changes
> + from <literal>node1</literal>.
>
> Why confluse with subsription names by including node1 and node2 in
> it? We are not creating subscriptions from node1 to node2, are we? I'd
> recommend using simplified names like mysub1, mysub2 like elsewhere in
> the documentation.
I have used the name sub1_node1_node to indicate it is subscribing
changes from node1 to node2. I felt this is self explainatory names.
> 16.
> + Let's say publisher is in <literal>node1</literal> and subscriber is
> + in <literal>node2</literal>.
>
> How about saying "publisher is in a database cluster named
> <literal>node1</literal> and subscriber is in database cluster named
> <literal>node2</literal>"? I think using this terminology helps.
I felt existing is ok as similar is used in [2] & [3]
> 17.
> + refer to <xref linkend="logical-replication-upgrade"/> for details.
> + </para>
> + </note>
>
> IMHO, it could have been better if steps to upgrade the logical
> replication cluster is specified in pgupgrade.sgml as opposed to
> logical-replication.sgml. Because, upgrading logical replication
> cluster is a sub-section for pg_upgrade.
As the content for logical replication is more, I felt it is better to
keep it here and also we have given a link to this in the pg_upgrade
page. I did not want the upgrade page to become bulky because of the
logical replication upgrade section.
> 18.
> + <para>
> + The steps to upgrade the following logical replication clusters are
> + detailed below:
> + <itemizedlist>
> + <listitem>
> + <para>
> + Follow the steps specified in
>
> I think we can talk about what advantages upgrading logical
> replication clusters brings in. We can say that the pg_upgrade makes
> it possible 1) to re-use the logical replication slots post-upgrade,
> 2) to re-use the subscribers i.e. now it's not required to re-create
> all the logical subscribers after the upgrade, so no initial table
> sync, no creation of new clusters for subscribers etc.
I felt this is self explanatory, no need to mention about the
complexity involved in the manual steps involved. As the same is not
mentioned in case of streaming replication too at [4].
> 19. I think we can talk about the possible gotchas i.e. the things
> that can go wrong while performing any of the prescribed steps. What
> happens if the slot the pg_upgrade interrupts after it upgraded a few
> of the replication slots or if some of the prerequisites are not met
> etc.?
There is the note below when we run pg_upgrade:
"If pg_upgrade fails after this point, you must re-initdb the new
cluster before continuing."
I felt this is kind of self explanatory. Also the pre-requisite
mentions clearly about the configurations that must be set before
upgrade is run. So I felt the existing information was enough.
Thanks for the comment, the attached v6 version patch has the changes
for the same.
[1] - https://www.postgresql.org/docs/devel/logical-replication.html
[2] - https://www.postgresql.org/docs/devel/logical-replication-publication.html
[3] -
https://www.postgresql.org/docs/devel/logical-replication-subscription.html
[4] - https://www.postgresql.org/docs/devel/pgupgrade.html
Regards,
Vignesh
From 4eb9ae93d5973cd15ef30bf7dac79f1e49b8c786 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignes...@gmail.com>
Date: Tue, 30 Jan 2024 08:55:20 +0530
Subject: [PATCH v6] Documentation for upgrading logical replication cluster.
Documentation for upgrading logical replication cluster.
---
doc/src/sgml/glossary.sgml | 10 +
doc/src/sgml/logical-replication.sgml | 811 ++++++++++++++++++++++++++
doc/src/sgml/ref/pgupgrade.sgml | 148 +----
3 files changed, 841 insertions(+), 128 deletions(-)
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml
index 8c2f11480d..1f9166f27b 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -1068,6 +1068,16 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-logical-replication-cluster">
+ <glossterm>Logical replication cluster</glossterm>
+ <glossdef>
+ <para>
+ A set of publisher and subscriber instance with publisher instance
+ replicating changes to the subscriber instance.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-log-record">
<glossterm>Log record</glossterm>
<glossdef>
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index ec2130669e..e124d699c0 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -1926,6 +1926,817 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
</sect1>
+ <sect1 id="logical-replication-upgrade">
+ <title>Upgrade</title>
+
+ <para>
+ Migration of logical replication clusters is possible only when all the
+ members of the old logical replication clusters are version 17.0 or later.
+ </para>
+
+ <sect2 id="prepare-publisher-upgrades">
+ <title>Prepare for publisher upgrades</title>
+
+ <para>
+ <xref linkend="pgupgrade"/> attempts to migrate logical
+ slots. This helps avoid the need for manually defining the same
+ logical slots on the new publisher. Migration of logical slots is
+ only supported when the old cluster is version 17.0 or later.
+ Logical slots on clusters before version 17.0 will silently be
+ ignored.
+ </para>
+
+ <para>
+ Before you start upgrading the publisher cluster, ensure that the
+ subscription is temporarily disabled to avoid the subscriber connection
+ failures during publisher upgrade, by executing
+ <link linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>.
+ Re-enable the subscription after the upgrade.
+ </para>
+
+ <para>
+ Following are the prerequisites for <application>pg_upgrade</application>
+ to be able to upgrade the logical slots. If these are not met an error
+ will be reported.
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ The new cluster must have
+ <link linkend="guc-wal-level"><varname>wal_level</varname></link> as
+ <literal>logical</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The new cluster must have
+ <link linkend="guc-max-replication-slots"><varname>max_replication_slots</varname></link>
+ configured to a value greater than or equal to the number of slots
+ present in the old cluster.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The output plugins referenced by the slots on the old cluster must be
+ installed in the new <productname>PostgreSQL</productname> executable
+ directory.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The old cluster must have replicated all the transactions and logical
+ decoding messages to subscribers.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ All slots on the old cluster must be usable, i.e., there are no slots
+ whose
+ <link linkend="view-pg-replication-slots">pg_replication_slots</link>.<structfield>conflict_reason</structfield>
+ is not <literal>NULL</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The new cluster must not have permanent logical slots, i.e.,
+ there must be no slots listed with:
+<programlisting>
+SELECT count(*) FROM pg_replication_slots WHERE slot_type = 'logical'
+AND temporary IS false;
+</programlisting>
+ </para>
+ </listitem>
+ </itemizedlist>
+ </sect2>
+
+ <sect2 id="prepare-subscriber-upgrades">
+ <title>Prepare for subscriber upgrades</title>
+
+ <para>
+ Setup the <link linkend="logical-replication-config-subscriber">
+ subscriber configurations</link> in the new subscriber.
+ </para>
+
+ <para>
+ <application>pg_upgrade</application> attempts to migrate subscription
+ dependencies which includes the subscription's table information present in
+ <link linkend="catalog-pg-subscription-rel">pg_subscription_rel</link>
+ system catalog and also the subscription's replication origin. This allows
+ logical replication on the new subscriber to continue from where the
+ old subscriber was up to. Migration of subscription dependencies is only
+ supported when the old cluster is version 17.0 or later. Subscription
+ dependencies on clusters before version 17.0 will silently be ignored.
+ </para>
+
+ <para>
+ Following are the prerequisites for <application>pg_upgrade</application> to
+ be able to upgrade the subscriptions. If these are not met an error
+ will be reported.
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ All the subscription tables in the old subscriber should be in state
+ <literal>i</literal> (initialize) or <literal>r</literal> (ready). This
+ can be verified by checking <link linkend="catalog-pg-subscription-rel">pg_subscription_rel</link>.<structfield>srsubstate</structfield>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The replication origin entry corresponding to each of the subscriptions
+ should exist in the old cluster. This can be found by checking
+ <link linkend="catalog-pg-subscription">pg_subscription</link> and
+ <link linkend="catalog-pg-replication-origin">pg_replication_origin</link>
+ system tables.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ The new cluster must have
+ <link linkend="guc-max-replication-slots"><varname>max_replication_slots</varname></link>
+ configured to a value greater than or equal to the number of
+ subscriptions present in the old cluster.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </sect2>
+
+ <sect2 id="upgrading-logical-replication-clusters">
+ <title>Upgrading logical replication clusters</title>
+
+ <para>
+ While upgrading a subscriber, write operations can be performed in the
+ publisher. These changes will be replicated to the subscriber once the
+ subscriber upgrade is completed.
+ </para>
+
+ <note>
+ <para>
+ The logical replication restrictions apply to logical replication cluster
+ upgrades also. See <xref linkend="logical-replication-restrictions"/> for
+ details.
+ </para>
+ <para>
+ The prerequisites of publisher upgrade apply to logical replication
+ cluster upgrades also. See <xref linkend="prepare-publisher-upgrades"/>
+ for details.
+ </para>
+ <para>
+ The prerequisites of subscriber upgrade apply to logical replication
+ cluster upgrades also. See <xref linkend="prepare-subscriber-upgrades"/>
+ for details.
+ </para>
+ </note>
+
+ <warning>
+ <para>
+ Upgrading logical replication cluster requires multiple steps to be
+ performed on various nodes. Because not all operations are
+ transactional, the user is advised to take backups as described in
+ <xref linkend="backup-base-backup"/>.
+ </para>
+ </warning>
+
+ <para>
+ The steps to upgrade the following logical replication clusters are
+ detailed below:
+ <itemizedlist>
+ <listitem>
+ <para>
+ Follow the steps specified in
+ <xref linkend="steps-two-node-logical-replication-cluster"/> to upgrade
+ a two-node logical replication cluster.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Follow the steps specified in
+ <xref linkend="steps-cascaded-logical-replication-cluster"/> to upgrade
+ a cascaded logical replication cluster.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Follow the steps specified in
+ <xref linkend="steps-two-node-circular-logical-replication-cluster"/>
+ to upgrade a two-node circular logical replication cluster.
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <sect3 id="steps-two-node-logical-replication-cluster">
+ <title>Steps to upgrade a two-node logical replication cluster</title>
+ <para>
+ Let's say publisher is in <literal>node1</literal> and subscriber is
+ in <literal>node2</literal>. The subscriber <literal>node2</literal> has
+ two subscriptions <literal>sub1_node1_node2</literal> and
+ <literal>sub2_node1_node2</literal> which are subscribing the changes
+ from <literal>node1</literal>.
+ </para>
+
+ <procedure>
+ <step id="two-node-cluster-disable-subscriptions-node2">
+ <para>
+ Disable all the subscriptions on <literal>node2</literal> that are
+ subscribing the changes from <literal>node1</literal> by using
+ <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
+ e.g.:
+<programlisting>
+node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node1_node2 DISABLE;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+ <step>
+ <para>
+ Stop the publisher server in <literal>node1</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data1 stop
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize <literal>data1_upgraded</literal> instance by using the
+ required newer version.
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Upgrade the publisher <literal>node1</literal>'s server to the
+ required newer version, e.g.:
+<programlisting>
+pg_upgrade
+ --old-datadir "/opt/PostgreSQL/postgres/17/data1"
+ --new-datadir "/opt/PostgreSQL/postgres/18/data1_upgraded"
+ --old-bindir "/opt/PostgreSQL/postgres/17/bin"
+ --new-bindir "/opt/PostgreSQL/postgres/18/bin"
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Start the upgraded publisher server in <literal>node1</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Stop the subscriber server in <literal>node2</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data2 stop
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize <literal>data2_upgraded</literal> instance by using the
+ required newer version.
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Upgrade the subscriber <literal>node2</literal>'s server to
+ the required new version, e.g.:
+<programlisting>
+pg_upgrade
+ --old-datadir "/opt/PostgreSQL/postgres/17/data2"
+ --new-datadir "/opt/PostgreSQL/postgres/18/data2_upgraded"
+ --old-bindir "/opt/PostgreSQL/postgres/17/bin"
+ --new-bindir "/opt/PostgreSQL/postgres/18/bin"
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Start the upgraded subscriber server in <literal>node2</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ On <literal>node2</literal>, create any tables that were created in
+ the upgraded publisher <literal>node1</literal> server between
+ <xref linkend="two-node-cluster-disable-subscriptions-node2"/>
+ and now, e.g.:
+<programlisting>
+node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
+CREATE TABLE
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Enable all the subscriptions on <literal>node2</literal> that are
+ subscribing the changes from <literal>node1</literal> by using
+ <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
+ e.g.:
+<programlisting>
+node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node1_node2 ENABLE;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Refresh the <literal>node2</literal> subscription's publications using
+ <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
+ e.g.:
+<programlisting>
+node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node1_node2 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+ </procedure>
+ </sect3>
+
+ <sect3 id="steps-cascaded-logical-replication-cluster">
+ <title>Steps to upgrade a cascaded logical replication cluster</title>
+ <para>
+ Let's say we have a cascaded logical replication setup
+ <literal>node1</literal>-><literal>node2</literal>-><literal>node3</literal>.
+ Here <literal>node2</literal> is subscribing the changes from
+ <literal>node1</literal> and <literal>node3</literal> is subscribing
+ the changes from <literal>node2</literal>. The <literal>node2</literal>
+ has two subscriptions <literal>sub1_node1_node2</literal> and
+ <literal>sub2_node1_node2</literal> which are subscribing the changes
+ from <literal>node1</literal>. The <literal>node3</literal> has two
+ subscriptions <literal>sub1_node2_node3</literal> and
+ <literal>sub2_node2_node3</literal> which are subscribing the changes
+ from <literal>node2</literal>.
+ </para>
+
+ <procedure>
+ <step id="cascaded-cluster-disable-sub-node1-node2">
+ <para>
+ Disable all the subscriptions on <literal>node2</literal> that are
+ subscribing the changes from <literal>node1</literal> by using
+ <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
+ e.g.:
+<programlisting>
+node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node1_node2 DISABLE;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Stop the server in <literal>node1</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data1 stop
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize <literal>data1_upgraded</literal> instance by using the
+ required newer version.
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Upgrade the <literal>node1</literal>'s server to the required newer
+ version, e.g.:
+<programlisting>
+pg_upgrade
+ --old-datadir "/opt/PostgreSQL/postgres/17/data1"
+ --new-datadir "/opt/PostgreSQL/postgres/18/data1_upgraded"
+ --old-bindir "/opt/PostgreSQL/postgres/17/bin"
+ --new-bindir "/opt/PostgreSQL/postgres/18/bin"
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Start the upgraded server in <literal>node1</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step id="cascaded-cluster-disable-sub-node2-node3">
+ <para>
+ Disable all the subscriptions on <literal>node3</literal> that are
+ subscribing the changes from <literal>node2</literal> by using
+ <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
+ e.g.:
+<programlisting>
+node3=# ALTER SUBSCRIPTION sub1_node2_node3 DISABLE;
+ALTER SUBSCRIPTION
+node3=# ALTER SUBSCRIPTION sub2_node2_node3 DISABLE;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Stop the server in <literal>node2</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data2 stop
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize <literal>data2_upgraded</literal> instance by using the
+ required newer version.
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Upgrade the <literal>node2</literal>'s server to the required
+ new version, e.g.:
+<programlisting>
+pg_upgrade
+ --old-datadir "/opt/PostgreSQL/postgres/17/data2"
+ --new-datadir "/opt/PostgreSQL/postgres/18/data2_upgraded"
+ --old-bindir "/opt/PostgreSQL/postgres/17/bin"
+ --new-bindir "/opt/PostgreSQL/postgres/18/bin"
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Start the upgraded server in <literal>node2</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ On <literal>node2</literal>, create any tables that were created in
+ the upgraded publisher <literal>node1</literal> server between
+ <xref linkend="cascaded-cluster-disable-sub-node1-node2"/>
+ and now, e.g.:
+<programlisting>
+node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
+CREATE TABLE
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Enable all the subscriptions on <literal>node2</literal> that are
+ subscribing the changes from <literal>node1</literal> by using
+ <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
+ e.g.:
+<programlisting>
+node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node1_node2 ENABLE;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Refresh the <literal>node2</literal> subscription's publications using
+ <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
+ e.g.:
+<programlisting>
+node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node1_node2 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Stop the server in <literal>node3</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data3 stop
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize <literal>data3_upgraded</literal> instance by using the
+ required newer version.
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Upgrade the <literal>node3</literal>'s server to the required
+ new version, e.g.:
+<programlisting>
+pg_upgrade
+ --old-datadir "/opt/PostgreSQL/postgres/17/data3"
+ --new-datadir "/opt/PostgreSQL/postgres/18/data3_upgraded"
+ --old-bindir "/opt/PostgreSQL/postgres/17/bin"
+ --new-bindir "/opt/PostgreSQL/postgres/18/bin"
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Start the upgraded server in <literal>node3</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data3_upgraded start -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ On <literal>node3</literal>, create any tables that were created in
+ the upgraded <literal>node2</literal> between
+ <xref linkend="cascaded-cluster-disable-sub-node2-node3"/> and now,
+ e.g.:
+<programlisting>
+node3=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
+CREATE TABLE
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Enable all the subscriptions on <literal>node3</literal> that are
+ subscribing the changes from <literal>node2</literal> by using
+ <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
+ e.g.:
+<programlisting>
+node3=# ALTER SUBSCRIPTION sub1_node2_node3 ENABLE;
+ALTER SUBSCRIPTION
+node3=# ALTER SUBSCRIPTION sub2_node2_node3 ENABLE;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Refresh the <literal>node3</literal> subscription's publications using
+ <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
+ e.g.:
+<programlisting>
+node3=# ALTER SUBSCRIPTION sub1_node2_node3 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+node3=# ALTER SUBSCRIPTION sub2_node2_node3 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+ </procedure>
+ </sect3>
+
+ <sect3 id="steps-two-node-circular-logical-replication-cluster">
+ <title>Steps to upgrade a two-node circular logical replication cluster</title>
+ <para>
+ Let's say we have a circular logical replication setup
+ <literal>node1</literal>-><literal>node2</literal> and
+ <literal>node2</literal>-><literal>node1</literal>. Here
+ <literal>node2</literal> is subscribing the changes from
+ <literal>node1</literal> and <literal>node1</literal> is subscribing
+ the changes from <literal>node2</literal>. The <literal>node1</literal>
+ has two subscriptions <literal>sub1_node2_node1</literal> and
+ <literal>sub2_node2_node1</literal> which are subscribing the changes
+ from <literal>node2</literal>. The <literal>node2</literal> has two
+ subscriptions <literal>sub1_node1_node2</literal> and
+ <literal>sub2_node1_node2</literal> which are subscribing the changes
+ from <literal>node1</literal>.
+ </para>
+
+ <procedure>
+ <step id="circular-cluster-disable-sub-node2">
+ <para>
+ Disable all the subscriptions on <literal>node2</literal> that are
+ subscribing the changes from <literal>node1</literal> by using
+ <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
+ e.g.:
+<programlisting>
+node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node1_node2 DISABLE;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Stop the server in <literal>node1</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data1 stop
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize <literal>data1_upgraded</literal> instance by using the
+ required newer version.
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Upgrade the <literal>node1</literal>'s server to the required
+ newer version, e.g.:
+<programlisting>
+pg_upgrade
+ --old-datadir "/opt/PostgreSQL/postgres/17/data1"
+ --new-datadir "/opt/PostgreSQL/postgres/18/data1_upgraded"
+ --old-bindir "/opt/PostgreSQL/postgres/17/bin"
+ --new-bindir "/opt/PostgreSQL/postgres/18/bin"
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Start the upgraded server in <literal>node1</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ On <literal>node1</literal>, create any tables that were created in
+ <literal>node2</literal> between <xref linkend="circular-cluster-disable-sub-node2"/>
+ and now, e.g.:
+<programlisting>
+node1=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
+CREATE TABLE
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Enable all the subscriptions on <literal>node2</literal> that are
+ subscribing the changes from <literal>node1</literal> by using
+ <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
+ e.g.:
+<programlisting>
+node2=# ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node1_node2 ENABLE;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Refresh the <literal>node2</literal> subscription's publications using
+ <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
+ e.g.:
+<programlisting>
+node2=# ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+node2=# ALTER SUBSCRIPTION sub2_node1_node2 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+
+ <step id="circular-cluster-disable-sub-node1">
+ <para>
+ Disable all the subscriptions on <literal>node1</literal> that are
+ subscribing the changes from <literal>node2</literal> by using
+ <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
+ e.g.:
+<programlisting>
+node1=# ALTER SUBSCRIPTION sub1_node2_node1 DISABLE;
+ALTER SUBSCRIPTION
+node1=# ALTER SUBSCRIPTION sub2_node2_node1 DISABLE;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Stop the server in <literal>node2</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data2 stop
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize <literal>data2_upgraded</literal> instance by using the
+ required newer version.
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Upgrade the <literal>node2</literal>'s server to the required
+ new version, e.g.:
+<programlisting>
+pg_upgrade
+ --old-datadir "/opt/PostgreSQL/postgres/17/data2"
+ --new-datadir "/opt/PostgreSQL/postgres/18/data2_upgraded"
+ --old-bindir "/opt/PostgreSQL/postgres/17/bin"
+ --new-bindir "/opt/PostgreSQL/postgres/18/bin"
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Start the upgraded server in <literal>node2</literal>, e.g.:
+<programlisting>
+pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ On <literal>node2</literal>, create any tables that were created in
+ the upgraded <literal>node1</literal> between <xref linkend="circular-cluster-disable-sub-node1"/>
+ and now, e.g.:
+<programlisting>
+node2=# CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
+CREATE TABLE
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Enable all the subscriptions on <literal>node1</literal> that are
+ subscribing the changes from <literal>node2</literal> by using
+ <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
+ e.g.:
+<programlisting>
+node1=# ALTER SUBSCRIPTION sub1_node2_node1 ENABLE;
+ALTER SUBSCRIPTION
+node1=# ALTER SUBSCRIPTION sub2_node2_node1 ENABLE;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Refresh the <literal>node1</literal> subscription's publications using
+ <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>
+ e.g.:
+<programlisting>
+node1=# ALTER SUBSCRIPTION sub1_node2_node1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+node1=# ALTER SUBSCRIPTION sub2_node2_node1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting>
+ </para>
+ </step>
+ </procedure>
+ </sect3>
+
+ </sect2>
+ </sect1>
+
<sect1 id="logical-replication-quick-setup">
<title>Quick Setup</title>
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
index ae6d3c49a6..ada409143a 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -70,6 +70,7 @@ PostgreSQL documentation
pg_upgrade supports upgrades from 9.2.X and later to the current
major release of <productname>PostgreSQL</productname>, including snapshot and beta releases.
</para>
+
</refsect1>
<refsect1>
@@ -278,10 +279,17 @@ PostgreSQL documentation
<title>Usage</title>
<para>
- These are the steps to perform an upgrade
- with <application>pg_upgrade</application>:
+ Below are the steps to perform an upgrade
+ with <application>pg_upgrade</application>.
</para>
+ <note>
+ <para>
+ The steps to upgrade logical replication clusters are not covered here;
+ refer to <xref linkend="logical-replication-upgrade"/> for details.
+ </para>
+ </note>
+
<procedure>
<step performance="optional">
<title>Optionally move the old cluster</title>
@@ -383,129 +391,6 @@ make prefix=/usr/local/pgsql.new install
</para>
</step>
- <step>
- <title>Prepare for publisher upgrades</title>
-
- <para>
- <application>pg_upgrade</application> attempts to migrate logical
- slots. This helps avoid the need for manually defining the same
- logical slots on the new publisher. Migration of logical slots is
- only supported when the old cluster is version 17.0 or later.
- Logical slots on clusters before version 17.0 will silently be
- ignored.
- </para>
-
- <para>
- Before you start upgrading the publisher cluster, ensure that the
- subscription is temporarily disabled, by executing
- <link linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>.
- Re-enable the subscription after the upgrade.
- </para>
-
- <para>
- There are some prerequisites for <application>pg_upgrade</application> to
- be able to upgrade the logical slots. If these are not met an error
- will be reported.
- </para>
-
- <itemizedlist>
- <listitem>
- <para>
- The new cluster must have
- <link linkend="guc-wal-level"><varname>wal_level</varname></link> as
- <literal>logical</literal>.
- </para>
- </listitem>
- <listitem>
- <para>
- The new cluster must have
- <link linkend="guc-max-replication-slots"><varname>max_replication_slots</varname></link>
- configured to a value greater than or equal to the number of slots
- present in the old cluster.
- </para>
- </listitem>
- <listitem>
- <para>
- The output plugins referenced by the slots on the old cluster must be
- installed in the new PostgreSQL executable directory.
- </para>
- </listitem>
- <listitem>
- <para>
- The old cluster has replicated all the transactions and logical decoding
- messages to subscribers.
- </para>
- </listitem>
- <listitem>
- <para>
- All slots on the old cluster must be usable, i.e., there are no slots
- whose
- <link linkend="view-pg-replication-slots">pg_replication_slots</link>.<structfield>conflict_reason</structfield>
- is not <literal>NULL</literal>.
- </para>
- </listitem>
- <listitem>
- <para>
- The new cluster must not have permanent logical slots, i.e.,
- there must be no slots where
- <link linkend="view-pg-replication-slots">pg_replication_slots</link>.<structfield>temporary</structfield>
- is <literal>false</literal>.
- </para>
- </listitem>
- </itemizedlist>
-
- </step>
-
- <step>
- <title>Prepare for subscriber upgrades</title>
-
- <para>
- Setup the <link linkend="logical-replication-config-subscriber">
- subscriber configurations</link> in the new subscriber.
- <application>pg_upgrade</application> attempts to migrate subscription
- dependencies which includes the subscription's table information present in
- <link linkend="catalog-pg-subscription-rel">pg_subscription_rel</link>
- system catalog and also the subscription's replication origin. This allows
- logical replication on the new subscriber to continue from where the
- old subscriber was up to. Migration of subscription dependencies is only
- supported when the old cluster is version 17.0 or later. Subscription
- dependencies on clusters before version 17.0 will silently be ignored.
- </para>
-
- <para>
- There are some prerequisites for <application>pg_upgrade</application> to
- be able to upgrade the subscriptions. If these are not met an error
- will be reported.
- </para>
-
- <itemizedlist>
- <listitem>
- <para>
- All the subscription tables in the old subscriber should be in state
- <literal>i</literal> (initialize) or <literal>r</literal> (ready). This
- can be verified by checking <link linkend="catalog-pg-subscription-rel">pg_subscription_rel</link>.<structfield>srsubstate</structfield>.
- </para>
- </listitem>
- <listitem>
- <para>
- The replication origin entry corresponding to each of the subscriptions
- should exist in the old cluster. This can be found by checking
- <link linkend="catalog-pg-subscription">pg_subscription</link> and
- <link linkend="catalog-pg-replication-origin">pg_replication_origin</link>
- system tables.
- </para>
- </listitem>
- <listitem>
- <para>
- The new cluster must have
- <link linkend="guc-max-replication-slots"><varname>max_replication_slots</varname></link>
- configured to a value greater than or equal to the number of
- subscriptions present in the old cluster.
- </para>
- </listitem>
- </itemizedlist>
- </step>
-
<step>
<title>Stop both servers</title>
@@ -767,9 +652,16 @@ rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tb
Configure the servers for log shipping. (You do not need to run
<function>pg_backup_start()</function> and <function>pg_backup_stop()</function>
or take a file system backup as the standbys are still synchronized
- with the primary.) Only logical slots on the primary are copied to the
- new standby, but other slots on the old standby are not copied so must
- be recreated manually.
+ with the primary.)
+ </para>
+
+ <para>
+ If the old primary is prior to version 17.0, then no slots on the primary
+ are copied to the new standby, so all the slots on the old standby must
+ be recreated manually. If the old primary is version 17.0 or later, then
+ only logical slots on the primary are copied to the new standby, but
+ other slots on the old standby are not copied, so must be recreated
+ manually.
</para>
</step>
--
2.34.1
