On Fri, 5 Jan 2024 at 09:08, Hayato Kuroda (Fujitsu)
<[email protected]> wrote:
>
> Dear Vignesh,
>
> Thanks for making a patch! Below part is my comments.
>
> 1.
> Only two steps were added an id, but I think it should be for all the steps.
> See [1].
I have added wherever it is required as of now.
> 2.
> I'm not sure it should be listed as step 10. I felt that it should be new
> section.
> At that time other steps like "Prepare for {publisher|subscriber} upgrades"
> can be moved as well.
> Thought?
I have moved all of these to a separate page in logical-replication
under Upgrade
> 3.
> ```
> + The prerequisites of publisher upgrade applies to logical Replication
> ```
>
> Replication -> replication
Modified
> 4.
> ```
> + <para>
> + Let's say publisher is in <literal>node1</literal> and subscriber
> is
> + in <literal>node2</literal>.
> + </para>
> ```
>
> I felt it is more friendly if you added the name of directory for each
> instance.
I have listed this in the pg_upgrade command execution, since it is
mentioned there I have not added here too.
> 5.
> You did not write the initialization of new node. Was it intentional?
Added it now
> 6.
> ```
> + <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>,
> + for e.g.:
> +<programlisting>
> +node2=# ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
> +ALTER SUBSCRIPTION
> +node2=# ALTER SUBSCRIPTION sub2_node1_node2 DISABLE;
> +ALTER SUBSCRIPTION
> +</programlisting>
> + </para>
> ```
>
> Subscriptions are disabled after stopping a publisher, but it leads ERRORs on
> the publisher.
> I think it's better to swap these steps.
Modified
> 7.
> ```
> +<programlisting>
> +dba@node1:/opt/PostgreSQL/postgres/&majorversion;/bin$ pg_ctl -D
> /opt/PostgreSQL/pub_data stop -l logfile
> +</programlisting>
> ```
>
> Hmm. I thought you did not have to show the current directory. You were in the
> bin dir, but it is not our requirement, right?
I kept this just to show the version being used
> 8.
> ```
> +<programlisting>
> +dba@node1:/opt/PostgreSQL/postgres/&majorversion;/bin$ pg_upgrade
> + --old-datadir "/opt/PostgreSQL/postgres/17/pub_data"
> + --new-datadir
> "/opt/PostgreSQL/postgres/&majorversion;/pub_upgraded_data"
> + --old-bindir "/opt/PostgreSQL/postgres/17/bin"
> + --new-bindir "/opt/PostgreSQL/postgres/&majorversion;/bin"
> +</programlisting>
> ```
>
> For PG17, both old and new bindir look the same. Can we use 18 as new-bindir?
Modfied
> 9.
> ```
> + <para>
> + Create any tables that were created in <literal>node2</literal>
> + between step-2 and now, for e.g.:
> +<programlisting>
> +node2=# CREATE TABLE distributors (
> +node2(# did integer CONSTRAINT no_null NOT NULL,
> +node2(# name varchar(40) NOT NULL
> +node2(# );
> +CREATE TABLE
> +</programlisting>
> + </para>
> ```
>
> I think this SQLs must be done on node1, because it has not boot between
> step-2
> and step-7.
Modified
> 10.
> ```
> + <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>,
> + for 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 publications using
> + <link
> linkend="sql-altersubscription-params-refresh-publication"><command>ALTER
> SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
> + for 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>
> ```
>
> I was very confused the location where they would be really do. If my above
> comment is correct, should they be executed on node1 as well? Could you
> please all
> the notation again?
Modified
> 11.
> ```
> + <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>,
> + for e.g.:
> +<programlisting>
> +node2=# ALTER SUBSCRIPTION sub1_node2_node1 DISABLE;
> +ALTER SUBSCRIPTION
> +node2=# ALTER SUBSCRIPTION sub2_node2_node1 DISABLE;
> +ALTER SUBSCRIPTION
> +</programlisting>
> + </para>
> ```
>
> They should be on node1, but noted as node2.
Modified
> 12.
> ```
> + <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>,
> + for e.g.:
> +<programlisting>
> +node2=# ALTER SUBSCRIPTION sub1_node2_node1 ENABLE;
> +ALTER SUBSCRIPTION
> +node2=# ALTER SUBSCRIPTION sub2_node2_node1 ENABLE;
> +ALTER SUBSCRIPTION
> +</programlisting>
> + </para>
> ```
>
> You said that "enable all the subscription on node1", but SQLs are done on
> node2.
Modified
Thanks for the comments, the attached v2 version patch has the changes
for the same.
Regards,
Vignesh
From a38a839bf863a8da5232e0d7fb8c2e9594c01585 Mon Sep 17 00:00:00 2001
From: Vignesh C <[email protected]>
Date: Wed, 13 Dec 2023 14:11:58 +0530
Subject: [PATCH v2] Documentation for upgrading logical replication cluster.
Documentation for upgrading logical replication cluster.
---
doc/src/sgml/logical-replication.sgml | 806 ++++++++++++++++++++++++++
doc/src/sgml/ref/pgupgrade.sgml | 133 +----
2 files changed, 816 insertions(+), 123 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index ec2130669e..c055a58d01 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -1926,6 +1926,812 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
</sect1>
+ <sect1 id="logical-replication-upgrade">
+ <title>Upgrade</title>
+
+ <procedure>
+ <step id="prepare-publisher-upgrades">
+ <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>conflicting</structfield>
+ is <literal>true</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 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.
+ <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 id="upgrading-logical-replication-cluster">
+ <title>Upgrading logical replication cluster</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>
+
+ <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>
+ <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.
+ </para>
+ <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>
+ </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. Backups can be taken
+ 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>
+ <link linkend="steps-two-node-logical-replication-cluster">Two-node logical replication cluster.</link>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <link linkend="steps-cascaded-logical-replication-cluster">Cascaded logical replication cluster.</link>
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <link linkend="steps-two-node-circular-logical-replication-cluster">Two-node circular logical replication cluster.</link>
+ </para>
+ </listitem>
+ </itemizedlist>
+ </para>
+
+ <procedure>
+ <step 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 sub1_node1_node2 and sub2_node1_node2 which is
+ 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>
+dba@node1:/opt/PostgreSQL/postgres/17/bin$ pg_ctl -D /opt/PostgreSQL/data1 stop -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize data1_upgraded 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>
+dba@node1:/opt/PostgreSQL/postgres/18/bin$ 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>
+dba@node1:/opt/PostgreSQL/postgres/18/bin$ 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>
+dba@node2:/opt/PostgreSQL/postgres/17/bin$ pg_ctl -D /opt/PostgreSQL/data2 stop -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize data2_upgraded 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>
+dba@node2:/opt/PostgreSQL/postgres/18/bin$ 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>
+dba@node2:/opt/PostgreSQL/postgres/18/bin$ 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
+ <link linkend="two-node-cluster-disable-subscriptions-node2">
+ when the subscriptions where disabled in <literal>node2</literal></link>
+ 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>
+
+ </step>
+ </procedure>
+
+ <procedure>
+ <step id="steps-cascaded-logical-replication-cluster">
+ <title>Steps to upgrade a cascaded logical replication clusters</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 sub1_node1_node2 and sub2_node1_node2 which is
+ subscribing the changes from <literal>node1</literal>. The
+ <literal>node3</literal> has two subscriptions sub1_node2_node3 and
+ sub2_node2_node3 which is 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>
+dba@node1:/opt/PostgreSQL/postgres/17/bin$ pg_ctl -D /opt/PostgreSQL/data1 stop -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize data1_upgraded 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>
+dba@node1:/opt/PostgreSQL/postgres/18/bin$ 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>
+dba@node1:/opt/PostgreSQL/postgres/18/bin$ pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para id="cascaded-cluster-disable-sub-node2-node3">
+ 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>
+dba@node2:/opt/PostgreSQL/postgres/17/bin$ pg_ctl -D /opt/PostgreSQL/data2 stop -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize data2_upgraded 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>
+dba@node2:/opt/PostgreSQL/postgres/18/bin$ 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>
+dba@node2:/opt/PostgreSQL/postgres/18/bin$ 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
+ <link linkend="cascaded-cluster-disable-sub-node1-node2">
+ when the subscriptions where disabled in <literal>node2</literal></link>
+ 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>
+dba@node3:/opt/PostgreSQL/postgres/17/bin$ pg_ctl -D /opt/PostgreSQL/data3 stop -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize data3_upgraded 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>
+dba@node3:/opt/PostgreSQL/postgres/18/bin$ 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>
+dba@node3:/opt/PostgreSQL/postgres/18/bin$ 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
+ <link linkend="cascaded-cluster-disable-sub-node2-node3">when the
+ subscriptions where disabled in <literal>node3</literal></link>
+ 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>
+
+ </step>
+ </procedure>
+
+ <procedure>
+ <step 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 sub1_node2_node1 and sub2_node2_node1 which is
+ subscribing the changes from <literal>node2</literal>. The
+ <literal>node2</literal> has two subscriptions sub1_node1_node2 and
+ sub2_node1_node2 which is 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>
+dba@node1:/opt/PostgreSQL/postgres/17/bin$ pg_ctl -D /opt/PostgreSQL/data1 stop -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize data1_upgraded 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>
+dba@node1:/opt/PostgreSQL/postgres/18/bin$ 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>
+dba@node1:/opt/PostgreSQL/postgres/18/bin$ 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 <link linkend="circular-cluster-disable-sub-node2">
+ when the subscriptions where disabled in <literal>node2</literal></link>
+ 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>
+dba@node2:/opt/PostgreSQL/postgres/17/bin$ pg_ctl -D /opt/PostgreSQL/data2 stop -l logfile
+</programlisting>
+ </para>
+ </step>
+
+ <step>
+ <para>
+ Initialize data2_upgraded 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>
+dba@node2:/opt/PostgreSQL/postgres/18/bin$ 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>
+dba@node2:/opt/PostgreSQL/postgres/18/bin$ 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 <link linkend="circular-cluster-disable-sub-node1">
+ when the subscriptions where disabled in <literal>node1</literal></link>
+ 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>
+
+ </step>
+ </procedure>
+ </step>
+
+ </procedure>
+ </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 87be1fb1c2..fd8e01fa2c 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -383,129 +383,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>conflicting</structfield>
- is <literal>true</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>
@@ -777,6 +654,16 @@ rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tb
</step>
+ <step id="pgupgrade-step-logical-replication">
+ <title>Upgrade logical replication clusters</title>
+
+ <para>
+ Refer <link linkend="logical-replication-upgrade">logical replication upgrade section</link>
+ for details on upgrading logical replication clusters.
+ </para>
+
+ </step>
+
<step>
<title>Restore <filename>pg_hba.conf</filename></title>
--
2.34.1
