v6-0001-create-subscriptipon-pgdocs-for-deferred-slot-cre.patch

From 5ad653a9e4cc1485b16b1b2886350f69d0e772cc Mon Sep 17 00:00:00 2001
From: Peter Smith <peter.b.smith@fujitsu.com>
Date: Tue, 18 Oct 2022 20:27:59 +1100
Subject: [PATCH v6] create subscriptipon - pgdocs for deferred slot creation.

New documentation describing how to activate a subscription which was originally
created in a disconnected mode.

The new docs/examples are added as part of "Logical Replication - Subscription"
section 31.2.

The CREATE SUBSCRIPTION reference page is also updated to include links to it.
---
 doc/src/sgml/logical-replication.sgml     | 161 +++++++++++++++++++++++++++++-
 doc/src/sgml/ref/create_subscription.sgml |  34 ++++---
 2 files changed, 181 insertions(+), 14 deletions(-)

diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index e98538e..a5567cc 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -320,7 +320,7 @@
   </sect2>
 
   <sect2 id="logical-replication-subscription-examples">
-    <title>Examples</title>
+    <title>Examples - Normal Usage</title>
 
     <para>
      Create some test tables on the publisher.
@@ -512,6 +512,165 @@ test_sub=# SELECT * FROM t3;
 </programlisting></para>
   </sect2>
 
+  <sect2 id="logical-replication-subscription-examples-deferred-slot">
+   <title>Examples - Deferred Replication Slot Creation</title>
+
+   <para>
+    There are some cases (e.g.
+    <xref linkend="logical-replication-subscription-slot"/>) where, if the
+    remote replication slot was not created automatically, the user must create
+    it manually before the subscription can be activated. The steps to create
+    the slot and activate the subscription are shown in the following examples.
+    These examples specify the standard logical decoding plugin
+    (<literal>pgoutput</literal>), which is what the built-in logical
+    replication uses.
+   </para>
+   <para>
+    First, create a publication for the examples to use.
+<programlisting>
+test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES;
+CREATE PUBLICATION
+</programlisting></para>
+   <para>
+    Example 1: Where the subscription says <literal>connect = false</literal>
+   </para>
+   <para>
+    <itemizedlist>
+     <listitem>
+      <para>
+       Create the subscription.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (connect=false);
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+     </listitem>
+     <listitem>
+      <para>
+       On the publisher, manually create a slot. Because the name was not
+       specified during <literal>CREATE SUBSCRIPTION</literal>, the name of the
+       slot to create is same as the subscription name, e.g. "sub1".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput');
+ slot_name |    lsn
+-----------+-----------
+ sub1      | 0/19404D0
+(1 row)
+</programlisting></para>
+     </listitem>
+     <listitem>
+      <para>
+       On the subscriber, complete the activation of the subscription. After
+       this the tables of <literal>pub1</literal> will start replicating.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+     </listitem>
+    </itemizedlist>
+   </para>
+
+   <para>
+    Example 2: Where the subscription says <literal>connect = false</literal>,
+    but also specifies the <literal>slot_name</literal>
+    <itemizedlist>
+     <listitem>
+      <para>
+       Create the subscription.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (connect=false, slot_name='myslot');
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+     </listitem>
+     <listitem>
+      <para>
+       On the publisher, manually create a slot using the same name that was
+       specified during <literal>CREATE SUBSCRIPTION</literal>, e.g. "myslot".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+ slot_name |    lsn
+-----------+-----------
+ myslot    | 0/19059A0
+(1 row)
+</programlisting></para>
+     </listitem>
+     <listitem>
+      <para>
+       On the subscriber, the remaining subscription activation steps are the
+       same as before.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+     </listitem>
+    </itemizedlist>
+   </para>
+
+   <para>
+    Example 3: Where the subscription specifies <literal>slot_name = NONE</literal>
+    <itemizedlist>
+     <listitem>
+      <para>
+       Create the subscription. When <literal>slot_name = NONE</literal> then
+       <literal>enabled = false</literal>, and
+       <literal>create_slot = false</literal> are also needed.
+<programlisting>
+test_sub=# CREATE SUBSCRIPTION sub1
+test_sub-# CONNECTION 'host=localhost dbname=test_pub'
+test_sub-# PUBLICATION pub1
+test_sub-# WITH (slot_name=NONE, enabled=false, create_slot=false);
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
+CREATE SUBSCRIPTION
+</programlisting></para>
+     </listitem>
+     <listitem>
+      <para>
+       On the publisher, manually create a slot using any name, e.g. "myslot".
+<programlisting>
+test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
+ slot_name |    lsn
+-----------+-----------
+ myslot    | 0/1905930
+(1 row)
+</programlisting></para>
+     </listitem>
+     <listitem>
+      <para>
+       On the subscriber, associate the subscription with the slot name just
+       created.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 SET (slot_name='myslot');
+ALTER SUBSCRIPTION
+</programlisting></para>
+     </listitem>
+     <listitem>
+      <para>
+       The remaining subscription activation steps are same as before.
+<programlisting>
+test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
+ALTER SUBSCRIPTION
+test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION
+</programlisting></para>
+     </listitem>
+    </itemizedlist>
+   </para>
+  </sect2>
+
  </sect1>
 
  <sect1 id="logical-replication-row-filter">
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index bd12e71..f9a1776 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -120,11 +120,11 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
 
          <para>
           Since no connection is made when this option is
-          <literal>false</literal>, no tables are subscribed, and so
-          after you enable the subscription nothing will be replicated.
-          You will need to then run
-          <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal>
-          for tables to be subscribed.
+          <literal>false</literal>, no tables are subscribed. To initiate
+          replication, you must manually create the replication slot, enable
+          the subscription, and refresh the subscription. See
+          <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
+          for examples.
          </para>
         </listitem>
        </varlistentry>
@@ -135,8 +135,12 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
          <para>
           Specifies whether the command should create the replication slot on
           the publisher.  The default is <literal>true</literal>.
+         </para>
+         <para>
           If set to <literal>false</literal>, you are responsible for
-          creating the publisher's slot in some other way.
+          creating the publisher's slot in some other way. See
+          <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
+          for examples.
          </para>
         </listitem>
        </varlistentry>
@@ -162,11 +166,13 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
 
          <para>
           Setting <literal>slot_name</literal> to <literal>NONE</literal>
-          means there will be no replication slot
-          associated with the subscription.  Use this when you will be
-          creating the replication slot later manually.  Such
-          subscriptions must also have both <literal>enabled</literal> and
-          <literal>create_slot</literal> set to <literal>false</literal>.
+          means there will be no replication slot associated with the
+          subscription. Such subscriptions must also have both
+          <literal>enabled</literal> and <literal>create_slot</literal> set to
+          <literal>false</literal>.  Use this when you will be creating the
+          replication slot later manually. See
+          <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
+          for examples.
          </para>
         </listitem>
        </varlistentry>
@@ -357,8 +363,10 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
    replication slot separately (using the
    function <function>pg_create_logical_replication_slot</function> with the
    plugin name <literal>pgoutput</literal>) and create the subscription using
-   the parameter <literal>create_slot = false</literal>.  This is an
-   implementation restriction that might be lifted in a future release.
+   the parameter <literal>create_slot = false</literal>.  See
+   <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
+   for examples. This is an implementation restriction that might be lifted in a
+   future release.
   </para>
 
   <para>
-- 
1.8.3.1