Re: Documentation to upgrade logical replication cluster

On Tue, 24 Sept 2024 at 16:20, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> On Fri, Sep 20, 2024 at 5:46 PM vignesh C <vignesh21@gmail.com> wrote:
> >
> > I didn’t include a note because each disable/enable statement
> > specifies: a) Disable all subscriptions on the node, b) Enable all
> > subscriptions on the node. The attached v11 version patch just to show
> > the examples with one subscription.
> >
>
> The following steps in the bi-directional node upgrade have some problems.
>
> +       <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
> +</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
> +</programlisting>
> +       </para>
>
> If you are creating missing tables on node-1, won't that node's
> subscription be refreshed to get the missing data? Also, I suggest
> moving the step-2 in the above steps to enable subscriptions on node-2
> should be moved before creating a table on node-1 and then issuing a
> REFRESH command on node-1. The similar steps for other node's upgrade
> following these steps have similar problems.

Reordered the docs to enable the subscription before creating the
table. For bi-directional replication, a publication refresh is
necessary on both nodes: a) First, refresh the publication on the old
version server to set the newly added tables to a ready state in the
pg_subscription_rel catalog. b) Next, refresh the publication on the
upgraded version server to initiate the initial sync and update the
pg_subscription_rel with the ready state. This change has been
incorporated into the attached v12 version patch.

Regards,
Vignesh