summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/logical-replication.sgml
blob: bc3f5ec78d0bb8b65e74ca6cc8bb39b2e46c5bb5 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
<!-- doc/src/sgml/logical-replication.sgml -->

<chapter id="logical-replication">
 <title>Logical Replication</title>

 <para>
  Logical replication is a method of replicating data objects and their
  changes, based upon their replication identity (usually a primary key).  We
  use the term logical in contrast to physical replication, which uses exact
  block addresses and byte-by-byte replication.  PostgreSQL supports both
  mechanisms concurrently, see <xref linkend="high-availability"/>.  Logical
  replication allows fine-grained control over both data replication and
  security.
 </para>

 <para>
  Logical replication uses a <firstterm>publish</firstterm>
  and <firstterm>subscribe</firstterm> model with one or
  more <firstterm>subscribers</firstterm> subscribing to one or more
  <firstterm>publications</firstterm> on a <firstterm>publisher</firstterm>
  node.  Subscribers pull data from the publications they subscribe to and may
  subsequently re-publish data to allow cascading replication or more complex
  configurations.
 </para>

 <para>
  Logical replication of a table typically starts with taking a snapshot
  of the data on the publisher database and copying that to the subscriber.
  Once that is done, the changes on the publisher are sent to the subscriber
  as they occur in real-time.  The subscriber applies the data in the same
  order as the publisher so that transactional consistency is guaranteed for
  publications within a single subscription.  This method of data replication
  is sometimes referred to as transactional replication.
 </para>

 <para>
  The typical use-cases for logical replication are:

  <itemizedlist>
   <listitem>
    <para>
     Sending incremental changes in a single database or a subset of a
     database to subscribers as they occur.
    </para>
   </listitem>

   <listitem>
    <para>
     Firing triggers for individual changes as they arrive on the
     subscriber.
    </para>
   </listitem>

   <listitem>
    <para>
     Consolidating multiple databases into a single one (for example for
     analytical purposes).
    </para>
   </listitem>

   <listitem>
    <para>
     Replicating between different major versions of PostgreSQL.
    </para>
   </listitem>

   <listitem>
    <para>
     Replicating between PostgreSQL instances on different platforms (for
     example Linux to Windows)
    </para>
   </listitem>

   <listitem>
    <para>
     Giving access to replicated data to different groups of users.
    </para>
   </listitem>

   <listitem>
    <para>
     Sharing a subset of the database between multiple databases.
    </para>
   </listitem>
  </itemizedlist>
 </para>

 <para>
  The subscriber database behaves in the same way as any other PostgreSQL
  instance and can be used as a publisher for other databases by defining its
  own publications.  When the subscriber is treated as read-only by
  application, there will be no conflicts from a single subscription.  On the
  other hand, if there are other writes done either by an application or by other
  subscribers to the same set of tables, conflicts can arise.
 </para>

 <sect1 id="logical-replication-publication">
  <title>Publication</title>

  <para>
   A <firstterm>publication</firstterm> can be defined on any physical
   replication primary.  The node where a publication is defined is referred to
   as <firstterm>publisher</firstterm>.  A publication is a set of changes
   generated from a table or a group of tables, and might also be described as
   a change set or replication set.  Each publication exists in only one database.
  </para>

  <para>
   Publications are different from schemas and do not affect how the table is
   accessed.  Each table can be added to multiple publications if needed.
   Publications may currently only contain tables and all tables in schema.
   Objects must be added explicitly, except when a publication is created for
   <literal>ALL TABLES</literal>.
  </para>

  <para>
   Publications can choose to limit the changes they produce to
   any combination of <command>INSERT</command>, <command>UPDATE</command>,
   <command>DELETE</command>, and <command>TRUNCATE</command>, similar to how triggers are fired by
   particular event types. By default, all operation types are replicated.
   These publication specifications apply only for DML operations; they do not affect the initial
   data synchronization copy. (Row filters have no effect for
   <command>TRUNCATE</command>. See <xref linkend="logical-replication-row-filter"/>).
  </para>

  <para>
   A published table must have a <quote>replica identity</quote> configured in
   order to be able to replicate <command>UPDATE</command>
   and <command>DELETE</command> operations, so that appropriate rows to
   update or delete can be identified on the subscriber side.  By default,
   this is the primary key, if there is one.  Another unique index (with
   certain additional requirements) can also be set to be the replica
   identity.  If the table does not have any suitable key, then it can be set
   to replica identity <quote>full</quote>, which means the entire row becomes
   the key.  This, however, is very inefficient and should only be used as a
   fallback if no other solution is possible.  If a replica identity other
   than <quote>full</quote> is set on the publisher side, a replica identity
   comprising the same or fewer columns must also be set on the subscriber
   side.  See <xref linkend="sql-altertable-replica-identity"/> for details on
   how to set the replica identity.  If a table without a replica identity is
   added to a publication that replicates <command>UPDATE</command>
   or <command>DELETE</command> operations then
   subsequent <command>UPDATE</command> or <command>DELETE</command>
   operations will cause an error on the publisher.  <command>INSERT</command>
   operations can proceed regardless of any replica identity.
  </para>

  <para>
   Every publication can have multiple subscribers.
  </para>

  <para>
   A publication is created using the <link linkend="sql-createpublication"><command>CREATE PUBLICATION</command></link>
   command and may later be altered or dropped using corresponding commands.
  </para>

  <para>
   The individual tables can be added and removed dynamically using
   <link linkend="sql-alterpublication"><command>ALTER PUBLICATION</command></link>.  Both the <literal>ADD
   TABLE</literal> and <literal>DROP TABLE</literal> operations are
   transactional; so the table will start or stop replicating at the correct
   snapshot once the transaction has committed.
  </para>
 </sect1>

 <sect1 id="logical-replication-subscription">
  <title>Subscription</title>

  <para>
   A <firstterm>subscription</firstterm> is the downstream side of logical
   replication.  The node where a subscription is defined is referred to as
   the <firstterm>subscriber</firstterm>.  A subscription defines the connection
   to another database and set of publications (one or more) to which it wants
   to subscribe.
  </para>

  <para>
   The subscriber database behaves in the same way as any other PostgreSQL
   instance and can be used as a publisher for other databases by defining its
   own publications.
  </para>

  <para>
   A subscriber node may have multiple subscriptions if desired.  It is
   possible to define multiple subscriptions between a single
   publisher-subscriber pair, in which case care must be taken to ensure
   that the subscribed publication objects don't overlap.
  </para>

  <para>
   Each subscription will receive changes via one replication slot (see
   <xref linkend="streaming-replication-slots"/>).  Additional replication
   slots may be required for the initial data synchronization of
   pre-existing table data and those will be dropped at the end of data
   synchronization.
  </para>

  <para>
   A logical replication subscription can be a standby for synchronous
   replication (see <xref linkend="synchronous-replication"/>).  The standby
   name is by default the subscription name.  An alternative name can be
   specified as <literal>application_name</literal> in the connection
   information of the subscription.
  </para>

  <para>
   Subscriptions are dumped by <command>pg_dump</command> if the current user
   is a superuser.  Otherwise a warning is written and subscriptions are
   skipped, because non-superusers cannot read all subscription information
   from the <structname>pg_subscription</structname> catalog.
  </para>

  <para>
   The subscription is added using <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link> and
   can be stopped/resumed at any time using the
   <link linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION</command></link> command and removed using
   <link linkend="sql-dropsubscription"><command>DROP SUBSCRIPTION</command></link>.
  </para>

  <para>
   When a subscription is dropped and recreated, the synchronization
   information is lost.  This means that the data has to be resynchronized
   afterwards.
  </para>

  <para>
   The schema definitions are not replicated, and the published tables must
   exist on the subscriber.  Only regular tables may be
   the target of replication.  For example, you can't replicate to a view.
  </para>

  <para>
   The tables are matched between the publisher and the subscriber using the
   fully qualified table name.  Replication to differently-named tables on the
   subscriber is not supported.
  </para>

  <para>
   Columns of a table are also matched by name.  The order of columns in the
   subscriber table does not need to match that of the publisher.  The data
   types of the columns do not need to match, as long as the text
   representation of the data can be converted to the target type.  For
   example, you can replicate from a column of type <type>integer</type> to a
   column of type <type>bigint</type>.  The target table can also have
   additional columns not provided by the published table.  Any such columns
   will be filled with the default value as specified in the definition of the
   target table.
  </para>

  <sect2 id="logical-replication-subscription-slot">
   <title>Replication Slot Management</title>

   <para>
    As mentioned earlier, each (active) subscription receives changes from a
    replication slot on the remote (publishing) side.
   </para>
   <para>
    Additional table synchronization slots are normally transient, created
    internally to perform initial table synchronization and dropped
    automatically when they are no longer needed. These table synchronization
    slots have generated names: <quote><literal>pg_%u_sync_%u_%llu</literal></quote>
    (parameters: Subscription <parameter>oid</parameter>,
    Table <parameter>relid</parameter>, system identifier <parameter>sysid</parameter>)
   </para>
   <para>
    Normally, the remote replication slot is created automatically when the
    subscription is created using <command>CREATE SUBSCRIPTION</command> and it
    is dropped automatically when the subscription is dropped using
    <command>DROP SUBSCRIPTION</command>.  In some situations, however, it can
    be useful or necessary to manipulate the subscription and the underlying
    replication slot separately.  Here are some scenarios:

    <itemizedlist>
     <listitem>
      <para>
       When creating a subscription, the replication slot already exists.  In
       that case, the subscription can be created using
       the <literal>create_slot = false</literal> option to associate with the
       existing slot.
      </para>
     </listitem>

     <listitem>
      <para>
       When creating a subscription, the remote host is not reachable or in an
       unclear state.  In that case, the subscription can be created using
       the <literal>connect = false</literal> option.  The remote host will then not
       be contacted at all.  This is what <application>pg_dump</application>
       uses.  The remote replication slot will then have to be created
       manually before the subscription can be activated.
      </para>
     </listitem>

     <listitem>
      <para>
       When dropping a subscription, the replication slot should be kept.
       This could be useful when the subscriber database is being moved to a
       different host and will be activated from there.  In that case,
       disassociate the slot from the subscription using <command>ALTER
       SUBSCRIPTION</command> before attempting to drop the subscription.
      </para>
     </listitem>

     <listitem>
      <para>
       When dropping a subscription, the remote host is not reachable.  In
       that case, disassociate the slot from the subscription
       using <command>ALTER SUBSCRIPTION</command> before attempting to drop
       the subscription.  If the remote database instance no longer exists, no
       further action is then necessary.  If, however, the remote database
       instance is just unreachable, the replication slot (and any still
       remaining table synchronization slots) should then be
       dropped manually; otherwise it/they would continue to reserve WAL and might
       eventually cause the disk to fill up.  Such cases should be carefully
       investigated.
      </para>
     </listitem>
    </itemizedlist>
   </para>
  </sect2>

  <sect2 id="logical-replication-subscription-examples">
    <title>Examples</title>

    <para>
     Create some test tables on the publisher.
<programlisting>
test_pub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
CREATE TABLE
test_pub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
CREATE TABLE
test_pub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
CREATE TABLE
</programlisting></para>

    <para>
     Create the same tables on the subscriber.
<programlisting>
test_sub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
CREATE TABLE
test_sub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
CREATE TABLE
test_sub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
CREATE TABLE
</programlisting></para>

    <para>
     Insert data to the tables at the publisher side.
<programlisting>
test_pub=# INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
INSERT 0 3
test_pub=# INSERT INTO t2 VALUES (1, 'A'), (2, 'B'), (3, 'C');
INSERT 0 3
test_pub=# INSERT INTO t3 VALUES (1, 'i'), (2, 'ii'), (3, 'iii');
INSERT 0 3
</programlisting></para>

    <para>
     Create publications for the tables. The publications <literal>pub2</literal>
     and <literal>pub3a</literal> disallow some <literal>publish</literal>
     operations. The publication <literal>pub3b</literal> has a row filter (see
     <xref linkend="logical-replication-row-filter"/>).
<programlisting>
test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1;
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION pub2 FOR TABLE t2 WITH (publish = 'truncate');
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION pub3a FOR TABLE t3 WITH (publish = 'truncate');
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION pub3b FOR TABLE t3 WHERE (e > 5);
CREATE PUBLICATION
</programlisting></para>

    <para>
     Create subscriptions for the publications. The subscription
     <literal>sub3</literal> subscribes to both <literal>pub3a</literal> and
     <literal>pub3b</literal>. All subscriptions will copy initial data by default.
<programlisting>
test_sub=# CREATE SUBSCRIPTION sub1
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub1'
test_sub-# PUBLICATION pub1;
CREATE SUBSCRIPTION
test_sub=# CREATE SUBSCRIPTION sub2
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub2'
test_sub-# PUBLICATION pub2;
CREATE SUBSCRIPTION
test_sub=# CREATE SUBSCRIPTION sub3
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub3'
test_sub-# PUBLICATION pub3a, pub3b;
CREATE SUBSCRIPTION
</programlisting></para>

    <para>
     Observe that initial table data is copied, regardless of the
     <literal>publish</literal> operation of the publication.
<programlisting>
test_sub=# SELECT * FROM t1;
 a |   b
---+-------
 1 | one
 2 | two
 3 | three
(3 rows)

test_sub=# SELECT * FROM t2;
 c | d
---+---
 1 | A
 2 | B
 3 | C
(3 rows)
</programlisting></para>

    <para>
     Furthermore, because the initial data copy ignores the <literal>publish</literal>
     operation, and because publication <literal>pub3a</literal> has no row filter,
     it means the copied table <literal>t3</literal> contains all rows even when
     they do not match the row filter of publication <literal>pub3b</literal>.
<programlisting>
test_sub=# SELECT * FROM t3;
 e |  f
---+-----
 1 | i
 2 | ii
 3 | iii
(3 rows)
</programlisting></para>

   <para>
    Insert more data to the tables at the publisher side.
<programlisting>
test_pub=# INSERT INTO t1 VALUES (4, 'four'), (5, 'five'), (6, 'six');
INSERT 0 3
test_pub=# INSERT INTO t2 VALUES (4, 'D'), (5, 'E'), (6, 'F');
INSERT 0 3
test_pub=# INSERT INTO t3 VALUES (4, 'iv'), (5, 'v'), (6, 'vi');
INSERT 0 3
</programlisting></para>

   <para>
    Now the publisher side data looks like:
<programlisting>
test_pub=# SELECT * FROM t1;
 a |   b
---+-------
 1 | one
 2 | two
 3 | three
 4 | four
 5 | five
 6 | six
(6 rows)

test_pub=# SELECT * FROM t2;
 c | d
---+---
 1 | A
 2 | B
 3 | C
 4 | D
 5 | E
 6 | F
(6 rows)

test_pub=# SELECT * FROM t3;
 e |  f
---+-----
 1 | i
 2 | ii
 3 | iii
 4 | iv
 5 | v
 6 | vi
(6 rows)
</programlisting></para>

   <para>
    Observe that during normal replication the appropriate
    <literal>publish</literal> operations are used. This means publications
    <literal>pub2</literal> and <literal>pub3a</literal> will not replicate the
    <literal>INSERT</literal>. Also, publication <literal>pub3b</literal> will
    only replicate data that matches the row filter of <literal>pub3b</literal>.
    Now the subscriber side data looks like:
<programlisting>
test_sub=# SELECT * FROM t1;
 a |   b
---+-------
 1 | one
 2 | two
 3 | three
 4 | four
 5 | five
 6 | six
(6 rows)

test_sub=# SELECT * FROM t2;
 c | d
---+---
 1 | A
 2 | B
 3 | C
(3 rows)

test_sub=# SELECT * FROM t3;
 e |  f
---+-----
 1 | i
 2 | ii
 3 | iii
 6 | vi
(4 rows)
</programlisting></para>
  </sect2>

 </sect1>

 <sect1 id="logical-replication-row-filter">
  <title>Row Filters</title>

  <para>
   By default, all data from all published tables will be replicated to the
   appropriate subscribers. The replicated data can be reduced by using a
   <firstterm>row filter</firstterm>. A user might choose to use row filters
   for behavioral, security or performance reasons. If a published table sets a
   row filter, a row is replicated only if its data satisfies the row filter
   expression. This allows a set of tables to be partially replicated. The row
   filter is defined per table. Use a <literal>WHERE</literal> clause after the
   table name for each published table that requires data to be filtered out.
   The <literal>WHERE</literal> clause must be enclosed by parentheses. See
   <xref linkend="sql-createpublication"/> for details.
  </para>

  <sect2 id="logical-replication-row-filter-rules">
   <title>Row Filter Rules</title>

   <para>
    Row filters are applied <emphasis>before</emphasis> publishing the changes.
    If the row filter evaluates to <literal>false</literal> or <literal>NULL</literal>
    then the row is not replicated. The <literal>WHERE</literal> clause expression
    is evaluated with the same role used for the replication connection (i.e.
    the role specified in the <literal>CONNECTION</literal> clause of the
    <xref linkend="sql-createsubscription"/>). Row filters have no effect for
    <command>TRUNCATE</command> command.
   </para>

  </sect2>

  <sect2 id="logical-replication-row-filter-restrictions">
   <title>Expression Restrictions</title>

   <para>
    The <literal>WHERE</literal> clause allows only simple expressions. It
    cannot contain user-defined functions, operators, types, and collations,
    system column references or non-immutable built-in functions.
   </para>

   <para>
    If a publication publishes <command>UPDATE</command> or
    <command>DELETE</command> operations, the row filter <literal>WHERE</literal>
    clause must contain only columns that are covered by the replica identity
    (see <xref linkend="sql-altertable-replica-identity"/>). If a publication
    publishes only <command>INSERT</command> operations, the row filter
    <literal>WHERE</literal> clause can use any column.
   </para>

  </sect2>

  <sect2 id="logical-replication-row-filter-transformations">
   <title>UPDATE Transformations</title>

   <para>
    Whenever an <command>UPDATE</command> is processed, the row filter
    expression is evaluated for both the old and new row (i.e. using the data
    before and after the update). If both evaluations are <literal>true</literal>,
    it replicates the <command>UPDATE</command> change. If both evaluations are
    <literal>false</literal>, it doesn't replicate the change. If only one of
    the old/new rows matches the row filter expression, the <command>UPDATE</command>
    is transformed to <command>INSERT</command> or <command>DELETE</command>, to
    avoid any data inconsistency. The row on the subscriber should reflect what
    is defined by the row filter expression on the publisher.
   </para>

   <para>
    If the old row satisfies the row filter expression (it was sent to the
    subscriber) but the new row doesn't, then, from a data consistency
    perspective the old row should be removed from the subscriber.
    So the <command>UPDATE</command> is transformed into a <command>DELETE</command>.
   </para>

   <para>
    If the old row doesn't satisfy the row filter expression (it wasn't sent
    to the subscriber) but the new row does, then, from a data consistency
    perspective the new row should be added to the subscriber.
    So the <command>UPDATE</command> is transformed into an <command>INSERT</command>.
   </para>

   <para>
    <xref linkend="logical-replication-row-filter-transformations-summary"/>
    summarizes the applied transformations.
   </para>

   <table id="logical-replication-row-filter-transformations-summary">
    <title><command>UPDATE</command> Transformation Summary</title>
    <tgroup cols="3">
    <thead>
     <row>
      <entry>Old row</entry><entry>New row</entry><entry>Transformation</entry>
     </row>
    </thead>
    <tbody>
     <row>
      <entry>no match</entry><entry>no match</entry><entry>don't replicate</entry>
     </row>
     <row>
      <entry>no match</entry><entry>match</entry><entry><literal>INSERT</literal></entry>
     </row>
     <row>
      <entry>match</entry><entry>no match</entry><entry><literal>DELETE</literal></entry>
     </row>
     <row>
      <entry>match</entry><entry>match</entry><entry><literal>UPDATE</literal></entry>
     </row>
    </tbody>
   </tgroup>
   </table>

  </sect2>

  <sect2 id="logical-replication-row-filter-partitioned-table">
   <title>Partitioned Tables</title>

   <para>
    If the publication contains a partitioned table, the publication parameter
    <literal>publish_via_partition_root</literal> determines which row filter
    is used. If <literal>publish_via_partition_root</literal> is <literal>true</literal>,
    the <emphasis>root partitioned table's</emphasis> row filter is used. Otherwise,
    if <literal>publish_via_partition_root</literal> is <literal>false</literal>
    (default), each <emphasis>partition's</emphasis> row filter is used.
   </para>

  </sect2>

  <sect2 id="logical-replication-row-filter-initial-data-sync">
   <title>Initial Data Synchronization</title>

   <para>
    If the subscription requires copying pre-existing table data
    and a publication contains <literal>WHERE</literal> clauses, only data that
    satisfies the row filter expressions is copied to the subscriber.
   </para>

   <para>
    If the subscription has several publications in which a table has been
    published with different <literal>WHERE</literal> clauses, rows that satisfy
    <emphasis>any</emphasis> of the expressions will be copied. See
    <xref linkend="logical-replication-row-filter-combining"/> for details.
   </para>

   <warning>
    <para>
     Because initial data synchronization does not take into account the
     <literal>publish</literal> parameter when copying existing table data,
     some rows may be copied that would not be replicated using DML. Refer to
     <xref linkend="logical-replication-snapshot"/>, and see
     <xref linkend="logical-replication-subscription-examples"/> for examples.
    </para>
   </warning>

   <note>
    <para>
     If the subscriber is in a release prior to 15, copy pre-existing data
     doesn't use row filters even if they are defined in the publication.
     This is because old releases can only copy the entire table data.
    </para>
   </note>

  </sect2>

  <sect2 id="logical-replication-row-filter-combining">
   <title>Combining Multiple Row Filters</title>

   <para>
    If the subscription has several publications in which the same table has
    been published with different row filters (for the same <literal>publish</literal>
    operation), those expressions get ORed together, so that rows satisfying
    <emphasis>any</emphasis> of the expressions will be replicated. This means all
    the other row filters for the same table become redundant if:
    <itemizedlist>
     <listitem>
      <para>
       One of the publications has no row filter.
      </para>
     </listitem>
     <listitem>
      <para>
       One of the publications was created using <literal>FOR ALL TABLES</literal>.
       This clause does not allow row filters.
      </para>
     </listitem>
     <listitem>
      <para>
       One of the publications was created using
       <literal>FOR TABLES IN SCHEMA</literal> and the table belongs to
       the referred schema. This clause does not allow row filters.
      </para>
     </listitem>
    </itemizedlist></para>

  </sect2>

  <sect2 id="logical-replication-row-filter-examples">
   <title>Examples</title>

   <para>
    Create some tables to be used in the following examples.
<programlisting>
test_pub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
CREATE TABLE
test_pub=# CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d));
CREATE TABLE
test_pub=# CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g));
CREATE TABLE
</programlisting></para>

   <para>
    Create some publications. Publication <literal>p1</literal> has one table
    (<literal>t1</literal>) and that table has a row filter. Publication
    <literal>p2</literal> has two tables. Table <literal>t1</literal> has no row
    filter, and table <literal>t2</literal> has a row filter. Publication
    <literal>p3</literal> has two tables, and both of them have a row filter.
<programlisting>
test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
CREATE PUBLICATION
</programlisting></para>

   <para>
    <command>psql</command> can be used to show the row filter expressions (if
    defined) for each publication.
<programlisting>
test_pub=# \dRp+
                               Publication p1
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text))

                               Publication p2
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.t1"
    "public.t2" WHERE (e = 99)

                               Publication p3
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.t2" WHERE (d = 10)
    "public.t3" WHERE (g = 10)
</programlisting></para>

   <para>
    <command>psql</command> can be used to show the row filter expressions (if
    defined) for each table. See that table <literal>t1</literal> is a member
    of two publications, but has a row filter only in <literal>p1</literal>.
    See that table <literal>t2</literal> is a member of two publications, and
    has a different row filter in each of them.
<programlisting>
test_pub=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
 b      | integer |           |          |
 c      | text    |           | not null |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (a, c)
Publications:
    "p1" WHERE ((a > 5) AND (c = 'NSW'::text))
    "p2"

test_pub=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 d      | integer |           | not null |
 e      | integer |           |          |
 f      | integer |           |          |
Indexes:
    "t2_pkey" PRIMARY KEY, btree (d)
Publications:
    "p2" WHERE (e = 99)
    "p3" WHERE (d = 10)

test_pub=# \d t3
                 Table "public.t3"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 g      | integer |           | not null |
 h      | integer |           |          |
 i      | integer |           |          |
Indexes:
    "t3_pkey" PRIMARY KEY, btree (g)
Publications:
    "p3" WHERE (g = 10)
</programlisting></para>

   <para>
    On the subscriber node, create a table <literal>t1</literal> with the same
    definition as the one on the publisher, and also create the subscription
    <literal>s1</literal> that subscribes to the publication <literal>p1</literal>.
<programlisting>
test_sub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
CREATE TABLE
test_sub=# CREATE SUBSCRIPTION s1
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1'
test_sub-# PUBLICATION p1;
CREATE SUBSCRIPTION
</programlisting></para>

   <para>
    Insert some rows. Only the rows satisfying the <literal>t1 WHERE</literal>
    clause of publication <literal>p1</literal> are replicated.
<programlisting>
test_pub=# INSERT INTO t1 VALUES (2, 102, 'NSW');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (3, 103, 'QLD');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (4, 104, 'VIC');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (5, 105, 'ACT');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (6, 106, 'NSW');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (7, 107, 'NT');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (8, 108, 'QLD');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (9, 109, 'NSW');
INSERT 0 1

test_pub=# SELECT * FROM t1;
 a |  b  |  c
---+-----+-----
 2 | 102 | NSW
 3 | 103 | QLD
 4 | 104 | VIC
 5 | 105 | ACT
 6 | 106 | NSW
 7 | 107 | NT
 8 | 108 | QLD
 9 | 109 | NSW
(8 rows)
</programlisting>
<programlisting>
test_sub=# SELECT * FROM t1;
 a |  b  |  c
---+-----+-----
 6 | 106 | NSW
 9 | 109 | NSW
(2 rows)
</programlisting></para>

   <para>
    Update some data, where the old and new row values both
    satisfy the <literal>t1 WHERE</literal> clause of publication
    <literal>p1</literal>. The <command>UPDATE</command> replicates
    the change as normal.
<programlisting>
test_pub=# UPDATE t1 SET b = 999 WHERE a = 6;
UPDATE 1

test_pub=# SELECT * FROM t1;
 a |  b  |  c
---+-----+-----
 2 | 102 | NSW
 3 | 103 | QLD
 4 | 104 | VIC
 5 | 105 | ACT
 7 | 107 | NT
 8 | 108 | QLD
 9 | 109 | NSW
 6 | 999 | NSW
(8 rows)
</programlisting>
<programlisting>
test_sub=# SELECT * FROM t1;
 a |  b  |  c
---+-----+-----
 9 | 109 | NSW
 6 | 999 | NSW
(2 rows)
</programlisting></para>

   <para>
    Update some data, where the old row values did not satisfy
    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
    but the new row values do satisfy it. The <command>UPDATE</command> is
    transformed into an <command>INSERT</command> and the change is replicated.
    See the new row on the subscriber.
<programlisting>
test_pub=# UPDATE t1 SET a = 555 WHERE a = 2;
UPDATE 1

test_pub=# SELECT * FROM t1;
  a  |  b  |  c
-----+-----+-----
   3 | 103 | QLD
   4 | 104 | VIC
   5 | 105 | ACT
   7 | 107 | NT
   8 | 108 | QLD
   9 | 109 | NSW
   6 | 999 | NSW
 555 | 102 | NSW
(8 rows)
</programlisting>
<programlisting>
test_sub=# SELECT * FROM t1;
  a  |  b  |  c
-----+-----+-----
   9 | 109 | NSW
   6 | 999 | NSW
 555 | 102 | NSW
(3 rows)
</programlisting></para>

   <para>
    Update some data, where the old row values satisfied
    the <literal>t1 WHERE</literal> clause of publication <literal>p1</literal>,
    but the new row values do not satisfy it. The <command>UPDATE</command> is
    transformed into a <command>DELETE</command> and the change is replicated.
    See that the row is removed from the subscriber.
<programlisting>
test_pub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;
UPDATE 1

test_pub=# SELECT * FROM t1;
  a  |  b  |  c
-----+-----+-----
   3 | 103 | QLD
   4 | 104 | VIC
   5 | 105 | ACT
   7 | 107 | NT
   8 | 108 | QLD
   6 | 999 | NSW
 555 | 102 | NSW
   9 | 109 | VIC
(8 rows)
</programlisting>
<programlisting>
test_sub=# SELECT * FROM t1;
  a  |  b  |  c
-----+-----+-----
   6 | 999 | NSW
 555 | 102 | NSW
(2 rows)
</programlisting></para>

   <para>
    The following examples show how the publication parameter
    <literal>publish_via_partition_root</literal> determines whether the row
    filter of the parent or child table will be used in the case of partitioned
    tables.
   </para>

   <para>
    Create a partitioned table on the publisher.
<programlisting>
test_pub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
CREATE TABLE
test_pub=# CREATE TABLE child PARTITION OF parent DEFAULT;
CREATE TABLE
</programlisting>
   Create the same tables on the subscriber.
<programlisting>
test_sub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
CREATE TABLE
test_sub=# CREATE TABLE child PARTITION OF parent DEFAULT;
CREATE TABLE
</programlisting></para>

   <para>
    Create a publication <literal>p4</literal>, and then subscribe to it. The
    publication parameter <literal>publish_via_partition_root</literal> is set
    as true. There are row filters defined on both the partitioned table
    (<literal>parent</literal>), and on the partition (<literal>child</literal>).
<programlisting>
test_pub=# CREATE PUBLICATION p4 FOR TABLE parent WHERE (a &lt; 5), child WHERE (a >= 5)
test_pub-# WITH (publish_via_partition_root=true);
CREATE PUBLICATION
</programlisting>
<programlisting>
test_sub=# CREATE SUBSCRIPTION s4
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s4'
test_sub-# PUBLICATION p4;
CREATE SUBSCRIPTION
</programlisting></para>

   <para>
    Insert some values directly into the <literal>parent</literal> and
    <literal>child</literal> tables. They replicate using the row filter of
    <literal>parent</literal> (because <literal>publish_via_partition_root</literal>
    is true).
<programlisting>
test_pub=# INSERT INTO parent VALUES (2), (4), (6);
INSERT 0 3
test_pub=# INSERT INTO child VALUES (3), (5), (7);
INSERT 0 3

test_pub=# SELECT * FROM parent ORDER BY a;
 a
---
 2
 3
 4
 5
 6
 7
(6 rows)
</programlisting>
<programlisting>
test_sub=# SELECT * FROM parent ORDER BY a;
 a
---
 2
 3
 4
(3 rows)
</programlisting></para>

   <para>
    Repeat the same test, but with a different value for <literal>publish_via_partition_root</literal>.
    The publication parameter <literal>publish_via_partition_root</literal> is
    set as false. A row filter is defined on the partition (<literal>child</literal>).
<programlisting>
test_pub=# DROP PUBLICATION p4;
DROP PUBLICATION
test_pub=# CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5)
test_pub-# WITH (publish_via_partition_root=false);
CREATE PUBLICATION
</programlisting>
<programlisting>
test_sub=# ALTER SUBSCRIPTION s4 REFRESH PUBLICATION;
ALTER SUBSCRIPTION
</programlisting></para>

   <para>
    Do the inserts on the publisher same as before. They replicate using the
    row filter of <literal>child</literal> (because
    <literal>publish_via_partition_root</literal> is false).
<programlisting>
test_pub=# TRUNCATE parent;
TRUNCATE TABLE
test_pub=# INSERT INTO parent VALUES (2), (4), (6);
INSERT 0 3
test_pub=# INSERT INTO child VALUES (3), (5), (7);
INSERT 0 3

test_pub=# SELECT * FROM parent ORDER BY a;
 a
---
 2
 3
 4
 5
 6
 7
(6 rows)
</programlisting>
<programlisting>
test_sub=# SELECT * FROM child ORDER BY a;
 a
---
 5
 6
 7
(3 rows)
</programlisting></para>

  </sect2>

 </sect1>

 <sect1 id="logical-replication-col-lists">
  <title>Column Lists</title>

  <para>
   Each publication can optionally specify which columns of each table are
   replicated to subscribers. The table on the subscriber side must have at
   least all the columns that are published. If no column list is specified,
   then all columns on the publisher are replicated.
   See <xref linkend="sql-createpublication"/> for details on the syntax.
  </para>

  <para>
   The choice of columns can be based on behavioral or performance reasons.
   However, do not rely on this feature for security: a malicious subscriber
   is able to obtain data from columns that are not specifically
   published.  If security is a consideration, protections can be applied
   at the publisher side.
  </para>

  <para>
   If no column list is specified, any columns added later are automatically
   replicated. This means that having a column list which names all columns
   is not the same as having no column list at all.
  </para>

  <para>
   A column list can contain only simple column references.  The order
   of columns in the list is not preserved.
  </para>

  <para>
   Specifying a column list when the publication also publishes
   <literal>FOR TABLES IN SCHEMA</literal> is not supported.
  </para>

  <para>
   For partitioned tables, the publication parameter
   <literal>publish_via_partition_root</literal> determines which column list
   is used. If <literal>publish_via_partition_root</literal> is
   <literal>true</literal>, the root partitioned table's column list is used.
   Otherwise, if <literal>publish_via_partition_root</literal> is
   <literal>false</literal> (the default), each partition's column list is used.
  </para>

  <para>
   If a publication publishes <command>UPDATE</command> or
   <command>DELETE</command> operations, any column list must include the
   table's replica identity columns (see
   <xref linkend="sql-altertable-replica-identity"/>).
   If a publication publishes only <command>INSERT</command> operations, then
   the column list may omit replica identity columns.
  </para>

  <para>
   Column lists have no effect for the <literal>TRUNCATE</literal> command.
  </para>

  <para>
   During initial data synchronization, only the published columns are
   copied.  However, if the subscriber is from a release prior to 15, then
   all the columns in the table are copied during initial data synchronization,
   ignoring any column lists.
  </para>

   <warning id="logical-replication-col-list-combining">
    <title>Warning: Combining Column Lists from Multiple Publications</title>
    <para>
     There's currently no support for subscriptions comprising several
     publications where the same table has been published with different
     column lists.  <xref linkend="sql-createsubscription"/> disallows
     creating such subscriptions, but it is still possible to get into
     that situation by adding or altering column lists on the publication
     side after a subscription has been created.
    </para>
    <para>
     This means changing the column lists of tables on publications that are
     already subscribed could lead to errors being thrown on the subscriber
     side.
    </para>
    <para>
     If a subscription is affected by this problem, the only way to resume
     replication is to adjust one of the column lists on the publication
     side so that they all match; and then either recreate the subscription,
     or use <literal>ALTER SUBSCRIPTION ... DROP PUBLICATION</literal> to
     remove one of the offending publications and add it again.
    </para>
   </warning>

  <sect2 id="logical-replication-col-list-examples">
   <title>Examples</title>

   <para>
    Create a table <literal>t1</literal> to be used in the following example.
<programlisting>
test_pub=# CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id));
CREATE TABLE
</programlisting></para>

   <para>
    Create a publication <literal>p1</literal>. A column list is defined for
    table <literal>t1</literal> to reduce the number of columns that will be
    replicated. Notice that the order of column names in the column list does
    not matter.
<programlisting>
test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 (id, b, a, d);
CREATE PUBLICATION
</programlisting></para>

    <para>
     <literal>psql</literal> can be used to show the column lists (if defined)
     for each publication.
<programlisting>
test_pub=# \dRp+
                               Publication p1
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.t1" (id, a, b, d)
</programlisting></para>

    <para>
     <literal>psql</literal> can be used to show the column lists (if defined)
     for each table.
<programlisting>
test_pub=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 a      | text    |           |          |
 b      | text    |           |          |
 c      | text    |           |          |
 d      | text    |           |          |
 e      | text    |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)
Publications:
    "p1" (id, a, b, d)
</programlisting></para>

    <para>
     On the subscriber node, create a table <literal>t1</literal> which now
     only needs a subset of the columns that were on the publisher table
     <literal>t1</literal>, and also create the subscription
     <literal>s1</literal> that subscribes to the publication
     <literal>p1</literal>.
<programlisting>
test_sub=# CREATE TABLE t1(id int, b text, a text, d text, PRIMARY KEY(id));
CREATE TABLE
test_sub=# CREATE SUBSCRIPTION s1
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1'
test_sub-# PUBLICATION p1;
CREATE SUBSCRIPTION
</programlisting></para>

    <para>
     On the publisher node, insert some rows to table <literal>t1</literal>.
<programlisting>
test_pub=# INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES(3, 'a-3', 'b-3', 'c-3', 'd-3', 'e-3');
INSERT 0 1
test_pub=# SELECT * FROM t1 ORDER BY id;
 id |  a  |  b  |  c  |  d  |  e
----+-----+-----+-----+-----+-----
  1 | a-1 | b-1 | c-1 | d-1 | e-1
  2 | a-2 | b-2 | c-2 | d-2 | e-2
  3 | a-3 | b-3 | c-3 | d-3 | e-3
(3 rows)
</programlisting></para>

    <para>
     Only data from the column list of publication <literal>p1</literal> is
     replicated.
<programlisting>
test_sub=# SELECT * FROM t1 ORDER BY id;
 id |  b  |  a  |  d
----+-----+-----+-----
  1 | b-1 | a-1 | d-1
  2 | b-2 | a-2 | d-2
  3 | b-3 | a-3 | d-3
(3 rows)
</programlisting></para>

  </sect2>

 </sect1>

 <sect1 id="logical-replication-conflicts">
  <title>Conflicts</title>

  <para>
   Logical replication behaves similarly to normal DML operations in that
   the data will be updated even if it was changed locally on the subscriber
   node.  If incoming data violates any constraints the replication will
   stop.  This is referred to as a <firstterm>conflict</firstterm>.  When
   replicating <command>UPDATE</command> or <command>DELETE</command>
   operations, missing data will not produce a conflict and such operations
   will simply be skipped.
  </para>

  <para>
   Logical replication operations are performed with the privileges of the role
   which owns the subscription.  Permissions failures on target tables will
   cause replication conflicts, as will enabled
   <link linkend="ddl-rowsecurity">row-level security</link> on target tables
   that the subscription owner is subject to, without regard to whether any
   policy would ordinarily reject the <command>INSERT</command>,
   <command>UPDATE</command>, <command>DELETE</command> or
   <command>TRUNCATE</command> which is being replicated.  This restriction on
   row-level security may be lifted in a future version of
   <productname>PostgreSQL</productname>.
  </para>

  <para>
   A conflict will produce an error and will stop the replication; it must be
   resolved manually by the user.  Details about the conflict can be found in
   the subscriber's server log.
  </para>

  <para>
   The resolution can be done either by changing data or permissions on the subscriber so
   that it does not conflict with the incoming change or by skipping the
   transaction that conflicts with the existing data.  When a conflict produces
   an error, the replication won't proceed, and the logical replication worker will
   emit the following kind of message to the subscriber's server log:
<screen>
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (c)=(1) already exists.
CONTEXT:  processing remote data for replication origin "pg_16395" during "INSERT" for replication target relation "public.test" in transaction 725 finished at 0/14C0378
</screen>
   The LSN of the transaction that contains the change violating the constraint and
   the replication origin name can be found from the server log (LSN 0/14C0378 and
   replication origin <literal>pg_16395</literal> in the above case).  The
   transaction that produced the conflict can be skipped by using
   <command>ALTER SUBSCRIPTION ... SKIP</command> with the finish LSN
   (i.e., LSN 0/14C0378).  The finish LSN could be an LSN at which the transaction
   is committed or prepared on the publisher.  Alternatively, the transaction can
   also be skipped by calling the <link linkend="pg-replication-origin-advance">
   <function>pg_replication_origin_advance()</function></link> function.
   Before using this function, the subscription needs to be disabled temporarily
   either by <command>ALTER SUBSCRIPTION ... DISABLE</command> or, the
   subscription can be used with the <literal>disable_on_error</literal> option.
   Then, you can use <function>pg_replication_origin_advance()</function> function
   with the <parameter>node_name</parameter> (i.e., <literal>pg_16395</literal>)
   and the next LSN of the finish LSN (i.e., 0/14C0379).  The current position of
   origins can be seen in the <link linkend="view-pg-replication-origin-status">
   <structname>pg_replication_origin_status</structname></link> system view.
   Please note that skipping the whole transaction includes skipping changes that
   might not violate any constraint.  This can easily make the subscriber
   inconsistent.
  </para>
 </sect1>

 <sect1 id="logical-replication-restrictions">
  <title>Restrictions</title>

  <para>
   Logical replication currently has the following restrictions or missing
   functionality.  These might be addressed in future releases.
  </para>

  <itemizedlist>
   <listitem>
    <para>
     The database schema and DDL commands are not replicated.  The initial
     schema can be copied by hand using <literal>pg_dump
     --schema-only</literal>.  Subsequent schema changes would need to be kept
     in sync manually.  (Note, however, that there is no need for the schemas
     to be absolutely the same on both sides.)  Logical replication is robust
     when schema definitions change in a live database: When the schema is
     changed on the publisher and replicated data starts arriving at the
     subscriber but does not fit into the table schema, replication will error
     until the schema is updated.  In many cases, intermittent errors can be
     avoided by applying additive schema changes to the subscriber first.
    </para>
   </listitem>

   <listitem>
    <para>
     Sequence data is not replicated.  The data in serial or identity columns
     backed by sequences will of course be replicated as part of the table,
     but the sequence itself would still show the start value on the
     subscriber.  If the subscriber is used as a read-only database, then this
     should typically not be a problem.  If, however, some kind of switchover
     or failover to the subscriber database is intended, then the sequences
     would need to be updated to the latest values, either by copying the
     current data from the publisher (perhaps
     using <command>pg_dump</command>) or by determining a sufficiently high
     value from the tables themselves.
    </para>
   </listitem>

   <listitem>
    <para>
     Replication of <command>TRUNCATE</command> commands is supported, but
     some care must be taken when truncating groups of tables connected by
     foreign keys.  When replicating a truncate action, the subscriber will
     truncate the same group of tables that was truncated on the publisher,
     either explicitly specified or implicitly collected via
     <literal>CASCADE</literal>, minus tables that are not part of the
     subscription.  This will work correctly if all affected tables are part
     of the same subscription.  But if some tables to be truncated on the
     subscriber have foreign-key links to tables that are not part of the same
     (or any) subscription, then the application of the truncate action on the
     subscriber will fail.
    </para>
   </listitem>

   <listitem>
    <para>
     Large objects (see <xref linkend="largeobjects"/>) are not replicated.
     There is no workaround for that, other than storing data in normal
     tables.
    </para>
   </listitem>

   <listitem>
    <para>
     Replication is only supported by tables, including partitioned tables.
     Attempts to replicate other types of relations, such as views, materialized
     views, or foreign tables, will result in an error.
    </para>
   </listitem>

   <listitem>
    <para>
     When replicating between partitioned tables, the actual replication
     originates, by default, from the leaf partitions on the publisher, so
     partitions on the publisher must also exist on the subscriber as valid
     target tables. (They could either be leaf partitions themselves, or they
     could be further subpartitioned, or they could even be independent
     tables.)  Publications can also specify that changes are to be replicated
     using the identity and schema of the partitioned root table instead of
     that of the individual leaf partitions in which the changes actually
     originate (see <link linkend="sql-createpublication"><command>CREATE PUBLICATION</command></link>).
    </para>
   </listitem>
  </itemizedlist>
 </sect1>

 <sect1 id="logical-replication-architecture">
  <title>Architecture</title>

  <para>
   Logical replication starts by copying a snapshot of the data on the
   publisher database.  Once that is done, changes on the publisher are sent
   to the subscriber as they occur in real time.  The subscriber applies data
   in the order in which commits were made on the publisher so that
   transactional consistency is guaranteed for the publications within any
   single subscription.
  </para>

  <para>
   Logical replication is built with an architecture similar to physical
   streaming replication (see <xref linkend="streaming-replication"/>).  It is
   implemented by <quote>walsender</quote> and <quote>apply</quote>
   processes.  The walsender process starts logical decoding (described
   in <xref linkend="logicaldecoding"/>) of the WAL and loads the standard
   logical decoding plugin (pgoutput).  The plugin transforms the changes read
   from WAL to the logical replication protocol
   (see <xref linkend="protocol-logical-replication"/>) and filters the data
   according to the publication specification.  The data is then continuously
   transferred using the streaming replication protocol to the apply worker,
   which maps the data to local tables and applies the individual changes as
   they are received, in correct transactional order.
  </para>

  <para>
   The apply process on the subscriber database always runs with
   <link linkend="guc-session-replication-role"><varname>session_replication_role</varname></link>
   set to <literal>replica</literal>. This means that, by default,
   triggers and rules will not fire on a subscriber. Users can optionally choose to
   enable triggers and rules on a table using the
   <link linkend="sql-altertable"><command>ALTER TABLE</command></link> command
   and the <literal>ENABLE TRIGGER</literal> and <literal>ENABLE RULE</literal>
   clauses.
  </para>

  <para>
   The logical replication apply process currently only fires row triggers,
   not statement triggers.  The initial table synchronization, however, is
   implemented like a <command>COPY</command> command and thus fires both row
   and statement triggers for <command>INSERT</command>.
  </para>

  <sect2 id="logical-replication-snapshot">
    <title>Initial Snapshot</title>
    <para>
     The initial data in existing subscribed tables are snapshotted and
     copied in a parallel instance of a special kind of apply process.
     This process will create its own replication slot and copy the existing
     data.  As soon as the copy is finished the table contents will become
     visible to other backends.  Once existing data is copied, the worker
     enters synchronization mode, which ensures that the table is brought
     up to a synchronized state with the main apply process by streaming
     any changes that happened during the initial data copy using standard
     logical replication.  During this synchronization phase, the changes
     are applied and committed in the same order as they happened on the
     publisher.  Once synchronization is done, control of the
     replication of the table is given back to the main apply process where
     replication continues as normal.
    </para>
    <note>
     <para>
      The publication <literal>publish</literal> parameter only affects what
      DML operations will be replicated. The initial data synchronization does
      not take this parameter into account when copying the existing table data.
     </para>
    </note>
  </sect2>
 </sect1>

 <sect1 id="logical-replication-monitoring">
  <title>Monitoring</title>

  <para>
   Because logical replication is based on a similar architecture as
   <link linkend="streaming-replication">physical streaming replication</link>,
   the monitoring on a publication node is similar to monitoring of a
   physical replication primary
   (see <xref linkend="streaming-replication-monitoring"/>).
  </para>

  <para>
   The monitoring information about subscription is visible in
   <link linkend="monitoring-pg-stat-subscription">
   <structname>pg_stat_subscription</structname></link>.
   This view contains one row for every subscription worker.  A subscription
   can have zero or more active subscription workers depending on its state.
  </para>

  <para>
   Normally, there is a single apply process running for an enabled
   subscription.  A disabled subscription or a crashed subscription will have
   zero rows in this view.  If the initial data synchronization of any
   table is in progress, there will be additional workers for the tables
   being synchronized.
  </para>
 </sect1>

 <sect1 id="logical-replication-security">
  <title>Security</title>

  <para>
   A user able to modify the schema of subscriber-side tables can execute
   arbitrary code as the role which owns any subscription which modifies those tables.  Limit ownership
   and <literal>TRIGGER</literal> privilege on such tables to trusted roles.
   Moreover, if untrusted users can create tables, use only
   publications that list tables explicitly.  That is to say, create a
   subscription <literal>FOR ALL TABLES</literal> or
   <literal>FOR TABLES IN SCHEMA</literal> only when superusers trust
   every user permitted to create a non-temp table on the publisher or the
   subscriber.
  </para>

  <para>
   The role used for the replication connection must have
   the <literal>REPLICATION</literal> attribute (or be a superuser).  If the
   role lacks <literal>SUPERUSER</literal> and <literal>BYPASSRLS</literal>,
   publisher row security policies can execute.  If the role does not trust
   all table owners, include <literal>options=-crow_security=off</literal> in
   the connection string; if a table owner then adds a row security policy,
   that setting will cause replication to halt rather than execute the policy.
   Access for the role must be configured in <filename>pg_hba.conf</filename>
   and it must have the <literal>LOGIN</literal> attribute.
  </para>

  <para>
   In order to be able to copy the initial table data, the role used for the
   replication connection must have the <literal>SELECT</literal> privilege on
   a published table (or be a superuser).
  </para>

  <para>
   To create a publication, the user must have the <literal>CREATE</literal>
   privilege in the database.
  </para>

  <para>
   To add tables to a publication, the user must have ownership rights on the
   table. To add all tables in schema to a publication, the user must be a
   superuser. To create a publication that publishes all tables or all tables in
   schema automatically, the user must be a superuser.
  </para>

  <para>
   To create a subscription, the user must be a superuser.
  </para>

  <para>
   The subscription apply process will run in the local database with the
   privileges of the subscription owner.
  </para>

  <para>
   On the publisher, privileges are only checked once at the start of a
   replication connection and are not re-checked as each change record is read.
  </para>

  <para>
   On the subscriber, the subscription owner's privileges are re-checked for
   each transaction when applied. If a worker is in the process of applying a
   transaction when the ownership of the subscription is changed by a
   concurrent transaction, the application of the current transaction will
   continue under the old owner's privileges.
  </para>
 </sect1>

 <sect1 id="logical-replication-config">
  <title>Configuration Settings</title>

  <para>
   Logical replication requires several configuration options to be set.
  </para>

  <para>
   On the publisher side, <varname>wal_level</varname> must be set to
   <literal>logical</literal>, and <varname>max_replication_slots</varname>
   must be set to at least the number of subscriptions expected to connect,
   plus some reserve for table synchronization.  And
   <varname>max_wal_senders</varname> should be set to at least the same as
   <varname>max_replication_slots</varname> plus the number of physical
   replicas that are connected at the same time.
  </para>

  <para>
   <varname>max_replication_slots</varname> must also be set on the subscriber.
   It should be set to at least the number of subscriptions that will be added
   to the subscriber, plus some reserve for table synchronization.
   <varname>max_logical_replication_workers</varname> must be set to at least
   the number of subscriptions, again plus some reserve for the table
   synchronization.  Additionally the <varname>max_worker_processes</varname>
   may need to be adjusted to accommodate for replication workers, at least
   (<varname>max_logical_replication_workers</varname>
   + <literal>1</literal>).  Note that some extensions and parallel queries
   also take worker slots from <varname>max_worker_processes</varname>.
  </para>
 </sect1>

 <sect1 id="logical-replication-quick-setup">
  <title>Quick Setup</title>

  <para>
   First set the configuration options in <filename>postgresql.conf</filename>:
<programlisting>
wal_level = logical
</programlisting>
   The other required settings have default values that are sufficient for a
   basic setup.
  </para>

  <para>
   <filename>pg_hba.conf</filename> needs to be adjusted to allow replication
   (the values here depend on your actual network configuration and user you
   want to use for connecting):
<programlisting>
host     all     repuser     0.0.0.0/0     md5
</programlisting>
  </para>

  <para>
   Then on the publisher database:
<programlisting>
CREATE PUBLICATION mypub FOR TABLE users, departments;
</programlisting>
  </para>

  <para>
   And on the subscriber database:
<programlisting>
CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION mypub;
</programlisting>
  </para>

  <para>
   The above will start the replication process, which synchronizes the
   initial table contents of the tables <literal>users</literal> and
   <literal>departments</literal> and then starts replicating
   incremental changes to those tables.
  </para>
 </sect1>
</chapter>