Debian Codegraph @

doc/src/sgml/ref/create_subscription.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_subscription.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createsubscription">
7 <indexterm zone="sql-createsubscription">
8 <primary>CREATE SUBSCRIPTION</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE SUBSCRIPTION</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE SUBSCRIPTION</refname>
19 <refpurpose>define a new subscription</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceable>
25 CONNECTION '<replaceable class="parameter">conninfo</replaceable>'
26 PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...]
27 [ WITH ( <replaceable class="parameter">subscription_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
28 </synopsis>
29 </refsynopsisdiv>
30
31 <refsect1>
32 <title>Description</title>
33
34 <para>
35 <command>CREATE SUBSCRIPTION</command> adds a new logical-replication
36 subscription. The user that creates a subscription becomes the owner
37 of the subscription. The subscription name must be distinct from the name of
38 any existing subscription in the current database.
39 </para>
40
41 <para>
42 A subscription represents a replication connection to the publisher.
43 Hence, in addition to adding definitions in the local catalogs, this
44 command normally creates a replication slot on the publisher.
45 </para>
46
47 <para>
48 A logical replication worker will be started to replicate data for the new
49 subscription at the commit of the transaction where this command is run,
50 unless the subscription is initially disabled.
51 </para>
52
53 <para>
54 To be able to create a subscription, you must have the privileges of
55 the <literal>pg_create_subscription</literal> role, as well as
56 <literal>CREATE</literal> privileges on the current database.
57 </para>
58
59 <para>
60 Additional information about subscriptions and logical replication as a
61 whole is available at <xref linkend="logical-replication-subscription"/> and
62 <xref linkend="logical-replication"/>.
63 </para>
64
65 </refsect1>
66
67 <refsect1>
68 <title>Parameters</title>
69
70 <variablelist>
71 <varlistentry id="sql-createsubscription-params-name">
72 <term><replaceable class="parameter">subscription_name</replaceable></term>
73 <listitem>
74 <para>
75 The name of the new subscription.
76 </para>
77 </listitem>
78 </varlistentry>
79
80 <varlistentry id="sql-createsubscription-params-connection">
81 <term><literal>CONNECTION '<replaceable class="parameter">conninfo</replaceable>'</literal></term>
82 <listitem>
83 <para>
84 The <application>libpq</application> connection string defining how
85 to connect to the publisher database. For details see
86 <xref linkend="libpq-connstring"/>.
87 </para>
88 </listitem>
89 </varlistentry>
90
91 <varlistentry id="sql-createsubscription-params-publication">
92 <term><literal>PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...]</literal></term>
93 <listitem>
94 <para>
95 Names of the publications on the publisher to subscribe to.
96 </para>
97 </listitem>
98 </varlistentry>
99
100 <varlistentry id="sql-createsubscription-params-with">
101 <term><literal>WITH ( <replaceable class="parameter">subscription_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
102 <listitem>
103 <para>
104 This clause specifies optional parameters for a subscription.
105 </para>
106
107 <para>
108 The following parameters control what happens during subscription creation:
109
110 <variablelist>
111
112 <varlistentry id="sql-createsubscription-params-with-connect">
113 <term><literal>connect</literal> (<type>boolean</type>)</term>
114 <listitem>
115 <para>
116 Specifies whether the <command>CREATE SUBSCRIPTION</command>
117 command should connect to the publisher at all. The default
118 is <literal>true</literal>. Setting this to
119 <literal>false</literal> will force the values of
120 <literal>create_slot</literal>, <literal>enabled</literal> and
121 <literal>copy_data</literal> to <literal>false</literal>.
122 (You cannot combine setting <literal>connect</literal>
123 to <literal>false</literal> with
124 setting <literal>create_slot</literal>, <literal>enabled</literal>,
125 or <literal>copy_data</literal> to <literal>true</literal>.)
126 </para>
127
128 <para>
129 Since no connection is made when this option is
130 <literal>false</literal>, no tables are subscribed. To initiate
131 replication, you must manually create the replication slot, enable
132 the failover if required, enable the subscription, and refresh the
133 subscription. See
134 <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
135 for examples.
136 </para>
137 </listitem>
138 </varlistentry>
139
140 <varlistentry id="sql-createsubscription-params-with-create-slot">
141 <term><literal>create_slot</literal> (<type>boolean</type>)</term>
142 <listitem>
143 <para>
144 Specifies whether the command should create the replication slot on
145 the publisher. The default is <literal>true</literal>.
146 </para>
147 <para>
148 If set to <literal>false</literal>, you are responsible for
149 creating the publisher's slot in some other way. See
150 <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
151 for examples.
152 </para>
153 </listitem>
154 </varlistentry>
155
156 <varlistentry id="sql-createsubscription-params-with-enabled">
157 <term><literal>enabled</literal> (<type>boolean</type>)</term>
158 <listitem>
159 <para>
160 Specifies whether the subscription should be actively replicating
161 or whether it should just be set up but not started yet. The default
162 is <literal>true</literal>.
163 </para>
164 </listitem>
165 </varlistentry>
166
167 <varlistentry id="sql-createsubscription-params-with-slot-name">
168 <term><literal>slot_name</literal> (<type>string</type>)</term>
169 <listitem>
170 <para>
171 Name of the publisher's replication slot to use. The default is
172 to use the name of the subscription for the slot name.
173 </para>
174
175 <para>
176 Setting <literal>slot_name</literal> to <literal>NONE</literal>
177 means there will be no replication slot associated with the
178 subscription. Such subscriptions must also have both
179 <literal>enabled</literal> and <literal>create_slot</literal> set to
180 <literal>false</literal>. Use this when you will be creating the
181 replication slot later manually. See
182 <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
183 for examples.
184 </para>
185
186 <para>
187 When setting <literal>slot_name</literal> to a valid name and
188 <literal>create_slot</literal> to <literal>false</literal>, the
189 <literal>failover</literal> property value of the named slot may
190 differ from the counterpart <literal>failover</literal> parameter
191 specified in the subscription. Always ensure the slot property
192 <literal>failover</literal> matches the counterpart parameter of the
193 subscription and vice versa. Otherwise, the slot on the publisher may
194 behave differently from what these subscription options say: for
195 example, the slot on the publisher could either be synced to the
196 standbys even when the subscription's <literal>failover</literal>
197 option is disabled or could be disabled for sync even when the
198 subscription's <literal>failover</literal> option is enabled.
199 </para>
200 </listitem>
201 </varlistentry>
202 </variablelist>
203 </para>
204
205 <para>
206 The following parameters control the subscription's replication
207 behavior after it has been created:
208
209 <variablelist>
210
211 <varlistentry id="sql-createsubscription-params-with-binary">
212 <term><literal>binary</literal> (<type>boolean</type>)</term>
213 <listitem>
214 <para>
215 Specifies whether the subscription will request the publisher to send
216 the data in binary format (as opposed to text). The default is
217 <literal>false</literal>. Any initial table synchronization copy
218 (see <literal>copy_data</literal>) also uses the same format. Binary
219 format can be faster than the text format, but it is less portable
220 across machine architectures and <productname>PostgreSQL</productname>
221 versions. Binary format is very data type specific; for example, it
222 will not allow copying from a <type>smallint</type> column to an
223 <type>integer</type> column, even though that would work fine in text
224 format. Even when this option is enabled, only data types having binary
225 send and receive functions will be transferred in binary. Note that
226 the initial synchronization requires all data types to have binary
227 send and receive functions, otherwise the synchronization will fail
228 (see <xref linkend="sql-createtype"/> for more about send/receive
229 functions).
230 </para>
231
232 <para>
233 When doing cross-version replication, it could be that the
234 publisher has a binary send function for some data type, but the
235 subscriber lacks a binary receive function for that type. In
236 such a case, data transfer will fail, and
237 the <literal>binary</literal> option cannot be used.
238 </para>
239
240 <para>
241 If the publisher is a <productname>PostgreSQL</productname> version
242 before 16, then any initial table synchronization will use text format
243 even if <literal>binary = true</literal>.
244 </para>
245 </listitem>
246 </varlistentry>
247
248 <varlistentry id="sql-createsubscription-params-with-copy-data">
249 <term><literal>copy_data</literal> (<type>boolean</type>)</term>
250 <listitem>
251 <para>
252 Specifies whether to copy pre-existing data in the publications
253 that are being subscribed to when the replication starts.
254 The default is <literal>true</literal>.
255 </para>
256 <para>
257 If the publications contain <literal>WHERE</literal> clauses, it
258 will affect what data is copied. Refer to the
259 <xref linkend="sql-createsubscription-notes" /> for details.
260 </para>
261 <para>
262 See <xref linkend="sql-createsubscription-notes"/> for details of how
263 <literal>copy_data = true</literal> can interact with the
264 <literal>origin</literal> parameter.
265 </para>
266 </listitem>
267 </varlistentry>
268
269 <varlistentry id="sql-createsubscription-params-with-streaming">
270 <term><literal>streaming</literal> (<type>enum</type>)</term>
271 <listitem>
272 <para>
273 Specifies whether to enable streaming of in-progress transactions
274 for this subscription. The default value is <literal>parallel</literal>,
275 meaning incoming changes are directly applied via one of the parallel
276 apply workers, if available. If no parallel apply worker is free to
277 handle streaming transactions then the changes are written to
278 temporary files and applied after the transaction is committed. Note
279 that if an error happens in a parallel apply worker, the finish LSN
280 of the remote transaction might not be reported in the server log.
281 </para>
282
283 <caution>
284 <para>
285 There is a risk of deadlock when the schemas of the publisher and
286 subscriber differ, although such cases are rare. The apply worker
287 is equipped to retry these transactions automatically.
288 </para>
289 </caution>
290
291 <para>
292 If set to <literal>on</literal>, the incoming changes are written to
293 temporary files and then applied only after the transaction is
294 committed on the publisher and received by the subscriber.
295 </para>
296
297 <para>
298 If set to <literal>off</literal>, all transactions are fully decoded
299 on the publisher and only then sent to the subscriber as a whole.
300 </para>
301 </listitem>
302 </varlistentry>
303
304 <varlistentry id="sql-createsubscription-params-with-synchronous-commit">
305 <term><literal>synchronous_commit</literal> (<type>enum</type>)</term>
306 <listitem>
307 <para>
308 The value of this parameter overrides the
309 <xref linkend="guc-synchronous-commit"/> setting within this
310 subscription's apply worker processes. The default value
311 is <literal>off</literal>.
312 </para>
313
314 <para>
315 It is safe to use <literal>off</literal> for logical replication:
316 If the subscriber loses transactions because of missing
317 synchronization, the data will be sent again from the publisher.
318 </para>
319
320 <para>
321 A different setting might be appropriate when doing synchronous
322 logical replication. The logical replication workers report the
323 positions of writes and flushes to the publisher, and when using
324 synchronous replication, the publisher will wait for the actual
325 flush. This means that setting
326 <literal>synchronous_commit</literal> for the subscriber to
327 <literal>off</literal> when the subscription is used for
328 synchronous replication might increase the latency for
329 <command>COMMIT</command> on the publisher. In this scenario, it
330 can be advantageous to set <literal>synchronous_commit</literal>
331 to <literal>local</literal> or higher.
332 </para>
333 </listitem>
334 </varlistentry>
335
336 <varlistentry id="sql-createsubscription-params-with-two-phase">
337 <term><literal>two_phase</literal> (<type>boolean</type>)</term>
338 <listitem>
339 <para>
340 Specifies whether two-phase commit is enabled for this subscription.
341 The default is <literal>false</literal>.
342 </para>
343
344 <para>
345 When two-phase commit is enabled, prepared transactions are sent
346 to the subscriber at the time of <command>PREPARE
347 TRANSACTION</command>, and are processed as two-phase
348 transactions on the subscriber too. Otherwise, prepared
349 transactions are sent to the subscriber only when committed, and
350 are then processed immediately by the subscriber.
351 </para>
352
353 <para>
354 The implementation of two-phase commit requires that replication
355 has successfully finished the initial table synchronization
356 phase. So even when <literal>two_phase</literal> is enabled for a
357 subscription, the internal two-phase state remains
358 temporarily <quote>pending</quote> until the initialization phase
359 completes. See column <structfield>subtwophasestate</structfield>
360 of <link linkend="catalog-pg-subscription"><structname>pg_subscription</structname></link>
361 to know the actual two-phase state.
362 </para>
363
364 </listitem>
365 </varlistentry>
366
367 <varlistentry id="sql-createsubscription-params-with-disable-on-error">
368 <term><literal>disable_on_error</literal> (<type>boolean</type>)</term>
369 <listitem>
370 <para>
371 Specifies whether the subscription should be automatically disabled
372 if any errors are detected by subscription workers during data
373 replication from the publisher. The default is
374 <literal>false</literal>.
375 </para>
376 </listitem>
377 </varlistentry>
378
379 <varlistentry id="sql-createsubscription-params-with-password-required">
380 <term><literal>password_required</literal> (<type>boolean</type>)</term>
381 <listitem>
382 <para>
383 If set to <literal>true</literal>, connections to the publisher made
384 as a result of this subscription must use password authentication
385 and the password must be specified as a part of the connection
386 string. This setting is ignored when the subscription is owned by a
387 superuser. The default is <literal>true</literal>. Only superusers
388 can set this value to <literal>false</literal>.
389 </para>
390 </listitem>
391 </varlistentry>
392
393 <varlistentry id="sql-createsubscription-params-with-run-as-owner">
394 <term><literal>run_as_owner</literal> (<type>boolean</type>)</term>
395 <listitem>
396 <para>
397 If true, all replication actions are performed as the subscription
398 owner. If false, replication workers will perform actions on each
399 table as the owner of that table. The latter configuration is
400 generally much more secure; for details, see
401 <xref linkend="logical-replication-security" />.
402 The default is <literal>false</literal>.
403 </para>
404 </listitem>
405 </varlistentry>
406
407 <varlistentry id="sql-createsubscription-params-with-origin">
408 <term><literal>origin</literal> (<type>string</type>)</term>
409 <listitem>
410 <para>
411 Specifies whether the subscription will request the publisher to only
412 send changes that don't have an origin or send changes regardless of
413 origin. Setting <literal>origin</literal> to <literal>none</literal>
414 means that the subscription will request the publisher to only send
415 changes that don't have an origin. Setting <literal>origin</literal>
416 to <literal>any</literal> means that the publisher sends changes
417 regardless of their origin. The default is <literal>any</literal>.
418 </para>
419 <para>
420 See <xref linkend="sql-createsubscription-notes"/> for details of how
421 <literal>copy_data = true</literal> can interact with the
422 <literal>origin</literal> parameter.
423 </para>
424 </listitem>
425 </varlistentry>
426
427 <varlistentry id="sql-createsubscription-params-with-failover">
428 <term><literal>failover</literal> (<type>boolean</type>)</term>
429 <listitem>
430 <para>
431 Specifies whether the replication slots associated with the subscription
432 are enabled to be synced to the standbys so that logical
433 replication can be resumed from the new primary after failover.
434 The default is <literal>false</literal>.
435 </para>
436 </listitem>
437 </varlistentry>
438 </variablelist></para>
439
440 </listitem>
441 </varlistentry>
442 </variablelist>
443
444 <para>
445 When specifying a parameter of type <type>boolean</type>, the
446 <literal>=</literal> <replaceable class="parameter">value</replaceable>
447 part can be omitted, which is equivalent to
448 specifying <literal>TRUE</literal>.
449 </para>
450 </refsect1>
451
452 <refsect1 id="sql-createsubscription-notes" xreflabel="Notes">
453 <title>Notes</title>
454
455 <para>
456 See <xref linkend="logical-replication-security"/> for details on
457 how to configure access control between the subscription and the
458 publication instance.
459 </para>
460
461 <para>
462 When creating a replication slot (the default behavior), <command>CREATE
463 SUBSCRIPTION</command> cannot be executed inside a transaction block.
464 </para>
465
466 <para>
467 Creating a subscription that connects to the same database cluster (for
468 example, to replicate between databases in the same cluster or to replicate
469 within the same database) will only succeed if the replication slot is not
470 created as part of the same command. Otherwise, the <command>CREATE
471 SUBSCRIPTION</command> call will hang. To make this work, create the
472 replication slot separately (using the
473 function <function>pg_create_logical_replication_slot</function> with the
474 plugin name <literal>pgoutput</literal>) and create the subscription using
475 the parameter <literal>create_slot = false</literal>. See
476 <xref linkend="logical-replication-subscription-examples-deferred-slot"/>
477 for examples. This is an implementation restriction that might be lifted in a
478 future release.
479 </para>
480
481 <para>
482 If any table in the publication has a <literal>WHERE</literal> clause, rows
483 for which the <replaceable class="parameter">expression</replaceable>
484 evaluates to <literal>false</literal> or <literal>NULL</literal> will not be
485 published. If the subscription has
486 several publications in which the same table has been published with
487 different <literal>WHERE</literal> clauses, a row will be published if any
488 of the expressions (referring to that publish operation) are satisfied. In
489 the case of different <literal>WHERE</literal> clauses, if one of the
490 publications has no <literal>WHERE</literal> clause (referring to that
491 publish operation) or the publication is declared as
492 <link linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL TABLES</literal></link>
493 or <link linkend="sql-createpublication-params-for-tables-in-schema"><literal>FOR TABLES IN SCHEMA</literal></link>,
494 rows are always published regardless of the definition of the other
495 expressions. If the subscriber is a <productname>PostgreSQL</productname>
496 version before 15, then any row filtering is ignored during the initial data
497 synchronization phase. For this case, the user might want to consider
498 deleting any initially copied data that would be incompatible with
499 subsequent filtering. Because initial data synchronization does not take
500 into account the publication
501 <link linkend="sql-createpublication-params-with-publish"><literal>publish</literal></link>
502 parameter when copying existing table data, some rows may be copied that
503 would not be replicated using DML. See
504 <xref linkend="logical-replication-subscription-examples"/> for examples.
505 </para>
506
507 <para>
508 Subscriptions having several publications in which the same table has been
509 published with different column lists are not supported.
510 </para>
511
512 <para>
513 We allow non-existent publications to be specified so that users can add
514 those later. This means
515 <link linkend="catalog-pg-subscription"><structname>pg_subscription</structname></link>
516 can have non-existent publications.
517 </para>
518
519 <para>
520 When using a subscription parameter combination of
521 <literal>copy_data = true</literal> and <literal>origin = NONE</literal>,
522 the initial sync table data is copied directly from the publisher, meaning
523 that knowledge of the true origin of that data is not possible. If the
524 publisher also has subscriptions then the copied table data might have
525 originated from further upstream. This scenario is detected and a WARNING is
526 logged to the user, but the warning is only an indication of a potential
527 problem; it is the user's responsibility to make the necessary checks to
528 ensure the copied data origins are really as wanted or not.
529 </para>
530
531 <para>
532 To find which tables might potentially include non-local origins (due to
533 other subscriptions created on the publisher) try this SQL query:
534 <programlisting>
535 # substitute &lt;pub-names&gt; below with your publication name(s) to be queried
536 SELECT DISTINCT PT.schemaname, PT.tablename
537 FROM pg_publication_tables PT
538 JOIN pg_class C ON (C.relname = PT.tablename)
539 JOIN pg_namespace N ON (N.nspname = PT.schemaname),
540 pg_subscription_rel PS
541 WHERE C.relnamespace = N.oid AND
542 (PS.srrelid = C.oid OR
543 C.oid IN (SELECT relid FROM pg_partition_ancestors(PS.srrelid) UNION
544 SELECT relid FROM pg_partition_tree(PS.srrelid))) AND
545 PT.pubname IN (&lt;pub-names&gt;);
546 </programlisting></para>
547
548 </refsect1>
549
550 <refsect1>
551 <title>Examples</title>
552
553 <para>
554 Create a subscription to a remote server that replicates tables in
555 the publications <literal>mypublication</literal> and
556 <literal>insert_only</literal> and starts replicating immediately on
557 commit:
558 <programlisting>
559 CREATE SUBSCRIPTION mysub
560 CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
561 PUBLICATION mypublication, insert_only;
562 </programlisting>
563 </para>
564
565 <para>
566 Create a subscription to a remote server that replicates tables in
567 the <literal>insert_only</literal> publication and does not start replicating
568 until enabled at a later time.
569 <programlisting>
570 CREATE SUBSCRIPTION mysub
571 CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
572 PUBLICATION insert_only
573 WITH (enabled = false);
574 </programlisting></para>
575 </refsect1>
576
577 <refsect1>
578 <title>Compatibility</title>
579
580 <para>
581 <command>CREATE SUBSCRIPTION</command> is a <productname>PostgreSQL</productname>
582 extension.
583 </para>
584 </refsect1>
585
586 <refsect1>
587 <title>See Also</title>
588
589 <simplelist type="inline">
590 <member><xref linkend="sql-altersubscription"/></member>
591 <member><xref linkend="sql-dropsubscription"/></member>
592 <member><xref linkend="sql-createpublication"/></member>
593 <member><xref linkend="sql-alterpublication"/></member>
594 </simplelist>
595 </refsect1>
596 </refentry>