Skip to content

Commit 155601d

Browse files
author
Amit Kapila
committed
Doc: Update information about manually creating slots.
There are some cases (e.g. when the subscription is created using the connect = false option) where the remote replication slot was not created automatically and the user must create it manually before the subscription can be activated. There was not enough information in the docs for users to do this easily. Author: Peter Smith Reviewd by: Shi yu, Amit Kapila Discussion: https://postgr.es/m/CAHut+PvqdqOanheWSHDyhQiF+Z-7w=-+k4U+bwbT=b6YQ_hrXQ@mail.gmail.com
1 parent 568546f commit 155601d

File tree

2 files changed

+179
-14
lines changed

2 files changed

+179
-14
lines changed

‎doc/src/sgml/logical-replication.sgml

+158-1
Original file line numberDiff line numberDiff line change
@@ -320,7 +320,7 @@
320320
</sect2>
321321

322322
<sect2 id="logical-replication-subscription-examples">
323-
<title>Examples</title>
323+
<title>Examples - Setup Logical Replication</title>
324324

325325
<para>
326326
Create some test tables on the publisher.
@@ -512,6 +512,163 @@ test_sub=# SELECT * FROM t3;
512512
</programlisting></para>
513513
</sect2>
514514

515+
<sect2 id="logical-replication-subscription-examples-deferred-slot">
516+
<title>Examples - Deferred Replication Slot Creation</title>
517+
518+
<para>
519+
There are some cases (e.g.
520+
<xref linkend="logical-replication-subscription-slot"/>) where, if the
521+
remote replication slot was not created automatically, the user must create
522+
it manually before the subscription can be activated. The steps to create
523+
the slot and activate the subscription are shown in the following examples.
524+
These examples specify the standard logical decoding plugin
525+
(<literal>pgoutput</literal>), which is what the built-in logical
526+
replication uses.
527+
</para>
528+
<para>
529+
First, create a publication for the examples to use.
530+
<programlisting>
531+
test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES;
532+
CREATE PUBLICATION
533+
</programlisting></para>
534+
<para>
535+
Example 1: Where the subscription says <literal>connect = false</literal>
536+
</para>
537+
<para>
538+
<itemizedlist>
539+
<listitem>
540+
<para>
541+
Create the subscription.
542+
<programlisting>
543+
test_sub=# CREATE SUBSCRIPTION sub1
544+
test_sub-# CONNECTION 'host=localhost dbname=test_pub'
545+
test_sub-# PUBLICATION pub1
546+
test_sub-# WITH (connect=false);
547+
WARNING: subscription was created, but is not connected
548+
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
549+
CREATE SUBSCRIPTION
550+
</programlisting></para>
551+
</listitem>
552+
<listitem>
553+
<para>
554+
On the publisher, manually create a slot. Because the name was not
555+
specified during <literal>CREATE SUBSCRIPTION</literal>, the name of the
556+
slot to create is same as the subscription name, e.g. "sub1".
557+
<programlisting>
558+
test_pub=# SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput');
559+
slot_name | lsn
560+
-----------+-----------
561+
sub1 | 0/19404D0
562+
(1 row)
563+
</programlisting></para>
564+
</listitem>
565+
<listitem>
566+
<para>
567+
On the subscriber, complete the activation of the subscription. After
568+
this the tables of <literal>pub1</literal> will start replicating.
569+
<programlisting>
570+
test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
571+
ALTER SUBSCRIPTION
572+
test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
573+
ALTER SUBSCRIPTION
574+
</programlisting></para>
575+
</listitem>
576+
</itemizedlist>
577+
</para>
578+
579+
<para>
580+
Example 2: Where the subscription says <literal>connect = false</literal>,
581+
but also specifies the <literal>slot_name</literal>
582+
<itemizedlist>
583+
<listitem>
584+
<para>
585+
Create the subscription.
586+
<programlisting>
587+
test_sub=# CREATE SUBSCRIPTION sub1
588+
test_sub-# CONNECTION 'host=localhost dbname=test_pub'
589+
test_sub-# PUBLICATION pub1
590+
test_sub-# WITH (connect=false, slot_name='myslot');
591+
WARNING: subscription was created, but is not connected
592+
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
593+
CREATE SUBSCRIPTION
594+
</programlisting></para>
595+
</listitem>
596+
<listitem>
597+
<para>
598+
On the publisher, manually create a slot using the same name that was
599+
specified during <literal>CREATE SUBSCRIPTION</literal>, e.g. "myslot".
600+
<programlisting>
601+
test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
602+
slot_name | lsn
603+
-----------+-----------
604+
myslot | 0/19059A0
605+
(1 row)
606+
</programlisting></para>
607+
</listitem>
608+
<listitem>
609+
<para>
610+
On the subscriber, the remaining subscription activation steps are the
611+
same as before.
612+
<programlisting>
613+
test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
614+
ALTER SUBSCRIPTION
615+
test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
616+
ALTER SUBSCRIPTION
617+
</programlisting></para>
618+
</listitem>
619+
</itemizedlist>
620+
</para>
621+
622+
<para>
623+
Example 3: Where the subscription specifies <literal>slot_name = NONE</literal>
624+
<itemizedlist>
625+
<listitem>
626+
<para>
627+
Create the subscription. When <literal>slot_name = NONE</literal> then
628+
<literal>enabled = false</literal>, and
629+
<literal>create_slot = false</literal> are also needed.
630+
<programlisting>
631+
test_sub=# CREATE SUBSCRIPTION sub1
632+
test_sub-# CONNECTION 'host=localhost dbname=test_pub'
633+
test_sub-# PUBLICATION pub1
634+
test_sub-# WITH (slot_name=NONE, enabled=false, create_slot=false);
635+
CREATE SUBSCRIPTION
636+
</programlisting></para>
637+
</listitem>
638+
<listitem>
639+
<para>
640+
On the publisher, manually create a slot using any name, e.g. "myslot".
641+
<programlisting>
642+
test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
643+
slot_name | lsn
644+
-----------+-----------
645+
myslot | 0/1905930
646+
(1 row)
647+
</programlisting></para>
648+
</listitem>
649+
<listitem>
650+
<para>
651+
On the subscriber, associate the subscription with the slot name just
652+
created.
653+
<programlisting>
654+
test_sub=# ALTER SUBSCRIPTION sub1 SET (slot_name='myslot');
655+
ALTER SUBSCRIPTION
656+
</programlisting></para>
657+
</listitem>
658+
<listitem>
659+
<para>
660+
The remaining subscription activation steps are same as before.
661+
<programlisting>
662+
test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
663+
ALTER SUBSCRIPTION
664+
test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
665+
ALTER SUBSCRIPTION
666+
</programlisting></para>
667+
</listitem>
668+
</itemizedlist>
669+
</para>
670+
</sect2>
671+
515672
</sect1>
516673

517674
<sect1 id="logical-replication-row-filter">

‎doc/src/sgml/ref/create_subscription.sgml

+21-13
Original file line numberDiff line numberDiff line change
@@ -120,11 +120,11 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
120120

121121
<para>
122122
Since no connection is made when this option is
123-
<literal>false</literal>, no tables are subscribed, and so
124-
after you enable the subscription nothing will be replicated.
125-
You will need to then run
126-
<literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal>
127-
for tables to be subscribed.
123+
<literal>false</literal>, no tables are subscribed. To initiate
124+
replication, you must manually create the replication slot, enable
125+
the subscription, and refresh the subscription. See
126+
<xref linkend="logical-replication-subscription-examples-deferred-slot"/>
127+
for examples.
128128
</para>
129129
</listitem>
130130
</varlistentry>
@@ -135,8 +135,12 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
135135
<para>
136136
Specifies whether the command should create the replication slot on
137137
the publisher. The default is <literal>true</literal>.
138+
</para>
139+
<para>
138140
If set to <literal>false</literal>, you are responsible for
139-
creating the publisher's slot in some other way.
141+
creating the publisher's slot in some other way. See
142+
<xref linkend="logical-replication-subscription-examples-deferred-slot"/>
143+
for examples.
140144
</para>
141145
</listitem>
142146
</varlistentry>
@@ -162,11 +166,13 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
162166

163167
<para>
164168
Setting <literal>slot_name</literal> to <literal>NONE</literal>
165-
means there will be no replication slot
166-
associated with the subscription. Use this when you will be
167-
creating the replication slot later manually. Such
168-
subscriptions must also have both <literal>enabled</literal> and
169-
<literal>create_slot</literal> set to <literal>false</literal>.
169+
means there will be no replication slot associated with the
170+
subscription. Such subscriptions must also have both
171+
<literal>enabled</literal> and <literal>create_slot</literal> set to
172+
<literal>false</literal>. Use this when you will be creating the
173+
replication slot later manually. See
174+
<xref linkend="logical-replication-subscription-examples-deferred-slot"/>
175+
for examples.
170176
</para>
171177
</listitem>
172178
</varlistentry>
@@ -357,8 +363,10 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
357363
replication slot separately (using the
358364
function <function>pg_create_logical_replication_slot</function> with the
359365
plugin name <literal>pgoutput</literal>) and create the subscription using
360-
the parameter <literal>create_slot = false</literal>. This is an
361-
implementation restriction that might be lifted in a future release.
366+
the parameter <literal>create_slot = false</literal>. See
367+
<xref linkend="logical-replication-subscription-examples-deferred-slot"/>
368+
for examples. This is an implementation restriction that might be lifted in a
369+
future release.
362370
</para>
363371

364372
<para>

0 commit comments

Comments
 (0)