On Mon, 15 Jan 2024 at 09:01, Peter Smith <smithpb2...@gmail.com> wrote:
>
> Hi Vignesh, here are some review comments for patch v2-0001.
>
> ======
> doc/src/sgml/ref/pgupgrade.sgml
>
> 1.
> + <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>
> +
>
> This renders like:
> Refer logical replication upgrade section for details on upgrading
> logical replication clusters.
>
> ~
>
> IMO it would be better to use xref instead of link, which will render
> more normally like:
> See Section 30.11 for details on upgrading logical replication clusters.
>
> SUGGESTION
> <para>
> See <xref linkend="logical-replication-upgrade"/>
> for details on upgrading logical replication clusters.
> </para>
Modified
> ======
> doc/src/sgml/logical-replication.sgml
>
> 2. GENERAL - blurb
>
> + <sect1 id="logical-replication-upgrade">
> + <title>Upgrade</title>
> +
> + <procedure>
> + <step id="prepare-publisher-upgrades">
> + <title>Prepare for publisher upgrades</title>
>
> I felt there should be a short (1 or 2 sentence) general blurb about
> pub/sub upgrade before jumping straight into:
>
> "1. Prepare for publisher upgrades"
> "2. Prepare for subscriber upgrades"
> "3. Upgrading logical replication cluster"
Added
> ~
>
> Specifically, at first, it looks strange that the HTML renders as
> steps 1,2,3 instead of sub-sections (30.11.1, 30.11.2, 30.11.3); Maybe
> "steps" are fine, but then at least there needs to be some intro
> sentence saying like "follow these steps:"
> ~~~
Modified
>
> 3.
> + <step id="upgrading-logical-replication-cluster">
> + <title>Upgrading logical replication cluster</title>
>
> /cluster/clusters/
Modified
> ~~~
>
> 4.
> + <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>
>
> Isn't there a better way to accomplish this by using xref and
> 'xreflabel' so you don't have to type the link text here?
Modified
>
> //////////
> Steps to upgrade a two-node logical replication cluster
> //////////
>
> 5.
> + <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>
>
> 5a
> Those subscription names should also be rendered as literals.
Modified
> ~
>
> 5b
> /which is/which are/
Modified
> ~~~
>
> 6.
> + <step>
> + <para>
> + Initialize data1_upgraded instance by using the required newer
> + version.
> + </para>
> + </step>
>
> data1_upgraded should be rendered as literal.
Modified
> ~~~
>
> 7.
> +
> + <step>
> + <para>
> + Initialize data2_upgraded instance by using the required newer
> + version.
> + </para>
> + </step>
>
> data2_upgraded should be rendered as literal.
Modified
> ~~~
>
> 8.
> +
> + <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>
>
> 8a.
> This link to the earlier step renders badly like:
> On node2, create any tables that were created in the upgraded
> publisher node1 server between when the subscriptions where disabled
> in node2 and now, e.g.:
>
> IMO this link should be like "Step N", not some words -- maybe it is
> another opportunity for using xreflabel?
Modified
> ~
>
> 8b.
> Also has typos "when the subscriptions where disabled" (??)
This is not required after using xref, removed it.
> //////////
> Steps to upgrade a cascaded logical replication clusters
> //////////
>
> 9.
> + <procedure>
> + <step id="steps-cascaded-logical-replication-cluster">
> + <title>Steps to upgrade a cascaded logical replication clusters</title>
>
> The title has a strange mix of singular "a" and plural "clusters"
Changed it to keep it consistent
> ~~~
>
> 10.
> + <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>
>
> 10a.
> Those subscription names should also be rendered as literals.
Modified
> ~
>
> 10b.
> /which is/which are/ (occurs 2x)
Modified
> ~~~
>
> 11.
> +
> + <step>
> + <para>
> + Initialize data1_upgraded instance by using the required
> newer version.
> + </para>
> + </step>
>
> data1_upgraded should be rendered as literal.
Modified
> ~~~
>
> 12.
> +
> + <step>
> + <para>
> + Initialize data2_upgraded instance by using the required
> newer version.
> + </para>
> + </step>
>
> data2_upgraded should be rendered as literal.
Modified
> ~~~
>
> 13.
> +
> + <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>
>
> 13a.
> This link to the earlier step renders badly like:
> On node2, create any tables that were created in the upgraded
> publisher node1 server between when the subscriptions where disabled
> in node2 and now, e.g.:
>
> IMO this link should be like "Step N", not some words -- maybe it is
> another opportunity for using xreflabel?
Modified
> ~
>
> 13b
> Also has typos "when the subscriptions where disabled" (??)
This is not required after using xref, removed it.
> ~~~
>
> 14.
> +
> + <step>
> + <para>
> + Initialize data3_upgraded instance by using the required
> newer version.
> + </para>
> + </step>
>
> data3_upgraded should be rendered as literal.
Modified
> ~~~
>
> 15.
> +
> + <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>
>
> 15a.
> This link to the earlier step renders badly like:
> On node3, create any tables that were created in the upgraded node2
> between when the subscriptions where disabled in node3 and now, e.g.:
Changed it to xref.
> ~
>
> 15b.
> Also has typos "when the subscriptions where disabled" (??)
This is not required after using xref, removed it.
> //////////
> Steps to upgrade a two-node circular logical replication cluster
> //////////
>
> 16.
> + <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>
>
> 16a
> Those subscription names should also be rendered as literals.
Modified
> ~
>
> 16b
> /which is/which are/
Modified
> ~~~
>
> 17.
> +
> + <step>
> + <para>
> + Initialize data1_upgraded instance by using the required newer
> + version.
> + </para>
> + </step>
>
> data1_upgraded should render as literal.
Modified
> ~~~
>
> 18.
> +
> + <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>
>
> 18a.
> This link to the earlier step renders badly like:
> On node1, Create any tables that were created in node2 between when
> the subscriptions where disabled in node2 and now, e.g.:
>
> IMO this link should be like "Step N", not some words -- maybe it is
> another opportunity for using xreflabel?
Modified to xref
>
> 18b
> Also has typos "when the subscriptions where disabled" (??)
This is not required after using xref, removed it.
> ~
>
> 18c.
> /Create any/create any/
Modified
> ~~~
>
> 19.
> +
> + <step>
> + <para>
> + Initialize data2_upgraded instance by using the required newer
> + version.
> + </para>
> + </step>
>
> data2_upgraded should render as literal.
Modified
> ~~~
>
> 20.
> +
> + <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>
>
> 20a.
> This link to the earlier step renders badly like:
> On node2, Create any tables that were created in the upgraded node1
> between when the subscriptions where disabled in node1 and now, e.g.:
Modified to xref
> ~
>
> 20b
> Also has typos "when the subscriptions where disabled" (??)
This is not required after using xref, removed it.
Thanks for the comments, the attached v3 version patch has the changes
for the same.
Regards,
Vignesh
From 1278510840097eece044b3c6505242e684bd3ff6 Mon Sep 17 00:00:00 2001
From: Vignesh C <vignes...@gmail.com>
Date: Wed, 13 Dec 2023 14:11:58 +0530
Subject: [PATCH v3] 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 | 136 +----
2 files changed, 816 insertions(+), 126 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index ec2130669e..a2945ca781 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>
+
+ <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.
+ Before reading this section, refer <xref linkend="pgupgrade"/> page for
+ more details about pg_upgrade.
+ </para>
+
+ <sect2 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>
+ </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.
+ <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>
+ </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 upgradation is completed.
+ </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>
+ 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 -l logfile
+</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 -l logfile
+</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 -l logfile
+</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 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>
+pg_ctl -D /opt/PostgreSQL/data2 stop -l logfile
+</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 -l logfile
+</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 -l logfile
+</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 -l logfile
+</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 87be1fb1c2..b7909d5687 100644
--- a/doc/src/sgml/ref/pgupgrade.sgml
+++ b/doc/src/sgml/ref/pgupgrade.sgml
@@ -70,6 +70,13 @@ 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>
+
+ <para>
+ This page does not cover steps to upgrade logical replication clusters, refer
+ <xref linkend="logical-replication-upgrade"/> for details on upgrading
+ logical replication clusters.
+ </para>
+
</refsect1>
<refsect1>
@@ -383,129 +390,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>
@@ -767,9 +651,9 @@ 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.) In version 17.0 or later, 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
