Debian Codegraph @

doc/src/sgml/logical-replication.sgml

raw
1 <!-- doc/src/sgml/logical-replication.sgml -->
2
3 <chapter id="logical-replication">
4 <title>Logical Replication</title>
5
6 <para>
7 Logical replication is a method of replicating data objects and their
8 changes, based upon their replication identity (usually a primary key). We
9 use the term logical in contrast to physical replication, which uses exact
10 block addresses and byte-by-byte replication. PostgreSQL supports both
11 mechanisms concurrently, see <xref linkend="high-availability"/>. Logical
12 replication allows fine-grained control over both data replication and
13 security.
14 </para>
15
16 <para>
17 Logical replication uses a <firstterm>publish</firstterm>
18 and <firstterm>subscribe</firstterm> model with one or
19 more <firstterm>subscribers</firstterm> subscribing to one or more
20 <firstterm>publications</firstterm> on a <firstterm>publisher</firstterm>
21 node. Subscribers pull data from the publications they subscribe to and may
22 subsequently re-publish data to allow cascading replication or more complex
23 configurations.
24 </para>
25
26 <para>
27 When logical replication of a table typically starts, PostgreSQL takes
28 a snapshot of the table's data on the publisher database and copies it
29 to the subscriber. Once complete, changes on the publisher since the
30 initial copy are sent continually to the subscriber. The subscriber
31 applies the data in the same
32 order as the publisher so that transactional consistency is guaranteed for
33 publications within a single subscription. This method of data replication
34 is sometimes referred to as transactional replication.
35 </para>
36
37 <para>
38 The typical use-cases for logical replication are:
39
40 <itemizedlist>
41 <listitem>
42 <para>
43 Sending incremental changes in a single database or a subset of a
44 database to subscribers as they occur.
45 </para>
46 </listitem>
47
48 <listitem>
49 <para>
50 Firing triggers for individual changes as they arrive on the
51 subscriber.
52 </para>
53 </listitem>
54
55 <listitem>
56 <para>
57 Consolidating multiple databases into a single one (for example for
58 analytical purposes).
59 </para>
60 </listitem>
61
62 <listitem>
63 <para>
64 Replicating between different major versions of PostgreSQL.
65 </para>
66 </listitem>
67
68 <listitem>
69 <para>
70 Replicating between PostgreSQL instances on different platforms (for
71 example Linux to Windows).
72 </para>
73 </listitem>
74
75 <listitem>
76 <para>
77 Giving access to replicated data to different groups of users.
78 </para>
79 </listitem>
80
81 <listitem>
82 <para>
83 Sharing a subset of the database between multiple databases.
84 </para>
85 </listitem>
86 </itemizedlist>
87 </para>
88
89 <para>
90 The subscriber database behaves in the same way as any other PostgreSQL
91 instance and can be used as a publisher for other databases by defining its
92 own publications. When the subscriber is treated as read-only by an
93 application, there will be no conflicts from a single subscription. On the
94 other hand, if there are other writes done either by an application or by other
95 subscribers to the same set of tables, conflicts can arise.
96 </para>
97
98 <sect1 id="logical-replication-publication">
99 <title>Publication</title>
100
101 <para>
102 A <firstterm>publication</firstterm> can be defined on any physical
103 replication primary. The node where a publication is defined is referred to
104 as <firstterm>publisher</firstterm>. A publication is a set of changes
105 generated from a table or a group of tables, and might also be described as
106 a change set or replication set. Each publication exists in only one database.
107 </para>
108
109 <para>
110 Publications are different from schemas and do not affect how the table is
111 accessed. Each table can be added to multiple publications if needed.
112 Publications may currently only contain tables and all tables in schema.
113 Objects must be added explicitly, except when a publication is created for
114 <literal>ALL TABLES</literal>.
115 </para>
116
117 <para>
118 Publications can choose to limit the changes they produce to
119 any combination of <command>INSERT</command>, <command>UPDATE</command>,
120 <command>DELETE</command>, and <command>TRUNCATE</command>, similar to how triggers are fired by
121 particular event types. By default, all operation types are replicated.
122 These publication specifications apply only for DML operations; they do not affect the initial
123 data synchronization copy. (Row filters have no effect for
124 <command>TRUNCATE</command>. See <xref linkend="logical-replication-row-filter"/>).
125 </para>
126
127 <para>
128 Every publication can have multiple subscribers.
129 </para>
130
131 <para>
132 A publication is created using the <link linkend="sql-createpublication"><command>CREATE PUBLICATION</command></link>
133 command and may later be altered or dropped using corresponding commands.
134 </para>
135
136 <para>
137 The individual tables can be added and removed dynamically using
138 <link linkend="sql-alterpublication"><command>ALTER PUBLICATION</command></link>. Both the <literal>ADD
139 TABLE</literal> and <literal>DROP TABLE</literal> operations are
140 transactional, so the table will start or stop replicating at the correct
141 snapshot once the transaction has committed.
142 </para>
143
144 <sect2 id="logical-replication-publication-replica-identity">
145 <title>Replica Identity</title>
146
147 <para>
148 A published table must have a <firstterm>replica identity</firstterm>
149 configured in order to be able to replicate <command>UPDATE</command>
150 and <command>DELETE</command> operations, so that appropriate rows to
151 update or delete can be identified on the subscriber side.
152 </para>
153
154 <para>
155 By default, this is the primary key, if there is one. Another unique index
156 (with certain additional requirements) can also be set to be the replica
157 identity. If the table does not have any suitable key, then it can be set
158 to replica identity <literal>FULL</literal>, which means the entire row
159 becomes the key. When replica identity <literal>FULL</literal> is
160 specified, indexes can be used on the subscriber side for searching the
161 rows. Candidate indexes must be btree or hash, non-partial, and the
162 leftmost index field must be a column (not an expression) that references
163 the published table column. These restrictions on the non-unique index
164 properties adhere to some of the restrictions that are enforced for
165 primary keys. If there are no such suitable indexes, the search on the
166 subscriber side can be very inefficient, therefore replica identity
167 <literal>FULL</literal> should only be used as a fallback if no other
168 solution is possible.
169 </para>
170
171 <para>
172 If a replica identity other than <literal>FULL</literal> is set on the
173 publisher side, a replica identity comprising the same or fewer columns
174 must also be set on the subscriber side.
175 </para>
176
177 <para>
178 Tables with a replica identity defined as <literal>NOTHING</literal>,
179 <literal>DEFAULT</literal> without a primary key, or <literal>USING
180 INDEX</literal> with a dropped index, cannot support
181 <command>UPDATE</command> or <command>DELETE</command> operations when
182 included in a publication replicating these actions. Attempting such
183 operations will result in an error on the publisher.
184 </para>
185
186 <para>
187 <command>INSERT</command> operations can proceed regardless of any replica identity.
188 </para>
189
190 <para>
191 See <link linkend="sql-altertable-replica-identity"><literal>ALTER TABLE...REPLICA IDENTITY</literal></link>
192 for details on how to set the replica identity.
193 </para>
194 </sect2>
195
196 </sect1>
197
198 <sect1 id="logical-replication-subscription">
199 <title>Subscription</title>
200
201 <para>
202 A <firstterm>subscription</firstterm> is the downstream side of logical
203 replication. The node where a subscription is defined is referred to as
204 the <firstterm>subscriber</firstterm>. A subscription defines the connection
205 to another database and the set of publications (one or more) to which it
206 wants to subscribe.
207 </para>
208
209 <para>
210 The subscriber database behaves in the same way as any other PostgreSQL
211 instance and can be used as a publisher for other databases by defining its
212 own publications.
213 </para>
214
215 <para>
216 A subscriber node may have multiple subscriptions if desired. It is
217 possible to define multiple subscriptions between a single
218 publisher-subscriber pair, in which case care must be taken to ensure
219 that the subscribed publication objects don't overlap.
220 </para>
221
222 <para>
223 Each subscription will receive changes via one replication slot (see
224 <xref linkend="streaming-replication-slots"/>). Additional replication
225 slots may be required for the initial data synchronization of
226 pre-existing table data and those will be dropped at the end of data
227 synchronization.
228 </para>
229
230 <para>
231 A logical replication subscription can be a standby for synchronous
232 replication (see <xref linkend="synchronous-replication"/>). The standby
233 name is by default the subscription name. An alternative name can be
234 specified as <literal>application_name</literal> in the connection
235 information of the subscription.
236 </para>
237
238 <para>
239 Subscriptions are dumped by <command>pg_dump</command> if the current user
240 is a superuser. Otherwise a warning is written and subscriptions are
241 skipped, because non-superusers cannot read all subscription information
242 from the <structname>pg_subscription</structname> catalog.
243 </para>
244
245 <para>
246 The subscription is added using <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link> and
247 can be stopped/resumed at any time using the
248 <link linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION</command></link> command and removed using
249 <link linkend="sql-dropsubscription"><command>DROP SUBSCRIPTION</command></link>.
250 </para>
251
252 <para>
253 When a subscription is dropped and recreated, the synchronization
254 information is lost. This means that the data has to be resynchronized
255 afterwards.
256 </para>
257
258 <para>
259 The schema definitions are not replicated, and the published tables must
260 exist on the subscriber. Only regular tables may be
261 the target of replication. For example, you can't replicate to a view.
262 </para>
263
264 <para>
265 The tables are matched between the publisher and the subscriber using the
266 fully qualified table name. Replication to differently-named tables on the
267 subscriber is not supported.
268 </para>
269
270 <para>
271 Columns of a table are also matched by name. The order of columns in the
272 subscriber table does not need to match that of the publisher. The data
273 types of the columns do not need to match, as long as the text
274 representation of the data can be converted to the target type. For
275 example, you can replicate from a column of type <type>integer</type> to a
276 column of type <type>bigint</type>. The target table can also have
277 additional columns not provided by the published table. Any such columns
278 will be filled with the default value as specified in the definition of the
279 target table. However, logical replication in binary format is more
280 restrictive. See the
281 <link linkend="sql-createsubscription-params-with-binary"><literal>binary</literal></link>
282 option of <command>CREATE SUBSCRIPTION</command> for details.
283 </para>
284
285 <sect2 id="logical-replication-subscription-slot">
286 <title>Replication Slot Management</title>
287
288 <para>
289 As mentioned earlier, each (active) subscription receives changes from a
290 replication slot on the remote (publishing) side.
291 </para>
292 <para>
293 Additional table synchronization slots are normally transient, created
294 internally to perform initial table synchronization and dropped
295 automatically when they are no longer needed. These table synchronization
296 slots have generated names: <quote><literal>pg_%u_sync_%u_%llu</literal></quote>
297 (parameters: Subscription <parameter>oid</parameter>,
298 Table <parameter>relid</parameter>, system identifier <parameter>sysid</parameter>)
299 </para>
300 <para>
301 Normally, the remote replication slot is created automatically when the
302 subscription is created using <link linkend="sql-createsubscription">
303 <command>CREATE SUBSCRIPTION</command></link> and it
304 is dropped automatically when the subscription is dropped using
305 <link linkend="sql-dropsubscription"><command>DROP SUBSCRIPTION</command></link>.
306 In some situations, however, it can
307 be useful or necessary to manipulate the subscription and the underlying
308 replication slot separately. Here are some scenarios:
309
310 <itemizedlist>
311 <listitem>
312 <para>
313 When creating a subscription, the replication slot already exists. In
314 that case, the subscription can be created using
315 the <literal>create_slot = false</literal> option to associate with the
316 existing slot.
317 </para>
318 </listitem>
319
320 <listitem>
321 <para>
322 When creating a subscription, the remote host is not reachable or in an
323 unclear state. In that case, the subscription can be created using
324 the <literal>connect = false</literal> option. The remote host will then not
325 be contacted at all. This is what <application>pg_dump</application>
326 uses. The remote replication slot will then have to be created
327 manually before the subscription can be activated.
328 </para>
329 </listitem>
330
331 <listitem>
332 <para>
333 When dropping a subscription, the replication slot should be kept.
334 This could be useful when the subscriber database is being moved to a
335 different host and will be activated from there. In that case,
336 disassociate the slot from the subscription using
337 <link linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION</command></link>
338 before attempting to drop the subscription.
339 </para>
340 </listitem>
341
342 <listitem>
343 <para>
344 When dropping a subscription, the remote host is not reachable. In
345 that case, disassociate the slot from the subscription
346 using <command>ALTER SUBSCRIPTION</command> before attempting to drop
347 the subscription. If the remote database instance no longer exists, no
348 further action is then necessary. If, however, the remote database
349 instance is just unreachable, the replication slot (and any still
350 remaining table synchronization slots) should then be
351 dropped manually; otherwise it/they would continue to reserve WAL and might
352 eventually cause the disk to fill up. Such cases should be carefully
353 investigated.
354 </para>
355 </listitem>
356 </itemizedlist>
357 </para>
358 </sect2>
359
360 <sect2 id="logical-replication-subscription-examples">
361 <title>Examples: Set Up Logical Replication</title>
362
363 <para>
364 Create some test tables on the publisher.
365 <programlisting>
366 /* pub # */ CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
367 /* pub # */ CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
368 /* pub # */ CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
369 </programlisting></para>
370
371 <para>
372 Create the same tables on the subscriber.
373 <programlisting>
374 /* sub # */ CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
375 /* sub # */ CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
376 /* sub # */ CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
377 </programlisting></para>
378
379 <para>
380 Insert data to the tables at the publisher side.
381 <programlisting>
382 /* pub # */ INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
383 /* pub # */ INSERT INTO t2 VALUES (1, 'A'), (2, 'B'), (3, 'C');
384 /* pub # */ INSERT INTO t3 VALUES (1, 'i'), (2, 'ii'), (3, 'iii');
385 </programlisting></para>
386
387 <para>
388 Create publications for the tables. The publications <literal>pub2</literal>
389 and <literal>pub3a</literal> disallow some
390 <link linkend="sql-createpublication-params-with-publish"><literal>publish</literal></link>
391 operations. The publication <literal>pub3b</literal> has a row filter (see
392 <xref linkend="logical-replication-row-filter"/>).
393 <programlisting><![CDATA[
394 /* pub # */ CREATE PUBLICATION pub1 FOR TABLE t1;
395 /* pub # */ CREATE PUBLICATION pub2 FOR TABLE t2 WITH (publish = 'truncate');
396 /* pub # */ CREATE PUBLICATION pub3a FOR TABLE t3 WITH (publish = 'truncate');
397 /* pub # */ CREATE PUBLICATION pub3b FOR TABLE t3 WHERE (e > 5);
398 ]]></programlisting></para>
399
400 <para>
401 Create subscriptions for the publications. The subscription
402 <literal>sub3</literal> subscribes to both <literal>pub3a</literal> and
403 <literal>pub3b</literal>. All subscriptions will copy initial data by default.
404 <programlisting>
405 /* sub # */ CREATE SUBSCRIPTION sub1
406 /* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub1'
407 /* sub - */ PUBLICATION pub1;
408 /* sub # */ CREATE SUBSCRIPTION sub2
409 /* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub2'
410 /* sub - */ PUBLICATION pub2;
411 /* sub # */ CREATE SUBSCRIPTION sub3
412 /* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub3'
413 /* sub - */ PUBLICATION pub3a, pub3b;
414 </programlisting></para>
415
416 <para>
417 Observe that initial table data is copied, regardless of the
418 <literal>publish</literal> operation of the publication.
419 <programlisting>
420 /* sub # */ SELECT * FROM t1;
421 a | b
422 ---+-------
423 1 | one
424 2 | two
425 3 | three
426 (3 rows)
427
428 /* sub # */ SELECT * FROM t2;
429 c | d
430 ---+---
431 1 | A
432 2 | B
433 3 | C
434 (3 rows)
435 </programlisting></para>
436
437 <para>
438 Furthermore, because the initial data copy ignores the <literal>publish</literal>
439 operation, and because publication <literal>pub3a</literal> has no row filter,
440 it means the copied table <literal>t3</literal> contains all rows even when
441 they do not match the row filter of publication <literal>pub3b</literal>.
442 <programlisting>
443 /* sub # */ SELECT * FROM t3;
444 e | f
445 ---+-----
446 1 | i
447 2 | ii
448 3 | iii
449 (3 rows)
450 </programlisting></para>
451
452 <para>
453 Insert more data to the tables at the publisher side.
454 <programlisting>
455 /* pub # */ INSERT INTO t1 VALUES (4, 'four'), (5, 'five'), (6, 'six');
456 /* pub # */ INSERT INTO t2 VALUES (4, 'D'), (5, 'E'), (6, 'F');
457 /* pub # */ INSERT INTO t3 VALUES (4, 'iv'), (5, 'v'), (6, 'vi');
458 </programlisting></para>
459
460 <para>
461 Now the publisher side data looks like:
462 <programlisting>
463 /* pub # */ SELECT * FROM t1;
464 a | b
465 ---+-------
466 1 | one
467 2 | two
468 3 | three
469 4 | four
470 5 | five
471 6 | six
472 (6 rows)
473
474 /* pub # */ SELECT * FROM t2;
475 c | d
476 ---+---
477 1 | A
478 2 | B
479 3 | C
480 4 | D
481 5 | E
482 6 | F
483 (6 rows)
484
485 /* pub # */ SELECT * FROM t3;
486 e | f
487 ---+-----
488 1 | i
489 2 | ii
490 3 | iii
491 4 | iv
492 5 | v
493 6 | vi
494 (6 rows)
495 </programlisting></para>
496
497 <para>
498 Observe that during normal replication the appropriate
499 <literal>publish</literal> operations are used. This means publications
500 <literal>pub2</literal> and <literal>pub3a</literal> will not replicate the
501 <literal>INSERT</literal>. Also, publication <literal>pub3b</literal> will
502 only replicate data that matches the row filter of <literal>pub3b</literal>.
503 Now the subscriber side data looks like:
504 <programlisting>
505 /* sub # */ SELECT * FROM t1;
506 a | b
507 ---+-------
508 1 | one
509 2 | two
510 3 | three
511 4 | four
512 5 | five
513 6 | six
514 (6 rows)
515
516 /* sub # */ SELECT * FROM t2;
517 c | d
518 ---+---
519 1 | A
520 2 | B
521 3 | C
522 (3 rows)
523
524 /* sub # */ SELECT * FROM t3;
525 e | f
526 ---+-----
527 1 | i
528 2 | ii
529 3 | iii
530 6 | vi
531 (4 rows)
532 </programlisting></para>
533 </sect2>
534
535 <sect2 id="logical-replication-subscription-examples-deferred-slot">
536 <title>Examples: Deferred Replication Slot Creation</title>
537
538 <para>
539 There are some cases (e.g.
540 <xref linkend="logical-replication-subscription-slot"/>) where, if the
541 remote replication slot was not created automatically, the user must create
542 it manually before the subscription can be activated. The steps to create
543 the slot and activate the subscription are shown in the following examples.
544 These examples specify the standard logical decoding output plugin
545 (<literal>pgoutput</literal>), which is what the built-in logical
546 replication uses.
547 </para>
548 <para>
549 First, create a publication for the examples to use.
550 <programlisting>
551 /* pub # */ CREATE PUBLICATION pub1 FOR ALL TABLES;
552 </programlisting></para>
553 <para>
554 Example 1: Where the subscription says <literal>connect = false</literal>
555 </para>
556 <para>
557 <itemizedlist>
558 <listitem>
559 <para>
560 Create the subscription.
561 <programlisting>
562 /* sub # */ CREATE SUBSCRIPTION sub1
563 /* sub - */ CONNECTION 'host=localhost dbname=test_pub'
564 /* sub - */ PUBLICATION pub1
565 /* sub - */ WITH (connect=false);
566 WARNING: subscription was created, but is not connected
567 HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
568 </programlisting></para>
569 </listitem>
570 <listitem>
571 <para>
572 On the publisher, manually create a slot. Because the name was not
573 specified during <literal>CREATE SUBSCRIPTION</literal>, the name of the
574 slot to create is same as the subscription name, e.g. "sub1".
575 <programlisting>
576 /* pub # */ SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput');
577 slot_name | lsn
578 -----------+-----------
579 sub1 | 0/19404D0
580 (1 row)
581 </programlisting></para>
582 </listitem>
583 <listitem>
584 <para>
585 On the subscriber, complete the activation of the subscription. After
586 this the tables of <literal>pub1</literal> will start replicating.
587 <programlisting>
588 /* sub # */ ALTER SUBSCRIPTION sub1 ENABLE;
589 /* sub # */ ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
590 </programlisting></para>
591 </listitem>
592 </itemizedlist>
593 </para>
594
595 <para>
596 Example 2: Where the subscription says <literal>connect = false</literal>,
597 but also specifies the
598 <link linkend="sql-createsubscription-params-with-slot-name"><literal>slot_name</literal></link>
599 option.
600 <itemizedlist>
601 <listitem>
602 <para>
603 Create the subscription.
604 <programlisting>
605 /* sub # */ CREATE SUBSCRIPTION sub1
606 /* sub - */ CONNECTION 'host=localhost dbname=test_pub'
607 /* sub - */ PUBLICATION pub1
608 /* sub - */ WITH (connect=false, slot_name='myslot');
609 WARNING: subscription was created, but is not connected
610 HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
611 </programlisting></para>
612 </listitem>
613 <listitem>
614 <para>
615 On the publisher, manually create a slot using the same name that was
616 specified during <literal>CREATE SUBSCRIPTION</literal>, e.g. "myslot".
617 <programlisting>
618 /* pub # */ SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
619 slot_name | lsn
620 -----------+-----------
621 myslot | 0/19059A0
622 (1 row)
623 </programlisting></para>
624 </listitem>
625 <listitem>
626 <para>
627 On the subscriber, the remaining subscription activation steps are the
628 same as before.
629 <programlisting>
630 /* sub # */ ALTER SUBSCRIPTION sub1 ENABLE;
631 /* sub # */ ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
632 </programlisting></para>
633 </listitem>
634 </itemizedlist>
635 </para>
636
637 <para>
638 Example 3: Where the subscription specifies <literal>slot_name = NONE</literal>
639 <itemizedlist>
640 <listitem>
641 <para>
642 Create the subscription. When <literal>slot_name = NONE</literal> then
643 <literal>enabled = false</literal>, and
644 <literal>create_slot = false</literal> are also needed.
645 <programlisting>
646 /* sub # */ CREATE SUBSCRIPTION sub1
647 /* sub - */ CONNECTION 'host=localhost dbname=test_pub'
648 /* sub - */ PUBLICATION pub1
649 /* sub - */ WITH (slot_name=NONE, enabled=false, create_slot=false);
650 </programlisting></para>
651 </listitem>
652 <listitem>
653 <para>
654 On the publisher, manually create a slot using any name, e.g. "myslot".
655 <programlisting>
656 /* pub # */ SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
657 slot_name | lsn
658 -----------+-----------
659 myslot | 0/1905930
660 (1 row)
661 </programlisting></para>
662 </listitem>
663 <listitem>
664 <para>
665 On the subscriber, associate the subscription with the slot name just
666 created.
667 <programlisting>
668 /* sub # */ ALTER SUBSCRIPTION sub1 SET (slot_name='myslot');
669 </programlisting></para>
670 </listitem>
671 <listitem>
672 <para>
673 The remaining subscription activation steps are same as before.
674 <programlisting>
675 /* sub # */ ALTER SUBSCRIPTION sub1 ENABLE;
676 /* sub # */ ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
677 </programlisting></para>
678 </listitem>
679 </itemizedlist>
680 </para>
681 </sect2>
682
683 </sect1>
684
685 <sect1 id="logical-replication-failover">
686 <title>Logical Replication Failover</title>
687
688 <para>
689 To allow subscriber nodes to continue replicating data from the publisher
690 node even when the publisher node goes down, there must be a physical standby
691 corresponding to the publisher node. The logical slots on the primary server
692 corresponding to the subscriptions can be synchronized to the standby server by
693 specifying <literal>failover = true</literal> when creating subscriptions. See
694 <xref linkend="logicaldecoding-replication-slots-synchronization"/> for details.
695 Enabling the
696 <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>
697 parameter ensures a seamless transition of those subscriptions after the
698 standby is promoted. They can continue subscribing to publications on the
699 new primary server.
700 </para>
701
702 <para>
703 Because the slot synchronization logic copies asynchronously, it is
704 necessary to confirm that replication slots have been synced to the standby
705 server before the failover happens. To ensure a successful failover, the
706 standby server must be ahead of the subscriber. This can be achieved by
707 configuring
708 <link linkend="guc-synchronized-standby-slots"><varname>synchronized_standby_slots</varname></link>.
709 </para>
710
711 <para>
712 To confirm that the standby server is indeed ready for failover for a given subscriber, follow these
713 steps to verify that all the logical replication slots required by that subscriber have been
714 synchronized to the standby server:
715 </para>
716
717 <procedure>
718 <step performance="required">
719 <para>
720 On the subscriber node, use the following SQL to identify which replication
721 slots should be synced to the standby that we plan to promote. This query
722 will return the relevant replication slots associated with the
723 failover-enabled subscriptions.
724 <programlisting>
725 /* sub # */ SELECT
726 array_agg(quote_literal(s.subslotname)) AS slots
727 FROM pg_subscription s
728 WHERE s.subfailover AND
729 s.subslotname IS NOT NULL;
730 slots
731 -------
732 {'sub1','sub2','sub3'}
733 (1 row)
734 </programlisting></para>
735 </step>
736 <step performance="required">
737 <para>
738 On the subscriber node, use the following SQL to identify which table
739 synchronization slots should be synced to the standby that we plan to promote.
740 This query needs to be run on each database that includes the failover-enabled
741 subscription(s). Note that the table sync slot should be synced to the standby
742 server only if the table copy is finished
743 (See <xref linkend="catalog-pg-subscription-rel"/>).
744 We don't need to ensure that the table sync slots are synced in other scenarios
745 as they will either be dropped or re-created on the new primary server in those
746 cases.
747 <programlisting>
748 /* sub # */ SELECT
749 array_agg(quote_literal(slot_name)) AS slots
750 FROM
751 (
752 SELECT CONCAT('pg_', srsubid, '_sync_', srrelid, '_', ctl.system_identifier) AS slot_name
753 FROM pg_control_system() ctl, pg_subscription_rel r, pg_subscription s
754 WHERE r.srsubstate = 'f' AND s.oid = r.srsubid AND s.subfailover
755 );
756 slots
757 -------
758 {'pg_16394_sync_16385_7394666715149055164'}
759 (1 row)
760 </programlisting></para>
761 </step>
762 <step performance="required">
763 <para>
764 Check that the logical replication slots identified above exist on
765 the standby server and are ready for failover.
766 <programlisting>
767 /* standby # */ SELECT slot_name, (synced AND NOT temporary AND invalidation_reason IS NULL) AS failover_ready
768 FROM pg_replication_slots
769 WHERE slot_name IN
770 ('sub1','sub2','sub3', 'pg_16394_sync_16385_7394666715149055164');
771 slot_name | failover_ready
772 --------------------------------------------+----------------
773 sub1 | t
774 sub2 | t
775 sub3 | t
776 pg_16394_sync_16385_7394666715149055164 | t
777 (4 rows)
778 </programlisting></para>
779 </step>
780 </procedure>
781
782 <para>
783 If all the slots are present on the standby server and the result
784 (<literal>failover_ready</literal>) of the above SQL query is true, then
785 existing subscriptions can continue subscribing to publications on the new
786 primary server.
787 </para>
788
789 <para>
790 The first two steps in the above procedure are meant for a
791 <productname>PostgreSQL</productname> subscriber. It is recommended to run
792 these steps on each subscriber node, that will be served by the designated
793 standby after failover, to obtain the complete list of replication
794 slots. This list can then be verified in Step 3 to ensure failover readiness.
795 Non-<productname>PostgreSQL</productname> subscribers, on the other hand, may
796 use their own methods to identify the replication slots used by their
797 respective subscriptions.
798 </para>
799
800 <para>
801 In some cases, such as during a planned failover, it is necessary to confirm
802 that all subscribers, whether <productname>PostgreSQL</productname> or
803 non-<productname>PostgreSQL</productname>, will be able to continue
804 replication after failover to a given standby server. In such cases, use the
805 following SQL, instead of performing the first two steps above, to identify
806 which replication slots on the primary need to be synced to the standby that
807 is intended for promotion. This query returns the relevant replication slots
808 associated with all the failover-enabled subscriptions.
809 </para>
810
811 <para>
812 <programlisting>
813 /* primary # */ SELECT array_agg(quote_literal(r.slot_name)) AS slots
814 FROM pg_replication_slots r
815 WHERE r.failover AND NOT r.temporary;
816 slots
817 -------
818 {'sub1','sub2','sub3', 'pg_16394_sync_16385_7394666715149055164'}
819 (1 row)
820 </programlisting></para>
821 </sect1>
822
823 <sect1 id="logical-replication-row-filter">
824 <title>Row Filters</title>
825
826 <para>
827 By default, all data from all published tables will be replicated to the
828 appropriate subscribers. The replicated data can be reduced by using a
829 <firstterm>row filter</firstterm>. A user might choose to use row filters
830 for behavioral, security or performance reasons. If a published table sets a
831 row filter, a row is replicated only if its data satisfies the row filter
832 expression. This allows a set of tables to be partially replicated. The row
833 filter is defined per table. Use a <literal>WHERE</literal> clause after the
834 table name for each published table that requires data to be filtered out.
835 The <literal>WHERE</literal> clause must be enclosed by parentheses. See
836 <xref linkend="sql-createpublication"/> for details.
837 </para>
838
839 <sect2 id="logical-replication-row-filter-rules">
840 <title>Row Filter Rules</title>
841
842 <para>
843 Row filters are applied <emphasis>before</emphasis> publishing the changes.
844 If the row filter evaluates to <literal>false</literal> or <literal>NULL</literal>
845 then the row is not replicated. The <literal>WHERE</literal> clause expression
846 is evaluated with the same role used for the replication connection (i.e.
847 the role specified in the
848 <link linkend="sql-createsubscription-params-connection"><literal>CONNECTION</literal></link>
849 clause of the <xref linkend="sql-createsubscription"/>). Row filters have
850 no effect for <command>TRUNCATE</command> command.
851 </para>
852
853 </sect2>
854
855 <sect2 id="logical-replication-row-filter-restrictions">
856 <title>Expression Restrictions</title>
857
858 <para>
859 The <literal>WHERE</literal> clause allows only simple expressions. It
860 cannot contain user-defined functions, operators, types, and collations,
861 system column references or non-immutable built-in functions.
862 </para>
863
864 <para>
865 If a publication publishes <command>UPDATE</command> or
866 <command>DELETE</command> operations, the row filter <literal>WHERE</literal>
867 clause must contain only columns that are covered by the replica identity
868 (see <xref linkend="sql-altertable-replica-identity"/>). If a publication
869 publishes only <command>INSERT</command> operations, the row filter
870 <literal>WHERE</literal> clause can use any column.
871 </para>
872
873 </sect2>
874
875 <sect2 id="logical-replication-row-filter-transformations">
876 <title>UPDATE Transformations</title>
877
878 <para>
879 Whenever an <command>UPDATE</command> is processed, the row filter
880 expression is evaluated for both the old and new row (i.e. using the data
881 before and after the update). If both evaluations are <literal>true</literal>,
882 it replicates the <command>UPDATE</command> change. If both evaluations are
883 <literal>false</literal>, it doesn't replicate the change. If only one of
884 the old/new rows matches the row filter expression, the <command>UPDATE</command>
885 is transformed to <command>INSERT</command> or <command>DELETE</command>, to
886 avoid any data inconsistency. The row on the subscriber should reflect what
887 is defined by the row filter expression on the publisher.
888 </para>
889
890 <para>
891 If the old row satisfies the row filter expression (it was sent to the
892 subscriber) but the new row doesn't, then, from a data consistency
893 perspective the old row should be removed from the subscriber.
894 So the <command>UPDATE</command> is transformed into a <command>DELETE</command>.
895 </para>
896
897 <para>
898 If the old row doesn't satisfy the row filter expression (it wasn't sent
899 to the subscriber) but the new row does, then, from a data consistency
900 perspective the new row should be added to the subscriber.
901 So the <command>UPDATE</command> is transformed into an <command>INSERT</command>.
902 </para>
903
904 <para>
905 <xref linkend="logical-replication-row-filter-transformations-summary"/>
906 summarizes the applied transformations.
907 </para>
908
909 <table id="logical-replication-row-filter-transformations-summary">
910 <title><command>UPDATE</command> Transformation Summary</title>
911 <tgroup cols="3">
912 <thead>
913 <row>
914 <entry>Old row</entry><entry>New row</entry><entry>Transformation</entry>
915 </row>
916 </thead>
917 <tbody>
918 <row>
919 <entry>no match</entry><entry>no match</entry><entry>don't replicate</entry>
920 </row>
921 <row>
922 <entry>no match</entry><entry>match</entry><entry><literal>INSERT</literal></entry>
923 </row>
924 <row>
925 <entry>match</entry><entry>no match</entry><entry><literal>DELETE</literal></entry>
926 </row>
927 <row>
928 <entry>match</entry><entry>match</entry><entry><literal>UPDATE</literal></entry>
929 </row>
930 </tbody>
931 </tgroup>
932 </table>
933
934 </sect2>
935
936 <sect2 id="logical-replication-row-filter-partitioned-table">
937 <title>Partitioned Tables</title>
938
939 <para>
940 If the publication contains a partitioned table, the publication parameter
941 <link linkend="sql-createpublication-params-with-publish-via-partition-root"><literal>publish_via_partition_root</literal></link>
942 determines which row filter is used. If <literal>publish_via_partition_root</literal>
943 is <literal>true</literal>, the <emphasis>root partitioned table's</emphasis>
944 row filter is used. Otherwise, if <literal>publish_via_partition_root</literal>
945 is <literal>false</literal> (default), each <emphasis>partition's</emphasis>
946 row filter is used.
947 </para>
948
949 </sect2>
950
951 <sect2 id="logical-replication-row-filter-initial-data-sync">
952 <title>Initial Data Synchronization</title>
953
954 <para>
955 If the subscription requires copying pre-existing table data
956 and a publication contains <literal>WHERE</literal> clauses, only data that
957 satisfies the row filter expressions is copied to the subscriber.
958 </para>
959
960 <para>
961 If the subscription has several publications in which a table has been
962 published with different <literal>WHERE</literal> clauses, rows that satisfy
963 <emphasis>any</emphasis> of the expressions will be copied. See
964 <xref linkend="logical-replication-row-filter-combining"/> for details.
965 </para>
966
967 <warning>
968 <para>
969 Because initial data synchronization does not take into account the
970 <link linkend="sql-createpublication-params-with-publish"><literal>publish</literal></link>
971 parameter when copying existing table data, some rows may be copied that
972 would not be replicated using DML. Refer to
973 <xref linkend="logical-replication-snapshot"/>, and see
974 <xref linkend="logical-replication-subscription-examples"/> for examples.
975 </para>
976 </warning>
977
978 <note>
979 <para>
980 If the subscriber is in a release prior to 15, copying pre-existing data
981 doesn't use row filters even if they are defined in the publication.
982 This is because old releases can only copy the entire table data.
983 </para>
984 </note>
985
986 </sect2>
987
988 <sect2 id="logical-replication-row-filter-combining">
989 <title>Combining Multiple Row Filters</title>
990
991 <para>
992 If the subscription has several publications in which the same table has
993 been published with different row filters (for the same
994 <link linkend="sql-createpublication-params-with-publish"><literal>publish</literal></link>
995 operation), those expressions get ORed together, so that rows satisfying
996 <emphasis>any</emphasis> of the expressions will be replicated. This means all
997 the other row filters for the same table become redundant if:
998 <itemizedlist>
999 <listitem>
1000 <para>
1001 One of the publications has no row filter.
1002 </para>
1003 </listitem>
1004 <listitem>
1005 <para>
1006 One of the publications was created using
1007 <link linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL TABLES</literal></link>.
1008 This clause does not allow row filters.
1009 </para>
1010 </listitem>
1011 <listitem>
1012 <para>
1013 One of the publications was created using
1014 <link linkend="sql-createpublication-params-for-tables-in-schema"><literal>FOR TABLES IN SCHEMA</literal></link>
1015 and the table belongs to the referred schema. This clause does not allow
1016 row filters.
1017 </para>
1018 </listitem>
1019 </itemizedlist></para>
1020
1021 </sect2>
1022
1023 <sect2 id="logical-replication-row-filter-examples">
1024 <title>Examples</title>
1025
1026 <para>
1027 Create some tables to be used in the following examples.
1028 <programlisting>
1029 /* pub # */ CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
1030 /* pub # */ CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d));
1031 /* pub # */ CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g));
1032 </programlisting></para>
1033
1034 <para>
1035 Create some publications. Publication <literal>p1</literal> has one table
1036 (<literal>t1</literal>) and that table has a row filter. Publication
1037 <literal>p2</literal> has two tables. Table <literal>t1</literal> has no row
1038 filter, and table <literal>t2</literal> has a row filter. Publication
1039 <literal>p3</literal> has two tables, and both of them have a row filter.
1040 <programlisting><![CDATA[
1041 /* pub # */ CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
1042 /* pub # */ CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
1043 /* pub # */ CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
1044 ]]></programlisting></para>
1045
1046 <para>
1047 <command>psql</command> can be used to show the row filter expressions (if
1048 defined) for each publication.
1049 <programlisting><![CDATA[
1050 /* pub # */ \dRp+
1051 Publication p1
1052 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1053 ----------+------------+---------+---------+---------+-----------+-------------------+----------
1054 postgres | f | t | t | t | t | none | f
1055 Tables:
1056 "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text))
1057
1058 Publication p2
1059 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1060 ----------+------------+---------+---------+---------+-----------+-------------------+----------
1061 postgres | f | t | t | t | t | none | f
1062 Tables:
1063 "public.t1"
1064 "public.t2" WHERE (e = 99)
1065
1066 Publication p3
1067 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1068 ----------+------------+---------+---------+---------+-----------+-------------------+----------
1069 postgres | f | t | t | t | t | none | f
1070 Tables:
1071 "public.t2" WHERE (d = 10)
1072 "public.t3" WHERE (g = 10)
1073 ]]></programlisting></para>
1074
1075 <para>
1076 <command>psql</command> can be used to show the row filter expressions (if
1077 defined) for each table. See that table <literal>t1</literal> is a member
1078 of two publications, but has a row filter only in <literal>p1</literal>.
1079 See that table <literal>t2</literal> is a member of two publications, and
1080 has a different row filter in each of them.
1081 <programlisting><![CDATA[
1082 /* pub # */ \d t1
1083 Table "public.t1"
1084 Column | Type | Collation | Nullable | Default
1085 --------+---------+-----------+----------+---------
1086 a | integer | | not null |
1087 b | integer | | |
1088 c | text | | not null |
1089 Indexes:
1090 "t1_pkey" PRIMARY KEY, btree (a, c)
1091 Publications:
1092 "p1" WHERE ((a > 5) AND (c = 'NSW'::text))
1093 "p2"
1094
1095 /* pub # */ \d t2
1096 Table "public.t2"
1097 Column | Type | Collation | Nullable | Default
1098 --------+---------+-----------+----------+---------
1099 d | integer | | not null |
1100 e | integer | | |
1101 f | integer | | |
1102 Indexes:
1103 "t2_pkey" PRIMARY KEY, btree (d)
1104 Publications:
1105 "p2" WHERE (e = 99)
1106 "p3" WHERE (d = 10)
1107
1108 /* pub # */ \d t3
1109 Table "public.t3"
1110 Column | Type | Collation | Nullable | Default
1111 --------+---------+-----------+----------+---------
1112 g | integer | | not null |
1113 h | integer | | |
1114 i | integer | | |
1115 Indexes:
1116 "t3_pkey" PRIMARY KEY, btree (g)
1117 Publications:
1118 "p3" WHERE (g = 10)
1119 ]]></programlisting></para>
1120
1121 <para>
1122 On the subscriber node, create a table <literal>t1</literal> with the same
1123 definition as the one on the publisher, and also create the subscription
1124 <literal>s1</literal> that subscribes to the publication <literal>p1</literal>.
1125 <programlisting>
1126 /* sub # */ CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
1127 /* sub # */ CREATE SUBSCRIPTION s1
1128 /* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s1'
1129 /* sub - */ PUBLICATION p1;
1130 </programlisting></para>
1131
1132 <para>
1133 Insert some rows. Only the rows satisfying the <literal>t1 WHERE</literal>
1134 clause of publication <literal>p1</literal> are replicated.
1135 <programlisting>
1136 /* pub # */ INSERT INTO t1 VALUES (2, 102, 'NSW');
1137 /* pub # */ INSERT INTO t1 VALUES (3, 103, 'QLD');
1138 /* pub # */ INSERT INTO t1 VALUES (4, 104, 'VIC');
1139 /* pub # */ INSERT INTO t1 VALUES (5, 105, 'ACT');
1140 /* pub # */ INSERT INTO t1 VALUES (6, 106, 'NSW');
1141 /* pub # */ INSERT INTO t1 VALUES (7, 107, 'NT');
1142 /* pub # */ INSERT INTO t1 VALUES (8, 108, 'QLD');
1143 /* pub # */ INSERT INTO t1 VALUES (9, 109, 'NSW');
1144
1145 /* pub # */ SELECT * FROM t1;
1146 a | b | c
1147 ---+-----+-----
1148 2 | 102 | NSW
1149 3 | 103 | QLD
1150 4 | 104 | VIC
1151 5 | 105 | ACT
1152 6 | 106 | NSW
1153 7 | 107 | NT
1154 8 | 108 | QLD
1155 9 | 109 | NSW
1156 (8 rows)
1157 </programlisting>
1158 <programlisting>
1159 /* sub # */ SELECT * FROM t1;
1160 a | b | c
1161 ---+-----+-----
1162 6 | 106 | NSW
1163 9 | 109 | NSW
1164 (2 rows)
1165 </programlisting></para>
1166
1167 <para>
1168 Update some data, where the old and new row values both
1169 satisfy the <literal>t1 WHERE</literal> clause of publication
1170 <literal>p1</literal>. The <command>UPDATE</command> replicates
1171 the change as normal.
1172 <programlisting>
1173 /* pub # */ UPDATE t1 SET b = 999 WHERE a = 6;
1174
1175 /* pub # */ SELECT * FROM t1;
1176 a | b | c
1177 ---+-----+-----
1178 2 | 102 | NSW
1179 3 | 103 | QLD
1180 4 | 104 | VIC
1181 5 | 105 | ACT
1182 7 | 107 | NT
1183 8 | 108 | QLD
1184 9 | 109 | NSW
1185 6 | 999 | NSW
1186 (8 rows)
1187 </programlisting>
1188 <programlisting>
1189 /* sub # */ SELECT * FROM t1;
1190 a | b | c
1191 ---+-----+-----
1192 9 | 109 | NSW
1193 6 | 999 | NSW
1194 (2 rows)
1195 </programlisting></para>
1196
1197 <para>
1198 Update some data, where the old row values did not satisfy
1199 the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
1200 but the new row values do satisfy it. The <command>UPDATE</command> is
1201 transformed into an <command>INSERT</command> and the change is replicated.
1202 See the new row on the subscriber.
1203 <programlisting>
1204 /* pub # */ UPDATE t1 SET a = 555 WHERE a = 2;
1205
1206 /* pub # */ SELECT * FROM t1;
1207 a | b | c
1208 -----+-----+-----
1209 3 | 103 | QLD
1210 4 | 104 | VIC
1211 5 | 105 | ACT
1212 7 | 107 | NT
1213 8 | 108 | QLD
1214 9 | 109 | NSW
1215 6 | 999 | NSW
1216 555 | 102 | NSW
1217 (8 rows)
1218 </programlisting>
1219 <programlisting>
1220 /* sub # */ SELECT * FROM t1;
1221 a | b | c
1222 -----+-----+-----
1223 9 | 109 | NSW
1224 6 | 999 | NSW
1225 555 | 102 | NSW
1226 (3 rows)
1227 </programlisting></para>
1228
1229 <para>
1230 Update some data, where the old row values satisfied
1231 the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
1232 but the new row values do not satisfy it. The <command>UPDATE</command> is
1233 transformed into a <command>DELETE</command> and the change is replicated.
1234 See that the row is removed from the subscriber.
1235 <programlisting>
1236 /* pub # */ UPDATE t1 SET c = 'VIC' WHERE a = 9;
1237
1238 /* pub # */ SELECT * FROM t1;
1239 a | b | c
1240 -----+-----+-----
1241 3 | 103 | QLD
1242 4 | 104 | VIC
1243 5 | 105 | ACT
1244 7 | 107 | NT
1245 8 | 108 | QLD
1246 6 | 999 | NSW
1247 555 | 102 | NSW
1248 9 | 109 | VIC
1249 (8 rows)
1250 </programlisting>
1251 <programlisting>
1252 /* sub # */ SELECT * FROM t1;
1253 a | b | c
1254 -----+-----+-----
1255 6 | 999 | NSW
1256 555 | 102 | NSW
1257 (2 rows)
1258 </programlisting></para>
1259
1260 <para>
1261 The following examples show how the publication parameter
1262 <link linkend="sql-createpublication-params-with-publish-via-partition-root"><literal>publish_via_partition_root</literal></link>
1263 determines whether the row filter of the parent or child table will be used
1264 in the case of partitioned tables.
1265 </para>
1266
1267 <para>
1268 Create a partitioned table on the publisher.
1269 <programlisting>
1270 /* pub # */ CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
1271 /* pub # */ CREATE TABLE child PARTITION OF parent DEFAULT;
1272 </programlisting>
1273 Create the same tables on the subscriber.
1274 <programlisting>
1275 /* sub # */ CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
1276 /* sub # */ CREATE TABLE child PARTITION OF parent DEFAULT;
1277 </programlisting></para>
1278
1279 <para>
1280 Create a publication <literal>p4</literal>, and then subscribe to it. The
1281 publication parameter <literal>publish_via_partition_root</literal> is set
1282 as true. There are row filters defined on both the partitioned table
1283 (<literal>parent</literal>), and on the partition (<literal>child</literal>).
1284 <programlisting><![CDATA[
1285 /* pub # */ CREATE PUBLICATION p4 FOR TABLE parent WHERE (a < 5), child WHERE (a >= 5)
1286 /* pub - */ WITH (publish_via_partition_root=true);
1287 ]]></programlisting>
1288 <programlisting>
1289 /* sub # */ CREATE SUBSCRIPTION s4
1290 /* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s4'
1291 /* sub - */ PUBLICATION p4;
1292 </programlisting></para>
1293
1294 <para>
1295 Insert some values directly into the <literal>parent</literal> and
1296 <literal>child</literal> tables. They replicate using the row filter of
1297 <literal>parent</literal> (because <literal>publish_via_partition_root</literal>
1298 is true).
1299 <programlisting>
1300 /* pub # */ INSERT INTO parent VALUES (2), (4), (6);
1301 /* pub # */ INSERT INTO child VALUES (3), (5), (7);
1302
1303 /* pub # */ SELECT * FROM parent ORDER BY a;
1304 a
1305 ---
1306 2
1307 3
1308 4
1309 5
1310 6
1311 7
1312 (6 rows)
1313 </programlisting>
1314 <programlisting>
1315 /* sub # */ SELECT * FROM parent ORDER BY a;
1316 a
1317 ---
1318 2
1319 3
1320 4
1321 (3 rows)
1322 </programlisting></para>
1323
1324 <para>
1325 Repeat the same test, but with a different value for <literal>publish_via_partition_root</literal>.
1326 The publication parameter <literal>publish_via_partition_root</literal> is
1327 set as false. A row filter is defined on the partition (<literal>child</literal>).
1328 <programlisting><![CDATA[
1329 /* pub # */ DROP PUBLICATION p4;
1330 /* pub # */ CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5)
1331 /* pub - */ WITH (publish_via_partition_root=false);
1332 ]]></programlisting>
1333 <programlisting>
1334 /* sub # */ ALTER SUBSCRIPTION s4 REFRESH PUBLICATION;
1335 </programlisting></para>
1336
1337 <para>
1338 Do the inserts on the publisher same as before. They replicate using the
1339 row filter of <literal>child</literal> (because
1340 <literal>publish_via_partition_root</literal> is false).
1341 <programlisting>
1342 /* pub # */ TRUNCATE parent;
1343 /* pub # */ INSERT INTO parent VALUES (2), (4), (6);
1344 /* pub # */ INSERT INTO child VALUES (3), (5), (7);
1345
1346 /* pub # */ SELECT * FROM parent ORDER BY a;
1347 a
1348 ---
1349 2
1350 3
1351 4
1352 5
1353 6
1354 7
1355 (6 rows)
1356 </programlisting>
1357 <programlisting>
1358 /* sub # */ SELECT * FROM child ORDER BY a;
1359 a
1360 ---
1361 5
1362 6
1363 7
1364 (3 rows)
1365 </programlisting></para>
1366
1367 </sect2>
1368
1369 </sect1>
1370
1371 <sect1 id="logical-replication-col-lists">
1372 <title>Column Lists</title>
1373
1374 <para>
1375 Each publication can optionally specify which columns of each table are
1376 replicated to subscribers. The table on the subscriber side must have at
1377 least all the columns that are published. If no column list is specified,
1378 then all columns on the publisher are replicated.
1379 See <xref linkend="sql-createpublication"/> for details on the syntax.
1380 </para>
1381
1382 <para>
1383 The choice of columns can be based on behavioral or performance reasons.
1384 However, do not rely on this feature for security: a malicious subscriber
1385 is able to obtain data from columns that are not specifically
1386 published. If security is a consideration, protections can be applied
1387 at the publisher side.
1388 </para>
1389
1390 <para>
1391 If no column list is specified, any columns added to the table later are
1392 automatically replicated. This means that having a column list which names
1393 all columns is not the same as having no column list at all.
1394 </para>
1395
1396 <para>
1397 A column list can contain only simple column references. The order
1398 of columns in the list is not preserved.
1399 </para>
1400
1401 <para>
1402 Generated columns can also be specified in a column list. This allows
1403 generated columns to be published, regardless of the publication parameter
1404 <link linkend="sql-createpublication-params-with-publish-generated-columns">
1405 <literal>publish_generated_columns</literal></link>. See
1406 <xref linkend="logical-replication-gencols"/> for details.
1407 </para>
1408
1409 <para>
1410 Specifying a column list when the publication also publishes
1411 <link linkend="sql-createpublication-params-for-tables-in-schema"><literal>FOR TABLES IN SCHEMA</literal></link>
1412 is not supported.
1413 </para>
1414
1415 <para>
1416 For partitioned tables, the publication parameter
1417 <link linkend="sql-createpublication-params-with-publish-via-partition-root"><literal>publish_via_partition_root</literal></link>
1418 determines which column list is used. If <literal>publish_via_partition_root</literal>
1419 is <literal>true</literal>, the root partitioned table's column list is
1420 used. Otherwise, if <literal>publish_via_partition_root</literal> is
1421 <literal>false</literal> (the default), each partition's column list is used.
1422 </para>
1423
1424 <para>
1425 If a publication publishes <command>UPDATE</command> or
1426 <command>DELETE</command> operations, any column list must include the
1427 table's replica identity columns (see
1428 <xref linkend="sql-altertable-replica-identity"/>).
1429 If a publication publishes only <command>INSERT</command> operations, then
1430 the column list may omit replica identity columns.
1431 </para>
1432
1433 <para>
1434 Column lists have no effect for the <literal>TRUNCATE</literal> command.
1435 </para>
1436
1437 <para>
1438 During initial data synchronization, only the published columns are
1439 copied. However, if the subscriber is from a release prior to 15, then
1440 all the columns in the table are copied during initial data synchronization,
1441 ignoring any column lists. If the subscriber is from a release prior to 18,
1442 then initial table synchronization won't copy generated columns even if they
1443 are defined in the publisher.
1444 </para>
1445
1446 <warning id="logical-replication-col-list-combining">
1447 <title>Warning: Combining Column Lists from Multiple Publications</title>
1448 <para>
1449 There's currently no support for subscriptions comprising several
1450 publications where the same table has been published with different
1451 column lists. <xref linkend="sql-createsubscription"/> disallows
1452 creating such subscriptions, but it is still possible to get into
1453 that situation by adding or altering column lists on the publication
1454 side after a subscription has been created.
1455 </para>
1456 <para>
1457 This means changing the column lists of tables on publications that are
1458 already subscribed could lead to errors being thrown on the subscriber
1459 side.
1460 </para>
1461 <para>
1462 If a subscription is affected by this problem, the only way to resume
1463 replication is to adjust one of the column lists on the publication
1464 side so that they all match; and then either recreate the subscription,
1465 or use <link linkend="sql-altersubscription-params-setadddrop-publication">
1466 <literal>ALTER SUBSCRIPTION ... DROP PUBLICATION</literal></link> to
1467 remove one of the offending publications and add it again.
1468 </para>
1469 </warning>
1470
1471 <sect2 id="logical-replication-col-list-examples">
1472 <title>Examples</title>
1473
1474 <para>
1475 Create a table <literal>t1</literal> to be used in the following example.
1476 <programlisting>
1477 /* pub # */ CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id));
1478 </programlisting></para>
1479
1480 <para>
1481 Create a publication <literal>p1</literal>. A column list is defined for
1482 table <literal>t1</literal> to reduce the number of columns that will be
1483 replicated. Notice that the order of column names in the column list does
1484 not matter.
1485 <programlisting>
1486 /* pub # */ CREATE PUBLICATION p1 FOR TABLE t1 (id, b, a, d);
1487 </programlisting></para>
1488
1489 <para>
1490 <literal>psql</literal> can be used to show the column lists (if defined)
1491 for each publication.
1492 <programlisting>
1493 /* pub # */ \dRp+
1494 Publication p1
1495 Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
1496 ----------+------------+---------+---------+---------+-----------+-------------------+----------
1497 postgres | f | t | t | t | t | none | f
1498 Tables:
1499 "public.t1" (id, a, b, d)
1500 </programlisting></para>
1501
1502 <para>
1503 <literal>psql</literal> can be used to show the column lists (if defined)
1504 for each table.
1505 <programlisting>
1506 /* pub # */ \d t1
1507 Table "public.t1"
1508 Column | Type | Collation | Nullable | Default
1509 --------+---------+-----------+----------+---------
1510 id | integer | | not null |
1511 a | text | | |
1512 b | text | | |
1513 c | text | | |
1514 d | text | | |
1515 e | text | | |
1516 Indexes:
1517 "t1_pkey" PRIMARY KEY, btree (id)
1518 Publications:
1519 "p1" (id, a, b, d)
1520 </programlisting></para>
1521
1522 <para>
1523 On the subscriber node, create a table <literal>t1</literal> which now
1524 only needs a subset of the columns that were on the publisher table
1525 <literal>t1</literal>, and also create the subscription
1526 <literal>s1</literal> that subscribes to the publication
1527 <literal>p1</literal>.
1528 <programlisting>
1529 /* sub # */ CREATE TABLE t1(id int, b text, a text, d text, PRIMARY KEY(id));
1530 /* sub # */ CREATE SUBSCRIPTION s1
1531 /* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=s1'
1532 /* sub - */ PUBLICATION p1;
1533 </programlisting></para>
1534
1535 <para>
1536 On the publisher node, insert some rows to table <literal>t1</literal>.
1537 <programlisting>
1538 /* pub # */ INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1');
1539 /* pub # */ INSERT INTO t1 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2');
1540 /* pub # */ INSERT INTO t1 VALUES(3, 'a-3', 'b-3', 'c-3', 'd-3', 'e-3');
1541 /* pub # */ SELECT * FROM t1 ORDER BY id;
1542 id | a | b | c | d | e
1543 ----+-----+-----+-----+-----+-----
1544 1 | a-1 | b-1 | c-1 | d-1 | e-1
1545 2 | a-2 | b-2 | c-2 | d-2 | e-2
1546 3 | a-3 | b-3 | c-3 | d-3 | e-3
1547 (3 rows)
1548 </programlisting></para>
1549
1550 <para>
1551 Only data from the column list of publication <literal>p1</literal> is
1552 replicated.
1553 <programlisting>
1554 /* sub # */ SELECT * FROM t1 ORDER BY id;
1555 id | b | a | d
1556 ----+-----+-----+-----
1557 1 | b-1 | a-1 | d-1
1558 2 | b-2 | a-2 | d-2
1559 3 | b-3 | a-3 | d-3
1560 (3 rows)
1561 </programlisting></para>
1562
1563 </sect2>
1564
1565 </sect1>
1566
1567 <sect1 id="logical-replication-gencols">
1568 <title>Generated Column Replication</title>
1569
1570 <para>
1571 Typically, a table at the subscriber will be defined the same as the
1572 publisher table, so if the publisher table has a <link linkend="ddl-generated-columns">
1573 <literal>GENERATED column</literal></link> then the subscriber table will
1574 have a matching generated column. In this case, it is always the subscriber
1575 table generated column value that is used.
1576 </para>
1577
1578 <para>
1579 For example, note below that subscriber table generated column value comes from the
1580 subscriber column's calculation.
1581 <programlisting>
1582 /* pub # */ CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a + 1) STORED);
1583 /* pub # */ INSERT INTO tab_gen_to_gen VALUES (1),(2),(3);
1584 /* pub # */ CREATE PUBLICATION pub1 FOR TABLE tab_gen_to_gen;
1585 /* pub # */ SELECT * FROM tab_gen_to_gen;
1586 a | b
1587 ---+---
1588 1 | 2
1589 2 | 3
1590 3 | 4
1591 (3 rows)
1592
1593 /* sub # */ CREATE TABLE tab_gen_to_gen (a int, b int GENERATED ALWAYS AS (a * 100) STORED);
1594 /* sub # */ CREATE SUBSCRIPTION sub1 CONNECTION 'dbname=test_pub' PUBLICATION pub1;
1595 /* sub # */ SELECT * from tab_gen_to_gen;
1596 a | b
1597 ---+----
1598 1 | 100
1599 2 | 200
1600 3 | 300
1601 (3 rows)
1602 </programlisting>
1603 </para>
1604
1605 <para>
1606 In fact, prior to version 18.0, logical replication does not publish
1607 <literal>GENERATED</literal> columns at all.
1608 </para>
1609
1610 <para>
1611 But, replicating a generated column to a regular column can sometimes be
1612 desirable.
1613 <tip>
1614 <para>
1615 This feature may be useful when replicating data to a
1616 non-PostgreSQL database via output plugin, especially if the target database
1617 does not support generated columns.
1618 </para>
1619 </tip>
1620 </para>
1621
1622 <para>
1623 Generated columns are not published by default, but users can opt to
1624 publish stored generated columns just like regular ones.
1625 </para>
1626
1627 <para>
1628 There are two ways to do this:
1629 <itemizedlist>
1630 <listitem>
1631 <para>
1632 Set the <command>PUBLICATION</command> parameter
1633 <link linkend="sql-createpublication-params-with-publish-generated-columns">
1634 <literal>publish_generated_columns</literal></link> to <literal>stored</literal>.
1635 This instructs PostgreSQL logical replication to publish current and
1636 future stored generated columns of the publication's tables.
1637 </para>
1638 </listitem>
1639
1640 <listitem>
1641 <para>
1642 Specify a table <link linkend="logical-replication-col-lists">column list</link>
1643 to explicitly nominate which stored generated columns will be published.
1644 </para>
1645
1646 <note>
1647 <para>
1648 When determining which table columns will be published, a column list
1649 takes precedence, overriding the effect of the
1650 <literal>publish_generated_columns</literal> parameter.
1651 </para>
1652 </note>
1653 </listitem>
1654 </itemizedlist>
1655 </para>
1656
1657 <para>
1658 The following table summarizes behavior when there are generated columns
1659 involved in the logical replication. Results are shown for when
1660 publishing generated columns is not enabled, and for when it is
1661 enabled.
1662 </para>
1663
1664 <table id="logical-replication-gencols-table-summary">
1665 <title>Replication Result Summary</title>
1666 <tgroup cols="4">
1667
1668 <thead>
1669 <row>
1670 <entry>Publish generated columns?</entry>
1671 <entry>Publisher table column</entry>
1672 <entry>Subscriber table column</entry>
1673 <entry>Result</entry>
1674 </row>
1675 </thead>
1676
1677 <tbody>
1678 <row>
1679 <entry>No</entry>
1680 <entry>GENERATED</entry>
1681 <entry>GENERATED</entry>
1682 <entry>Publisher table column is not replicated. Use the subscriber table generated column value.</entry>
1683 </row>
1684
1685 <row>
1686 <entry>No</entry>
1687 <entry>GENERATED</entry>
1688 <entry>regular</entry>
1689 <entry>Publisher table column is not replicated. Use the subscriber table regular column default value.</entry>
1690 </row>
1691
1692 <row>
1693 <entry>No</entry>
1694 <entry>GENERATED</entry>
1695 <entry>--missing--</entry>
1696 <entry>Publisher table column is not replicated. Nothing happens.</entry>
1697 </row>
1698
1699 <row>
1700 <entry>Yes</entry>
1701 <entry>GENERATED</entry>
1702 <entry>GENERATED</entry>
1703 <entry>ERROR. Not supported.</entry>
1704 </row>
1705
1706 <row>
1707 <entry>Yes</entry>
1708 <entry>GENERATED</entry>
1709 <entry>regular</entry>
1710 <entry>Publisher table column value is replicated to the subscriber table column.</entry>
1711 </row>
1712
1713 <row>
1714 <entry>Yes</entry>
1715 <entry>GENERATED</entry>
1716 <entry>--missing--</entry>
1717 <entry>ERROR. The column is reported as missing from the subscriber table.</entry>
1718 </row>
1719 </tbody>
1720 </tgroup>
1721 </table>
1722
1723 <warning>
1724 <para>
1725 There's currently no support for subscriptions comprising several
1726 publications where the same table has been published with different column
1727 lists. See <xref linkend="logical-replication-col-lists"/>.
1728 </para>
1729
1730 <para>
1731 This same situation can occur if one publication is publishing generated
1732 columns, while another publication in the same subscription is not
1733 publishing generated columns for the same table.
1734 </para>
1735 </warning>
1736
1737 <note>
1738 <para>
1739 If the subscriber is from a release prior to 18, then initial table
1740 synchronization won't copy generated columns even if they are defined in
1741 the publisher.
1742 </para>
1743 </note>
1744 </sect1>
1745
1746 <sect1 id="logical-replication-conflicts">
1747 <title>Conflicts</title>
1748
1749 <para>
1750 Logical replication behaves similarly to normal DML operations in that
1751 the data will be updated even if it was changed locally on the subscriber
1752 node. If incoming data violates any constraints the replication will
1753 stop. This is referred to as a <firstterm>conflict</firstterm>. When
1754 replicating <command>UPDATE</command> or <command>DELETE</command>
1755 operations, missing data is also considered as a
1756 <firstterm>conflict</firstterm>, but does not result in an error and such
1757 operations will simply be skipped.
1758 </para>
1759
1760 <para>
1761 Additional logging is triggered, and the conflict statistics are collected (displayed in the
1762 <link linkend="monitoring-pg-stat-subscription-stats"><structname>pg_stat_subscription_stats</structname></link> view)
1763 in the following <firstterm>conflict</firstterm> cases:
1764 <variablelist>
1765 <varlistentry id="conflict-insert-exists" xreflabel="insert_exists">
1766 <term><literal>insert_exists</literal></term>
1767 <listitem>
1768 <para>
1769 Inserting a row that violates a <literal>NOT DEFERRABLE</literal>
1770 unique constraint. Note that to log the origin and commit
1771 timestamp details of the conflicting key,
1772 <link linkend="guc-track-commit-timestamp"><varname>track_commit_timestamp</varname></link>
1773 should be enabled on the subscriber. In this case, an error will be
1774 raised until the conflict is resolved manually.
1775 </para>
1776 </listitem>
1777 </varlistentry>
1778 <varlistentry id="conflict-update-origin-differs" xreflabel="update_origin_differs">
1779 <term><literal>update_origin_differs</literal></term>
1780 <listitem>
1781 <para>
1782 Updating a row that was previously modified by another origin.
1783 Note that this conflict can only be detected when
1784 <link linkend="guc-track-commit-timestamp"><varname>track_commit_timestamp</varname></link>
1785 is enabled on the subscriber. Currently, the update is always applied
1786 regardless of the origin of the local row.
1787 </para>
1788 </listitem>
1789 </varlistentry>
1790 <varlistentry id="conflict-update-exists" xreflabel="update_exists">
1791 <term><literal>update_exists</literal></term>
1792 <listitem>
1793 <para>
1794 The updated value of a row violates a <literal>NOT DEFERRABLE</literal>
1795 unique constraint. Note that to log the origin and commit
1796 timestamp details of the conflicting key,
1797 <link linkend="guc-track-commit-timestamp"><varname>track_commit_timestamp</varname></link>
1798 should be enabled on the subscriber. In this case, an error will be
1799 raised until the conflict is resolved manually. Note that when updating a
1800 partitioned table, if the updated row value satisfies another partition
1801 constraint resulting in the row being inserted into a new partition, the
1802 <literal>insert_exists</literal> conflict may arise if the new row
1803 violates a <literal>NOT DEFERRABLE</literal> unique constraint.
1804 </para>
1805 </listitem>
1806 </varlistentry>
1807 <varlistentry id="conflict-update-missing" xreflabel="update_missing">
1808 <term><literal>update_missing</literal></term>
1809 <listitem>
1810 <para>
1811 The row to be updated was not found. The update will simply be
1812 skipped in this scenario.
1813 </para>
1814 </listitem>
1815 </varlistentry>
1816 <varlistentry id="conflict-delete-origin-differs" xreflabel="delete_origin_differs">
1817 <term><literal>delete_origin_differs</literal></term>
1818 <listitem>
1819 <para>
1820 Deleting a row that was previously modified by another origin. Note that
1821 this conflict can only be detected when
1822 <link linkend="guc-track-commit-timestamp"><varname>track_commit_timestamp</varname></link>
1823 is enabled on the subscriber. Currently, the delete is always applied
1824 regardless of the origin of the local row.
1825 </para>
1826 </listitem>
1827 </varlistentry>
1828 <varlistentry id="conflict-delete-missing" xreflabel="delete_missing">
1829 <term><literal>delete_missing</literal></term>
1830 <listitem>
1831 <para>
1832 The row to be deleted was not found. The delete will simply be
1833 skipped in this scenario.
1834 </para>
1835 </listitem>
1836 </varlistentry>
1837 <varlistentry id="conflict-multiple-unique-conflicts" xreflabel="multiple_unique_conflicts">
1838 <term><literal>multiple_unique_conflicts</literal></term>
1839 <listitem>
1840 <para>
1841 Inserting or updating a row violates multiple
1842 <literal>NOT DEFERRABLE</literal> unique constraints. Note that to log
1843 the origin and commit timestamp details of conflicting keys, ensure
1844 that <link linkend="guc-track-commit-timestamp"><varname>track_commit_timestamp</varname></link>
1845 is enabled on the subscriber. In this case, an error will be raised until
1846 the conflict is resolved manually.
1847 </para>
1848 </listitem>
1849 </varlistentry>
1850 </variablelist>
1851 Note that there are other conflict scenarios, such as exclusion constraint
1852 violations. Currently, we do not provide additional details for them in the
1853 log.
1854 </para>
1855
1856 <para>
1857 The log format for logical replication conflicts is as follows:
1858 <synopsis>
1859 LOG: conflict detected on relation "<replaceable>schemaname</replaceable>.<replaceable>tablename</replaceable>": conflict=<replaceable>conflict_type</replaceable>
1860 DETAIL: <replaceable class="parameter">detailed_explanation</replaceable>.
1861 {<replaceable class="parameter">detail_values</replaceable> [; ... ]}.
1862
1863 <phrase>where <replaceable class="parameter">detail_values</replaceable> is one of:</phrase>
1864
1865 <literal>Key</literal> (<replaceable>column_name</replaceable> <optional>, ...</optional>)=(<replaceable>column_value</replaceable> <optional>, ...</optional>)
1866 <literal>existing local row</literal> <optional>(<replaceable>column_name</replaceable> <optional>, ...</optional>)=</optional>(<replaceable>column_value</replaceable> <optional>, ...</optional>)
1867 <literal>remote row</literal> <optional>(<replaceable>column_name</replaceable> <optional>, ...</optional>)=</optional>(<replaceable>column_value</replaceable> <optional>, ...</optional>)
1868 <literal>replica identity</literal> {(<replaceable>column_name</replaceable> <optional>, ...</optional>)=(<replaceable>column_value</replaceable> <optional>, ...</optional>) | full <optional>(<replaceable>column_name</replaceable> <optional>, ...</optional>)=</optional>(<replaceable>column_value</replaceable> <optional>, ...</optional>)}
1869 </synopsis>
1870
1871 The log provides the following information:
1872 <variablelist>
1873 <varlistentry>
1874 <term><literal>LOG</literal></term>
1875 <listitem>
1876 <itemizedlist>
1877 <listitem>
1878 <para>
1879 <replaceable>schemaname</replaceable>.<replaceable>tablename</replaceable>
1880 identifies the local relation involved in the conflict.
1881 </para>
1882 </listitem>
1883 <listitem>
1884 <para>
1885 <replaceable>conflict_type</replaceable> is the type of conflict that occurred
1886 (e.g., <literal>insert_exists</literal>, <literal>update_exists</literal>).
1887 </para>
1888 </listitem>
1889 </itemizedlist>
1890 </listitem>
1891 </varlistentry>
1892
1893 <varlistentry>
1894 <term><literal>DETAIL</literal></term>
1895 <listitem>
1896 <itemizedlist>
1897 <listitem>
1898 <para>
1899 <replaceable class="parameter">detailed_explanation</replaceable> includes
1900 the origin, transaction ID, and commit timestamp of the transaction that
1901 modified the existing local row, if available.
1902 </para>
1903 </listitem>
1904 <listitem>
1905 <para>
1906 The <literal>Key</literal> section includes the key values of the local
1907 row that violated a unique constraint for
1908 <literal>insert_exists</literal>, <literal>update_exists</literal> or
1909 <literal>multiple_unique_conflicts</literal> conflicts.
1910 </para>
1911 </listitem>
1912 <listitem>
1913 <para>
1914 The <literal>existing local row</literal> section includes the local
1915 row if its origin differs from the remote row for
1916 <literal>update_origin_differs</literal> or <literal>delete_origin_differs</literal>
1917 conflicts, or if the key value conflicts with the remote row for
1918 <literal>insert_exists</literal>, <literal>update_exists</literal> or
1919 <literal>multiple_unique_conflicts</literal> conflicts.
1920 </para>
1921 </listitem>
1922 <listitem>
1923 <para>
1924 The <literal>remote row</literal> section includes the new row from
1925 the remote insert or update operation that caused the conflict. Note that
1926 for an update operation, the column value of the new row will be null
1927 if the value is unchanged and toasted.
1928 </para>
1929 </listitem>
1930 <listitem>
1931 <para>
1932 The <literal>replica identity</literal> section includes the replica
1933 identity key values that were used to search for the existing local
1934 row to be updated or deleted. This may include the full row value
1935 if the local relation is marked with
1936 <link linkend="sql-altertable-replica-identity-full"><literal>REPLICA IDENTITY FULL</literal></link>.
1937 </para>
1938 </listitem>
1939 <listitem>
1940 <para>
1941 <replaceable class="parameter">column_name</replaceable> is the column name.
1942 For <literal>existing local row</literal>, <literal>remote row</literal>,
1943 and <literal>replica identity full</literal> cases, column names are
1944 logged only if the user lacks the privilege to access all columns of
1945 the table. If column names are present, they appear in the same order
1946 as the corresponding column values.
1947 </para>
1948 </listitem>
1949 <listitem>
1950 <para>
1951 <replaceable class="parameter">column_value</replaceable> is the column value.
1952 The large column values are truncated to 64 bytes.
1953 </para>
1954 </listitem>
1955 <listitem>
1956 <para>
1957 Note that in case of <literal>multiple_unique_conflicts</literal> conflict,
1958 multiple <replaceable class="parameter">detailed_explanation</replaceable>
1959 and <replaceable class="parameter">detail_values</replaceable> lines
1960 will be generated, each detailing the conflict information associated
1961 with distinct unique
1962 constraints.
1963 </para>
1964 </listitem>
1965 </itemizedlist>
1966 </listitem>
1967 </varlistentry>
1968 </variablelist>
1969 </para>
1970
1971 <para>
1972 Logical replication operations are performed with the privileges of the role
1973 which owns the subscription. Permissions failures on target tables will
1974 cause replication conflicts, as will enabled
1975 <link linkend="ddl-rowsecurity">row-level security</link> on target tables
1976 that the subscription owner is subject to, without regard to whether any
1977 policy would ordinarily reject the <command>INSERT</command>,
1978 <command>UPDATE</command>, <command>DELETE</command> or
1979 <command>TRUNCATE</command> which is being replicated. This restriction on
1980 row-level security may be lifted in a future version of
1981 <productname>PostgreSQL</productname>.
1982 </para>
1983
1984 <para>
1985 A conflict that produces an error will stop the replication; it must be
1986 resolved manually by the user. Details about the conflict can be found in
1987 the subscriber's server log.
1988 </para>
1989
1990 <para>
1991 The resolution can be done either by changing data or permissions on the subscriber so
1992 that it does not conflict with the incoming change or by skipping the
1993 transaction that conflicts with the existing data. When a conflict produces
1994 an error, the replication won't proceed, and the logical replication worker will
1995 emit the following kind of message to the subscriber's server log:
1996 <screen>
1997 ERROR: conflict detected on relation "public.test": conflict=insert_exists
1998 DETAIL: Key already exists in unique index "t_pkey", which was modified locally in transaction 740 at 2024-06-26 10:47:04.727375+08.
1999 Key (c)=(1); existing local row (1, 'local'); remote row (1, 'remote').
2000 CONTEXT: processing remote data for replication origin "pg_16395" during "INSERT" for replication target relation "public.test" in transaction 725 finished at 0/14C0378
2001 </screen>
2002 The LSN of the transaction that contains the change violating the constraint and
2003 the replication origin name can be found from the server log (LSN 0/14C0378 and
2004 replication origin <literal>pg_16395</literal> in the above case). The
2005 transaction that produced the conflict can be skipped by using
2006 <link linkend="sql-altersubscription-params-skip"><command>ALTER SUBSCRIPTION ... SKIP</command></link>
2007 with the finish LSN
2008 (i.e., LSN 0/14C0378). The finish LSN could be an LSN at which the transaction
2009 is committed or prepared on the publisher. Alternatively, the transaction can
2010 also be skipped by calling the <link linkend="pg-replication-origin-advance">
2011 <function>pg_replication_origin_advance()</function></link> function.
2012 Before using this function, the subscription needs to be disabled temporarily
2013 either by <link linkend="sql-altersubscription-params-disable">
2014 <command>ALTER SUBSCRIPTION ... DISABLE</command></link> or, the
2015 subscription can be used with the
2016 <link linkend="sql-createsubscription-params-with-disable-on-error"><literal>disable_on_error</literal></link>
2017 option. Then, you can use <function>pg_replication_origin_advance()</function>
2018 function with the <parameter>node_name</parameter> (i.e., <literal>pg_16395</literal>)
2019 and the next LSN of the finish LSN (i.e., 0/14C0379). The current position of
2020 origins can be seen in the <link linkend="view-pg-replication-origin-status">
2021 <structname>pg_replication_origin_status</structname></link> system view.
2022 Please note that skipping the whole transaction includes skipping changes that
2023 might not violate any constraint. This can easily make the subscriber
2024 inconsistent.
2025 The additional details regarding conflicting rows, such as their origin and
2026 commit timestamp can be seen in the <literal>DETAIL</literal> line of the
2027 log. But note that this information is only available when
2028 <link linkend="guc-track-commit-timestamp"><varname>track_commit_timestamp</varname></link>
2029 is enabled on the subscriber. Users can use this information to decide
2030 whether to retain the local change or adopt the remote alteration. For
2031 instance, the <literal>DETAIL</literal> line in the above log indicates that
2032 the existing row was modified locally. Users can manually perform a
2033 remote-change-win.
2034 </para>
2035
2036 <para>
2037 When the
2038 <link linkend="sql-createsubscription-params-with-streaming"><literal>streaming</literal></link>
2039 mode is <literal>parallel</literal>, the finish LSN of failed transactions
2040 may not be logged. In that case, it may be necessary to change the streaming
2041 mode to <literal>on</literal> or <literal>off</literal> and cause the same
2042 conflicts again so the finish LSN of the failed transaction will be written
2043 to the server log. For the usage of finish LSN, please refer to <link
2044 linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION ...
2045 SKIP</command></link>.
2046 </para>
2047 </sect1>
2048
2049 <sect1 id="logical-replication-restrictions">
2050 <title>Restrictions</title>
2051
2052 <para>
2053 Logical replication currently has the following restrictions or missing
2054 functionality. These might be addressed in future releases.
2055 </para>
2056
2057 <itemizedlist>
2058 <listitem>
2059 <para>
2060 The database schema and DDL commands are not replicated. The initial
2061 schema can be copied by hand using <literal>pg_dump
2062 --schema-only</literal>. Subsequent schema changes would need to be kept
2063 in sync manually. (Note, however, that there is no need for the schemas
2064 to be absolutely the same on both sides.) Logical replication is robust
2065 when schema definitions change in a live database: When the schema is
2066 changed on the publisher and replicated data starts arriving at the
2067 subscriber but does not fit into the table schema, replication will error
2068 until the schema is updated. In many cases, intermittent errors can be
2069 avoided by applying additive schema changes to the subscriber first.
2070 </para>
2071 </listitem>
2072
2073 <listitem>
2074 <para>
2075 Sequence data is not replicated. The data in serial or identity columns
2076 backed by sequences will of course be replicated as part of the table,
2077 but the sequence itself would still show the start value on the
2078 subscriber. If the subscriber is used as a read-only database, then this
2079 should typically not be a problem. If, however, some kind of switchover
2080 or failover to the subscriber database is intended, then the sequences
2081 would need to be updated to the latest values, either by copying the
2082 current data from the publisher (perhaps
2083 using <command>pg_dump</command>) or by determining a sufficiently high
2084 value from the tables themselves.
2085 </para>
2086 </listitem>
2087
2088 <listitem>
2089 <para>
2090 Replication of <command>TRUNCATE</command> commands is supported, but
2091 some care must be taken when truncating groups of tables connected by
2092 foreign keys. When replicating a truncate action, the subscriber will
2093 truncate the same group of tables that was truncated on the publisher,
2094 either explicitly specified or implicitly collected via
2095 <literal>CASCADE</literal>, minus tables that are not part of the
2096 subscription. This will work correctly if all affected tables are part
2097 of the same subscription. But if some tables to be truncated on the
2098 subscriber have foreign-key links to tables that are not part of the same
2099 (or any) subscription, then the application of the truncate action on the
2100 subscriber will fail.
2101 </para>
2102 </listitem>
2103
2104 <listitem>
2105 <para>
2106 Large objects (see <xref linkend="largeobjects"/>) are not replicated.
2107 There is no workaround for that, other than storing data in normal
2108 tables.
2109 </para>
2110 </listitem>
2111
2112 <listitem>
2113 <para>
2114 Replication is only supported by tables, including partitioned tables.
2115 Attempts to replicate other types of relations, such as views, materialized
2116 views, or foreign tables, will result in an error.
2117 </para>
2118 </listitem>
2119
2120 <listitem>
2121 <para>
2122 When replicating between partitioned tables, the actual replication
2123 originates, by default, from the leaf partitions on the publisher, so
2124 partitions on the publisher must also exist on the subscriber as valid
2125 target tables. (They could either be leaf partitions themselves, or they
2126 could be further subpartitioned, or they could even be independent
2127 tables.) Publications can also specify that changes are to be replicated
2128 using the identity and schema of the partitioned root table instead of
2129 that of the individual leaf partitions in which the changes actually
2130 originate (see
2131 <link linkend="sql-createpublication-params-with-publish-via-partition-root"><literal>publish_via_partition_root</literal></link>
2132 parameter of <command>CREATE PUBLICATION</command>).
2133 </para>
2134 </listitem>
2135
2136 <listitem>
2137 <para>
2138 When using
2139 <link linkend="sql-altertable-replica-identity-full"><literal>REPLICA IDENTITY FULL</literal></link>
2140 on published tables, it is important to note that the <literal>UPDATE</literal>
2141 and <literal>DELETE</literal> operations cannot be applied to subscribers
2142 if the tables include attributes with datatypes (such as point or box)
2143 that do not have a default operator class for B-tree or Hash. However,
2144 this limitation can be overcome by ensuring that the table has a primary
2145 key or replica identity defined for it.
2146 </para>
2147 </listitem>
2148 </itemizedlist>
2149 </sect1>
2150
2151 <sect1 id="logical-replication-architecture">
2152 <title>Architecture</title>
2153
2154 <para>
2155 Logical replication is built with an architecture similar to physical
2156 streaming replication (see <xref linkend="streaming-replication"/>). It is
2157 implemented by <literal>walsender</literal> and <literal>apply</literal>
2158 processes. The walsender process starts logical decoding (described
2159 in <xref linkend="logicaldecoding"/>) of the WAL and loads the standard
2160 logical decoding output plugin (<literal>pgoutput</literal>). The plugin
2161 transforms the changes read
2162 from WAL to the logical replication protocol
2163 (see <xref linkend="protocol-logical-replication"/>) and filters the data
2164 according to the publication specification. The data is then continuously
2165 transferred using the streaming replication protocol to the apply worker,
2166 which maps the data to local tables and applies the individual changes as
2167 they are received, in correct transactional order.
2168 </para>
2169
2170 <para>
2171 The apply process on the subscriber database always runs with
2172 <link linkend="guc-session-replication-role"><varname>session_replication_role</varname></link>
2173 set to <literal>replica</literal>. This means that, by default,
2174 triggers and rules will not fire on a subscriber. Users can optionally choose to
2175 enable triggers and rules on a table using the
2176 <link linkend="sql-altertable"><command>ALTER TABLE</command></link> command
2177 and the <literal>ENABLE TRIGGER</literal> and <literal>ENABLE RULE</literal>
2178 clauses.
2179 </para>
2180
2181 <para>
2182 The logical replication apply process currently only fires row triggers,
2183 not statement triggers. The initial table synchronization, however, is
2184 implemented like a <command>COPY</command> command and thus fires both row
2185 and statement triggers for <command>INSERT</command>.
2186 </para>
2187
2188 <sect2 id="logical-replication-snapshot">
2189 <title>Initial Snapshot</title>
2190 <para>
2191 The initial data in existing subscribed tables is snapshotted and
2192 copied in parallel instances of a special kind of apply process.
2193 These special apply processes are dedicated table synchronization
2194 workers, spawned for each table to be synchronized. Each table
2195 synchronization process will create its own replication slot and
2196 copy the existing data. As soon as the copy is finished the table
2197 contents will become visible to other backends. Once existing data
2198 is copied, the worker enters synchronization mode, which ensures
2199 that the table is brought up to a synchronized state with the main
2200 apply process by streaming any changes that happened during the
2201 initial data copy using standard logical replication. During this
2202 synchronization phase, the changes are applied and committed in the same
2203 order as they happened on the publisher. Once synchronization is done,
2204 control of the replication of the table is given back to the main apply
2205 process where replication continues as normal.
2206 </para>
2207 <note>
2208 <para>
2209 The publication
2210 <link linkend="sql-createpublication-params-with-publish"><literal>publish</literal></link>
2211 parameter only affects what DML operations will be replicated. The
2212 initial data synchronization does not take this parameter into account
2213 when copying the existing table data.
2214 </para>
2215 </note>
2216 <note>
2217 <para>
2218 If a table synchronization worker fails during copy, the apply worker
2219 detects the failure and respawns the table synchronization worker to
2220 continue the synchronization process. This behaviour ensures that
2221 transient errors do not permanently disrupt the replication setup. See
2222 also <link linkend="guc-wal-retrieve-retry-interval"><varname>wal_retrieve_retry_interval</varname></link>.
2223 </para>
2224 </note>
2225 </sect2>
2226 </sect1>
2227
2228 <sect1 id="logical-replication-monitoring">
2229 <title>Monitoring</title>
2230
2231 <para>
2232 Because logical replication is based on a similar architecture as
2233 <link linkend="streaming-replication">physical streaming replication</link>,
2234 the monitoring on a publication node is similar to monitoring of a
2235 physical replication primary
2236 (see <xref linkend="streaming-replication-monitoring"/>).
2237 </para>
2238
2239 <para>
2240 The monitoring information about subscription is visible in
2241 <link linkend="monitoring-pg-stat-subscription">
2242 <structname>pg_stat_subscription</structname></link>.
2243 This view contains one row for every subscription worker. A subscription
2244 can have zero or more active subscription workers depending on its state.
2245 </para>
2246
2247 <para>
2248 Normally, there is a single apply process running for an enabled
2249 subscription. A disabled subscription or a crashed subscription will have
2250 zero rows in this view. If the initial data synchronization of any
2251 table is in progress, there will be additional workers for the tables
2252 being synchronized. Moreover, if the
2253 <link linkend="sql-createsubscription-params-with-streaming"><literal>streaming</literal></link>
2254 transaction is applied in parallel, there may be additional parallel apply
2255 workers.
2256 </para>
2257 </sect1>
2258
2259 <sect1 id="logical-replication-security">
2260 <title>Security</title>
2261
2262 <para>
2263 The role used for the replication connection must have
2264 the <literal>REPLICATION</literal> attribute (or be a superuser). If the
2265 role lacks <literal>SUPERUSER</literal> and <literal>BYPASSRLS</literal>,
2266 publisher row security policies can execute. If the role does not trust
2267 all table owners, include <literal>options=-crow_security=off</literal> in
2268 the connection string; if a table owner then adds a row security policy,
2269 that setting will cause replication to halt rather than execute the policy.
2270 Access for the role must be configured in <filename>pg_hba.conf</filename>
2271 and it must have the <literal>LOGIN</literal> attribute.
2272 </para>
2273
2274 <para>
2275 The name of the output plugin used by the replication connection must be
2276 included in the server's <xref linkend="guc-output-plugin-libraries"/>. (For
2277 subscriptions, the plugin name that is used is <literal>pgoutput</literal>.)
2278 Superusers may modify the trusted list per-connection, by including
2279 <literal>options=-coutput_plugin_libraries=...</literal> in the connection
2280 string.
2281 </para>
2282
2283 <para>
2284 In order to be able to copy the initial table data, the role used for the
2285 replication connection must have the <literal>SELECT</literal> privilege on
2286 a published table (or be a superuser).
2287 </para>
2288
2289 <para>
2290 To create a publication, the user must have the <literal>CREATE</literal>
2291 privilege in the database.
2292 </para>
2293
2294 <para>
2295 To add tables to a publication, the user must have ownership rights on the
2296 table. To add all tables in schema to a publication, the user must be a
2297 superuser. To create a publication that publishes all tables or all tables in
2298 schema automatically, the user must be a superuser.
2299 </para>
2300
2301 <para>
2302 There are currently no privileges on publications. Any subscription (that
2303 is able to connect) can access any publication. Thus, if you intend to
2304 hide some information from particular subscribers, such as by using row
2305 filters or column lists, or by not adding the whole table to the
2306 publication, be aware that other publications in the same database could
2307 expose the same information. Publication privileges might be added to
2308 <productname>PostgreSQL</productname> in the future to allow for
2309 finer-grained access control.
2310 </para>
2311
2312 <para>
2313 To create a subscription, the user must have the privileges of
2314 the <literal>pg_create_subscription</literal> role, as well as
2315 <literal>CREATE</literal> privileges on the database.
2316 </para>
2317
2318 <para>
2319 The subscription apply process will, at a session level, run with the
2320 privileges of the subscription owner. However, when performing an insert,
2321 update, delete, or truncate operation on a particular table, it will switch
2322 roles to the table owner and perform the operation with the table owner's
2323 privileges. This means that the subscription owner needs to be able to
2324 <literal>SET ROLE</literal> to each role that owns a replicated table.
2325 </para>
2326
2327 <para>
2328 If the subscription has been configured with
2329 <literal>run_as_owner = true</literal>, then no user switching will
2330 occur. Instead, all operations will be performed with the permissions
2331 of the subscription owner. In this case, the subscription owner only
2332 needs privileges to <literal>SELECT</literal>, <literal>INSERT</literal>,
2333 <literal>UPDATE</literal>, and <literal>DELETE</literal> from the
2334 target table, and does not need privileges to <literal>SET ROLE</literal>
2335 to the table owner. However, this also means that any user who owns
2336 a table into which replication is happening can execute arbitrary code with
2337 the privileges of the subscription owner. For example, they could do this
2338 by simply attaching a trigger to one of the tables which they own.
2339 Because it is usually undesirable to allow one role to freely assume
2340 the privileges of another, this option should be avoided unless user
2341 security within the database is of no concern.
2342 </para>
2343
2344 <para>
2345 On the publisher, privileges are only checked once at the start of a
2346 replication connection and are not re-checked as each change record is read.
2347 </para>
2348
2349 <para>
2350 On the subscriber, the subscription owner's privileges are re-checked for
2351 each transaction when applied. If a worker is in the process of applying a
2352 transaction when the ownership of the subscription is changed by a
2353 concurrent transaction, the application of the current transaction will
2354 continue under the old owner's privileges.
2355 </para>
2356 </sect1>
2357
2358 <sect1 id="logical-replication-config">
2359 <title>Configuration Settings</title>
2360
2361 <para>
2362 Logical replication requires several configuration options to be set. These
2363 options are relevant only on one side of the replication.
2364 </para>
2365
2366 <sect2 id="logical-replication-config-publisher">
2367 <title>Publishers</title>
2368
2369 <para>
2370 <link linkend="guc-wal-level"><varname>wal_level</varname></link> must be
2371 set to <literal>logical</literal>.
2372 </para>
2373
2374 <para>
2375 <link linkend="guc-max-replication-slots"><varname>max_replication_slots</varname></link>
2376 must be set to at least the number of subscriptions expected to connect,
2377 plus some reserve for table synchronization.
2378 </para>
2379
2380 <para>
2381 Logical replication slots are also affected by
2382 <link linkend="guc-idle-replication-slot-timeout"><varname>idle_replication_slot_timeout</varname></link>.
2383 </para>
2384
2385 <para>
2386 <link linkend="guc-max-wal-senders"><varname>max_wal_senders</varname></link>
2387 should be set to at least the same as
2388 <varname>max_replication_slots</varname>, plus the number of physical
2389 replicas that are connected at the same time.
2390 </para>
2391
2392 <para>
2393 Logical replication walsender is also affected by
2394 <link linkend="guc-wal-sender-timeout"><varname>wal_sender_timeout</varname></link>.
2395 </para>
2396
2397 </sect2>
2398
2399 <sect2 id="logical-replication-config-subscriber">
2400 <title>Subscribers</title>
2401
2402 <para>
2403 <link linkend="guc-max-active-replication-origins"><varname>max_active_replication_origins</varname></link>
2404 must be set to at least the number of subscriptions that will be added to
2405 the subscriber, plus some reserve for table synchronization.
2406 </para>
2407
2408 <para>
2409 <link linkend="guc-max-logical-replication-workers"><varname>max_logical_replication_workers</varname></link>
2410 must be set to at least the number of subscriptions (for leader apply
2411 workers), plus some reserve for the table synchronization workers and
2412 parallel apply workers.
2413 </para>
2414
2415 <para>
2416 <link linkend="guc-max-worker-processes"><varname>max_worker_processes</varname></link>
2417 may need to be adjusted to accommodate for replication workers, at least
2418 (<link linkend="guc-max-logical-replication-workers"><varname>max_logical_replication_workers</varname></link>
2419 + <literal>1</literal>). Note, some extensions and parallel queries also
2420 take worker slots from <varname>max_worker_processes</varname>.
2421 </para>
2422
2423 <para>
2424 <link linkend="guc-max-sync-workers-per-subscription"><varname>max_sync_workers_per_subscription</varname></link>
2425 controls the amount of parallelism of the initial data copy during the
2426 subscription initialization or when new tables are added.
2427 </para>
2428
2429 <para>
2430 <link linkend="guc-max-parallel-apply-workers-per-subscription"><varname>max_parallel_apply_workers_per_subscription</varname></link>
2431 controls the amount of parallelism for streaming of in-progress
2432 transactions with subscription parameter
2433 <literal>streaming = parallel</literal>.
2434 </para>
2435
2436 <para>
2437 Logical replication workers are also affected by
2438 <link linkend="guc-wal-receiver-timeout"><varname>wal_receiver_timeout</varname></link>,
2439 <link linkend="guc-wal-receiver-status-interval"><varname>wal_receiver_status_interval</varname></link> and
2440 <link linkend="guc-wal-retrieve-retry-interval"><varname>wal_retrieve_retry_interval</varname></link>.
2441 </para>
2442
2443 </sect2>
2444
2445 </sect1>
2446
2447 <sect1 id="logical-replication-upgrade">
2448 <title>Upgrade</title>
2449
2450 <para>
2451 Migration of <glossterm linkend="glossary-logical-replication-cluster">logical replication clusters</glossterm>
2452 is possible only when all the members of the old logical replication
2453 clusters are version 17.0 or later.
2454 </para>
2455
2456 <sect2 id="prepare-publisher-upgrades">
2457 <title>Prepare for Publisher Upgrades</title>
2458
2459 <para>
2460 <application>pg_upgrade</application> attempts to migrate logical
2461 slots. This helps avoid the need for manually defining the same
2462 logical slots on the new publisher. Migration of logical slots is
2463 only supported when the old cluster is version 17.0 or later.
2464 Logical slots on clusters before version 17.0 will silently be
2465 ignored.
2466 </para>
2467
2468 <para>
2469 Before you start upgrading the publisher cluster, ensure that the
2470 subscription is temporarily disabled, by executing
2471 <link linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>.
2472 Re-enable the subscription after the upgrade.
2473 </para>
2474
2475 <para>
2476 There are some prerequisites for <application>pg_upgrade</application> to
2477 be able to upgrade the logical slots. If these are not met an error
2478 will be reported.
2479 </para>
2480
2481 <itemizedlist>
2482 <listitem>
2483 <para>
2484 The new cluster must have
2485 <link linkend="guc-wal-level"><varname>wal_level</varname></link> as
2486 <literal>logical</literal>.
2487 </para>
2488 </listitem>
2489 <listitem>
2490 <para>
2491 The new cluster must have
2492 <link linkend="guc-max-replication-slots"><varname>max_replication_slots</varname></link>
2493 configured to a value greater than or equal to the number of slots
2494 present in the old cluster.
2495 </para>
2496 </listitem>
2497 <listitem>
2498 <para>
2499 The output plugins referenced by the slots in the old cluster must be
2500 installed in the new PostgreSQL executable directory. They must also be
2501 included in the new cluster's <xref linkend="guc-output-plugin-libraries"/>;
2502 see that parameter's documentation for safety information.
2503 </para>
2504 </listitem>
2505 <listitem>
2506 <para>
2507 The old cluster has replicated all the transactions and logical decoding
2508 messages to subscribers.
2509 </para>
2510 </listitem>
2511 <listitem>
2512 <para>
2513 All slots on the old cluster must be usable, i.e., there are no slots
2514 whose
2515 <link linkend="view-pg-replication-slots">pg_replication_slots</link>.<structfield>conflicting</structfield>
2516 is not <literal>true</literal>.
2517 </para>
2518 </listitem>
2519 <listitem>
2520 <para>
2521 The new cluster must not have permanent logical slots, i.e.,
2522 there must be no slots where
2523 <link linkend="view-pg-replication-slots">pg_replication_slots</link>.<structfield>temporary</structfield>
2524 is <literal>false</literal>.
2525 </para>
2526 </listitem>
2527 </itemizedlist>
2528 </sect2>
2529
2530 <sect2 id="prepare-subscriber-upgrades">
2531 <title>Prepare for Subscriber Upgrades</title>
2532
2533 <para>
2534 Setup the <link linkend="logical-replication-config-subscriber">
2535 subscriber configurations</link> in the new subscriber.
2536 <application>pg_upgrade</application> attempts to migrate subscription
2537 dependencies which includes the subscription's table information present in
2538 <link linkend="catalog-pg-subscription-rel">pg_subscription_rel</link>
2539 system catalog and also the subscription's replication origin. This allows
2540 logical replication on the new subscriber to continue from where the
2541 old subscriber was up to. Migration of subscription dependencies is only
2542 supported when the old cluster is version 17.0 or later. Subscription
2543 dependencies on clusters before version 17.0 will silently be ignored.
2544 </para>
2545
2546 <para>
2547 There are some prerequisites for <application>pg_upgrade</application> to
2548 be able to upgrade the subscriptions. If these are not met an error
2549 will be reported.
2550 </para>
2551
2552 <itemizedlist>
2553 <listitem>
2554 <para>
2555 All the subscription tables in the old subscriber should be in state
2556 <literal>i</literal> (initialize) or <literal>r</literal> (ready). This
2557 can be verified by checking <link linkend="catalog-pg-subscription-rel">pg_subscription_rel</link>.<structfield>srsubstate</structfield>.
2558 </para>
2559 </listitem>
2560 <listitem>
2561 <para>
2562 The replication origin entry corresponding to each of the subscriptions
2563 should exist in the old cluster. This can be found by checking
2564 <link linkend="catalog-pg-subscription">pg_subscription</link> and
2565 <link linkend="catalog-pg-replication-origin">pg_replication_origin</link>
2566 system tables.
2567 </para>
2568 </listitem>
2569 <listitem>
2570 <para>
2571 The new cluster must have
2572 <link linkend="guc-max-active-replication-origins"><varname>max_active_replication_origins</varname></link>
2573 configured to a value greater than or equal to the number of
2574 subscriptions present in the old cluster.
2575 </para>
2576 </listitem>
2577 </itemizedlist>
2578 </sect2>
2579
2580 <sect2 id="upgrading-logical-replication-clusters">
2581 <title>Upgrading Logical Replication Clusters</title>
2582
2583 <para>
2584 While upgrading a subscriber, write operations can be performed in the
2585 publisher. These changes will be replicated to the subscriber once the
2586 subscriber upgrade is completed.
2587 </para>
2588
2589 <note>
2590 <para>
2591 The logical replication restrictions apply to logical replication cluster
2592 upgrades also. See <xref linkend="logical-replication-restrictions"/> for
2593 details.
2594 </para>
2595 <para>
2596 The prerequisites of publisher upgrade apply to logical replication
2597 cluster upgrades also. See <xref linkend="prepare-publisher-upgrades"/>
2598 for details.
2599 </para>
2600 <para>
2601 The prerequisites of subscriber upgrade apply to logical replication
2602 cluster upgrades also. See <xref linkend="prepare-subscriber-upgrades"/>
2603 for details.
2604 </para>
2605 </note>
2606
2607 <warning>
2608 <para>
2609 Upgrading logical replication cluster requires multiple steps to be
2610 performed on various nodes. Because not all operations are
2611 transactional, the user is advised to take backups as described in
2612 <xref linkend="backup-base-backup"/>.
2613 </para>
2614 </warning>
2615
2616 <para>
2617 The steps to upgrade the following logical replication clusters are
2618 detailed below:
2619 <itemizedlist>
2620 <listitem>
2621 <para>
2622 Follow the steps specified in
2623 <xref linkend="steps-two-node-logical-replication-cluster"/> to upgrade
2624 a two-node logical replication cluster.
2625 </para>
2626 </listitem>
2627 <listitem>
2628 <para>
2629 Follow the steps specified in
2630 <xref linkend="steps-cascaded-logical-replication-cluster"/> to upgrade
2631 a cascaded logical replication cluster.
2632 </para>
2633 </listitem>
2634 <listitem>
2635 <para>
2636 Follow the steps specified in
2637 <xref linkend="steps-two-node-circular-logical-replication-cluster"/>
2638 to upgrade a two-node circular logical replication cluster.
2639 </para>
2640 </listitem>
2641 </itemizedlist>
2642 </para>
2643
2644 <sect3 id="steps-two-node-logical-replication-cluster">
2645 <title>Steps to Upgrade a Two-node Logical Replication Cluster</title>
2646 <para>
2647 Let's say publisher is in <literal>node1</literal> and subscriber is
2648 in <literal>node2</literal>. The subscriber <literal>node2</literal> has
2649 a subscription <literal>sub1_node1_node2</literal> which is subscribing
2650 the changes from <literal>node1</literal>.
2651 </para>
2652
2653 <procedure>
2654 <step id="two-node-cluster-disable-subscriptions-node2">
2655 <para>
2656 Disable all the subscriptions on <literal>node2</literal> that are
2657 subscribing the changes from <literal>node1</literal> by using
2658 <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
2659 e.g.:
2660 <programlisting>
2661 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
2662 </programlisting>
2663 </para>
2664 </step>
2665 <step>
2666 <para>
2667 Stop the publisher server in <literal>node1</literal>, e.g.:
2668 <programlisting>
2669 pg_ctl -D /opt/PostgreSQL/data1 stop
2670 </programlisting>
2671 </para>
2672 </step>
2673
2674 <step>
2675 <para>
2676 Initialize <literal>data1_upgraded</literal> instance by using the
2677 required newer version.
2678 </para>
2679 </step>
2680
2681 <step>
2682 <para>
2683 Upgrade the publisher <literal>node1</literal>'s server to the
2684 required newer version, e.g.:
2685 <programlisting>
2686 pg_upgrade
2687 --old-datadir "/opt/PostgreSQL/postgres/17/data1"
2688 --new-datadir "/opt/PostgreSQL/postgres/18/data1_upgraded"
2689 --old-bindir "/opt/PostgreSQL/postgres/17/bin"
2690 --new-bindir "/opt/PostgreSQL/postgres/18/bin"
2691 </programlisting>
2692 </para>
2693 </step>
2694
2695 <step>
2696 <para>
2697 Start the upgraded publisher server in <literal>node1</literal>, e.g.:
2698 <programlisting>
2699 pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
2700 </programlisting>
2701 </para>
2702 </step>
2703
2704 <step>
2705 <para>
2706 Stop the subscriber server in <literal>node2</literal>, e.g.:
2707 <programlisting>
2708 pg_ctl -D /opt/PostgreSQL/data2 stop
2709 </programlisting>
2710 </para>
2711 </step>
2712
2713 <step>
2714 <para>
2715 Initialize <literal>data2_upgraded</literal> instance by using the
2716 required newer version.
2717 </para>
2718 </step>
2719
2720 <step>
2721 <para>
2722 Upgrade the subscriber <literal>node2</literal>'s server to
2723 the required new version, e.g.:
2724 <programlisting>
2725 pg_upgrade
2726 --old-datadir "/opt/PostgreSQL/postgres/17/data2"
2727 --new-datadir "/opt/PostgreSQL/postgres/18/data2_upgraded"
2728 --old-bindir "/opt/PostgreSQL/postgres/17/bin"
2729 --new-bindir "/opt/PostgreSQL/postgres/18/bin"
2730 </programlisting>
2731 </para>
2732 </step>
2733
2734 <step>
2735 <para>
2736 Start the upgraded subscriber server in <literal>node2</literal>, e.g.:
2737 <programlisting>
2738 pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
2739 </programlisting>
2740 </para>
2741 </step>
2742
2743 <step>
2744 <para>
2745 On <literal>node2</literal>, create any tables that were created in
2746 the upgraded publisher <literal>node1</literal> server between
2747 <xref linkend="two-node-cluster-disable-subscriptions-node2"/>
2748 and now, e.g.:
2749 <programlisting>
2750 /* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
2751 </programlisting>
2752 </para>
2753 </step>
2754
2755 <step>
2756 <para>
2757 Enable all the subscriptions on <literal>node2</literal> that are
2758 subscribing the changes from <literal>node1</literal> by using
2759 <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
2760 e.g.:
2761 <programlisting>
2762 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
2763 </programlisting>
2764 </para>
2765 </step>
2766
2767 <step>
2768 <para>
2769 Refresh the <literal>node2</literal> subscription's publications using
2770 <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
2771 e.g.:
2772 <programlisting>
2773 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
2774 </programlisting>
2775 </para>
2776 </step>
2777 </procedure>
2778
2779 <note>
2780 <para>
2781 In the steps described above, the publisher is upgraded first, followed
2782 by the subscriber. Alternatively, the user can use similar steps to
2783 upgrade the subscriber first, followed by the publisher.
2784 </para>
2785 </note>
2786 </sect3>
2787
2788 <sect3 id="steps-cascaded-logical-replication-cluster">
2789 <title>Steps to Upgrade a Cascaded Logical Replication Cluster</title>
2790 <para>
2791 Let's say we have a cascaded logical replication setup
2792 <literal>node1</literal>-><literal>node2</literal>-><literal>node3</literal>.
2793 Here <literal>node2</literal> is subscribing the changes from
2794 <literal>node1</literal> and <literal>node3</literal> is subscribing
2795 the changes from <literal>node2</literal>. The <literal>node2</literal>
2796 has a subscription <literal>sub1_node1_node2</literal> which is
2797 subscribing the changes from <literal>node1</literal>. The
2798 <literal>node3</literal> has a subscription
2799 <literal>sub1_node2_node3</literal> which is subscribing the changes from
2800 <literal>node2</literal>.
2801 </para>
2802
2803 <procedure>
2804 <step id="cascaded-cluster-disable-sub-node1-node2">
2805 <para>
2806 Disable all the subscriptions on <literal>node2</literal> that are
2807 subscribing the changes from <literal>node1</literal> by using
2808 <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
2809 e.g.:
2810 <programlisting>
2811 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
2812 </programlisting>
2813 </para>
2814 </step>
2815
2816 <step>
2817 <para>
2818 Stop the server in <literal>node1</literal>, e.g.:
2819 <programlisting>
2820 pg_ctl -D /opt/PostgreSQL/data1 stop
2821 </programlisting>
2822 </para>
2823 </step>
2824
2825 <step>
2826 <para>
2827 Initialize <literal>data1_upgraded</literal> instance by using the
2828 required newer version.
2829 </para>
2830 </step>
2831
2832 <step>
2833 <para>
2834 Upgrade the <literal>node1</literal>'s server to the required newer
2835 version, e.g.:
2836 <programlisting>
2837 pg_upgrade
2838 --old-datadir "/opt/PostgreSQL/postgres/17/data1"
2839 --new-datadir "/opt/PostgreSQL/postgres/18/data1_upgraded"
2840 --old-bindir "/opt/PostgreSQL/postgres/17/bin"
2841 --new-bindir "/opt/PostgreSQL/postgres/18/bin"
2842 </programlisting>
2843 </para>
2844 </step>
2845
2846 <step>
2847 <para>
2848 Start the upgraded server in <literal>node1</literal>, e.g.:
2849 <programlisting>
2850 pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
2851 </programlisting>
2852 </para>
2853 </step>
2854
2855 <step id="cascaded-cluster-disable-sub-node2-node3">
2856 <para>
2857 Disable all the subscriptions on <literal>node3</literal> that are
2858 subscribing the changes from <literal>node2</literal> by using
2859 <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
2860 e.g.:
2861 <programlisting>
2862 /* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 DISABLE;
2863 </programlisting>
2864 </para>
2865 </step>
2866
2867 <step>
2868 <para>
2869 Stop the server in <literal>node2</literal>, e.g.:
2870 <programlisting>
2871 pg_ctl -D /opt/PostgreSQL/data2 stop
2872 </programlisting>
2873 </para>
2874 </step>
2875
2876 <step>
2877 <para>
2878 Initialize <literal>data2_upgraded</literal> instance by using the
2879 required newer version.
2880 </para>
2881 </step>
2882
2883 <step>
2884 <para>
2885 Upgrade the <literal>node2</literal>'s server to the required
2886 new version, e.g.:
2887 <programlisting>
2888 pg_upgrade
2889 --old-datadir "/opt/PostgreSQL/postgres/17/data2"
2890 --new-datadir "/opt/PostgreSQL/postgres/18/data2_upgraded"
2891 --old-bindir "/opt/PostgreSQL/postgres/17/bin"
2892 --new-bindir "/opt/PostgreSQL/postgres/18/bin"
2893 </programlisting>
2894 </para>
2895 </step>
2896
2897 <step>
2898 <para>
2899 Start the upgraded server in <literal>node2</literal>, e.g.:
2900 <programlisting>
2901 pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
2902 </programlisting>
2903 </para>
2904 </step>
2905
2906 <step>
2907 <para>
2908 On <literal>node2</literal>, create any tables that were created in
2909 the upgraded publisher <literal>node1</literal> server between
2910 <xref linkend="cascaded-cluster-disable-sub-node1-node2"/>
2911 and now, e.g.:
2912 <programlisting>
2913 /* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
2914 </programlisting>
2915 </para>
2916 </step>
2917
2918 <step>
2919 <para>
2920 Enable all the subscriptions on <literal>node2</literal> that are
2921 subscribing the changes from <literal>node1</literal> by using
2922 <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
2923 e.g.:
2924 <programlisting>
2925 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
2926 </programlisting>
2927 </para>
2928 </step>
2929
2930 <step>
2931 <para>
2932 Refresh the <literal>node2</literal> subscription's publications using
2933 <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
2934 e.g.:
2935 <programlisting>
2936 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
2937 </programlisting>
2938 </para>
2939 </step>
2940
2941 <step>
2942 <para>
2943 Stop the server in <literal>node3</literal>, e.g.:
2944 <programlisting>
2945 pg_ctl -D /opt/PostgreSQL/data3 stop
2946 </programlisting>
2947 </para>
2948 </step>
2949
2950 <step>
2951 <para>
2952 Initialize <literal>data3_upgraded</literal> instance by using the
2953 required newer version.
2954 </para>
2955 </step>
2956
2957 <step>
2958 <para>
2959 Upgrade the <literal>node3</literal>'s server to the required
2960 new version, e.g.:
2961 <programlisting>
2962 pg_upgrade
2963 --old-datadir "/opt/PostgreSQL/postgres/17/data3"
2964 --new-datadir "/opt/PostgreSQL/postgres/18/data3_upgraded"
2965 --old-bindir "/opt/PostgreSQL/postgres/17/bin"
2966 --new-bindir "/opt/PostgreSQL/postgres/18/bin"
2967 </programlisting>
2968 </para>
2969 </step>
2970
2971 <step>
2972 <para>
2973 Start the upgraded server in <literal>node3</literal>, e.g.:
2974 <programlisting>
2975 pg_ctl -D /opt/PostgreSQL/data3_upgraded start -l logfile
2976 </programlisting>
2977 </para>
2978 </step>
2979
2980 <step>
2981 <para>
2982 On <literal>node3</literal>, create any tables that were created in
2983 the upgraded <literal>node2</literal> between
2984 <xref linkend="cascaded-cluster-disable-sub-node2-node3"/> and now,
2985 e.g.:
2986 <programlisting>
2987 /* node3 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
2988 </programlisting>
2989 </para>
2990 </step>
2991
2992 <step>
2993 <para>
2994 Enable all the subscriptions on <literal>node3</literal> that are
2995 subscribing the changes from <literal>node2</literal> by using
2996 <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
2997 e.g.:
2998 <programlisting>
2999 /* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 ENABLE;
3000 </programlisting>
3001 </para>
3002 </step>
3003
3004 <step>
3005 <para>
3006 Refresh the <literal>node3</literal> subscription's publications using
3007 <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
3008 e.g.:
3009 <programlisting>
3010 /* node3 # */ ALTER SUBSCRIPTION sub1_node2_node3 REFRESH PUBLICATION;
3011 </programlisting>
3012 </para>
3013 </step>
3014 </procedure>
3015 </sect3>
3016
3017 <sect3 id="steps-two-node-circular-logical-replication-cluster">
3018 <title>Steps to Upgrade a Two-node Circular Logical Replication Cluster</title>
3019 <para>
3020 Let's say we have a circular logical replication setup
3021 <literal>node1</literal>-><literal>node2</literal> and
3022 <literal>node2</literal>-><literal>node1</literal>. Here
3023 <literal>node2</literal> is subscribing the changes from
3024 <literal>node1</literal> and <literal>node1</literal> is subscribing
3025 the changes from <literal>node2</literal>. The <literal>node1</literal>
3026 has a subscription <literal>sub1_node2_node1</literal> which is
3027 subscribing the changes from <literal>node2</literal>. The
3028 <literal>node2</literal> has a subscription
3029 <literal>sub1_node1_node2</literal> which is subscribing the changes from
3030 <literal>node1</literal>.
3031 </para>
3032
3033 <procedure>
3034 <step id="circular-cluster-disable-sub-node2">
3035 <para>
3036 Disable all the subscriptions on <literal>node2</literal> that are
3037 subscribing the changes from <literal>node1</literal> by using
3038 <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
3039 e.g.:
3040 <programlisting>
3041 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 DISABLE;
3042 </programlisting>
3043 </para>
3044 </step>
3045
3046 <step>
3047 <para>
3048 Stop the server in <literal>node1</literal>, e.g.:
3049 <programlisting>
3050 pg_ctl -D /opt/PostgreSQL/data1 stop
3051 </programlisting>
3052 </para>
3053 </step>
3054
3055 <step>
3056 <para>
3057 Initialize <literal>data1_upgraded</literal> instance by using the
3058 required newer version.
3059 </para>
3060 </step>
3061
3062 <step>
3063 <para>
3064 Upgrade the <literal>node1</literal>'s server to the required
3065 newer version, e.g.:
3066 <programlisting>
3067 pg_upgrade
3068 --old-datadir "/opt/PostgreSQL/postgres/17/data1"
3069 --new-datadir "/opt/PostgreSQL/postgres/18/data1_upgraded"
3070 --old-bindir "/opt/PostgreSQL/postgres/17/bin"
3071 --new-bindir "/opt/PostgreSQL/postgres/18/bin"
3072 </programlisting>
3073 </para>
3074 </step>
3075
3076 <step>
3077 <para>
3078 Start the upgraded server in <literal>node1</literal>, e.g.:
3079 <programlisting>
3080 pg_ctl -D /opt/PostgreSQL/data1_upgraded start -l logfile
3081 </programlisting>
3082 </para>
3083 </step>
3084
3085 <step>
3086 <para>
3087 Enable all the subscriptions on <literal>node2</literal> that are
3088 subscribing the changes from <literal>node1</literal> by using
3089 <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
3090 e.g.:
3091 <programlisting>
3092 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 ENABLE;
3093 </programlisting>
3094 </para>
3095 </step>
3096
3097 <step>
3098 <para>
3099 On <literal>node1</literal>, create any tables that were created in
3100 <literal>node2</literal> between <xref linkend="circular-cluster-disable-sub-node2"/>
3101 and now, e.g.:
3102 <programlisting>
3103 /* node1 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
3104 </programlisting>
3105 </para>
3106 </step>
3107
3108
3109 <step>
3110 <para>
3111 Refresh the <literal>node1</literal> subscription's publications to
3112 copy initial table data from <literal>node2</literal> using
3113 <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
3114 e.g.:
3115 <programlisting>
3116 /* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 REFRESH PUBLICATION;
3117 </programlisting>
3118 </para>
3119 </step>
3120
3121 <step id="circular-cluster-disable-sub-node1">
3122 <para>
3123 Disable all the subscriptions on <literal>node1</literal> that are
3124 subscribing the changes from <literal>node2</literal> by using
3125 <link linkend="sql-altersubscription-params-disable"><command>ALTER SUBSCRIPTION ... DISABLE</command></link>,
3126 e.g.:
3127 <programlisting>
3128 /* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 DISABLE;
3129 </programlisting>
3130 </para>
3131 </step>
3132
3133 <step>
3134 <para>
3135 Stop the server in <literal>node2</literal>, e.g.:
3136 <programlisting>
3137 pg_ctl -D /opt/PostgreSQL/data2 stop
3138 </programlisting>
3139 </para>
3140 </step>
3141
3142 <step>
3143 <para>
3144 Initialize <literal>data2_upgraded</literal> instance by using the
3145 required newer version.
3146 </para>
3147 </step>
3148
3149 <step>
3150 <para>
3151 Upgrade the <literal>node2</literal>'s server to the required
3152 new version, e.g.:
3153 <programlisting>
3154 pg_upgrade
3155 --old-datadir "/opt/PostgreSQL/postgres/17/data2"
3156 --new-datadir "/opt/PostgreSQL/postgres/18/data2_upgraded"
3157 --old-bindir "/opt/PostgreSQL/postgres/17/bin"
3158 --new-bindir "/opt/PostgreSQL/postgres/18/bin"
3159 </programlisting>
3160 </para>
3161 </step>
3162
3163 <step>
3164 <para>
3165 Start the upgraded server in <literal>node2</literal>, e.g.:
3166 <programlisting>
3167 pg_ctl -D /opt/PostgreSQL/data2_upgraded start -l logfile
3168 </programlisting>
3169 </para>
3170 </step>
3171
3172 <step>
3173 <para>
3174 Enable all the subscriptions on <literal>node1</literal> that are
3175 subscribing the changes from <literal>node2</literal> by using
3176 <link linkend="sql-altersubscription-params-enable"><command>ALTER SUBSCRIPTION ... ENABLE</command></link>,
3177 e.g.:
3178 <programlisting>
3179 /* node1 # */ ALTER SUBSCRIPTION sub1_node2_node1 ENABLE;
3180 </programlisting>
3181 </para>
3182 </step>
3183
3184 <step>
3185 <para>
3186 On <literal>node2</literal>, create any tables that were created in
3187 the upgraded <literal>node1</literal> between <xref linkend="circular-cluster-disable-sub-node1"/>
3188 and now, e.g.:
3189 <programlisting>
3190 /* node2 # */ CREATE TABLE distributors (did integer PRIMARY KEY, name varchar(40));
3191 </programlisting>
3192 </para>
3193 </step>
3194
3195 <step>
3196 <para>
3197 Refresh the <literal>node2</literal> subscription's publications to
3198 copy initial table data from <literal>node1</literal> using
3199 <link linkend="sql-altersubscription-params-refresh-publication"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>,
3200 e.g.:
3201 <programlisting>
3202 /* node2 # */ ALTER SUBSCRIPTION sub1_node1_node2 REFRESH PUBLICATION;
3203 </programlisting>
3204 </para>
3205 </step>
3206 </procedure>
3207 </sect3>
3208
3209 </sect2>
3210 </sect1>
3211
3212 <sect1 id="logical-replication-quick-setup">
3213 <title>Quick Setup</title>
3214
3215 <para>
3216 First set the configuration options in <filename>postgresql.conf</filename>:
3217 <programlisting>
3218 wal_level = logical
3219 </programlisting>
3220 The other required settings have default values that are sufficient for a
3221 basic setup.
3222 </para>
3223
3224 <para>
3225 <filename>pg_hba.conf</filename> needs to be adjusted to allow replication
3226 (the values here depend on your actual network configuration and user you
3227 want to use for connecting):
3228 <programlisting>
3229 host all repuser 0.0.0.0/0 scram-sha-256
3230 </programlisting>
3231 </para>
3232
3233 <para>
3234 Then on the publisher database:
3235 <programlisting>
3236 CREATE PUBLICATION mypub FOR TABLE users, departments;
3237 </programlisting>
3238 </para>
3239
3240 <para>
3241 And on the subscriber database:
3242 <programlisting>
3243 CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION mypub;
3244 </programlisting>
3245 </para>
3246
3247 <para>
3248 The above will start the replication process, which synchronizes the
3249 initial table contents of the tables <literal>users</literal> and
3250 <literal>departments</literal> and then starts replicating
3251 incremental changes to those tables.
3252 </para>
3253 </sect1>
3254 </chapter>