Debian Codegraph @

doc/src/sgml/logicaldecoding.sgml

raw
1 <!-- doc/src/sgml/logicaldecoding.sgml -->
2 <chapter id="logicaldecoding">
3 <title>Logical Decoding</title>
4 <indexterm zone="logicaldecoding">
5 <primary>Logical Decoding</primary>
6 </indexterm>
7 <para>
8 PostgreSQL provides infrastructure to stream the modifications performed
9 via SQL to external consumers. This functionality can be used for a
10 variety of purposes, including replication solutions and auditing.
11 </para>
12
13 <para>
14 Changes are sent out in streams identified by logical replication slots.
15 </para>
16
17 <para>
18 The format in which those changes are streamed is determined by the output
19 plugin used. An example plugin is provided in the PostgreSQL distribution.
20 Additional plugins can be
21 written to extend the choice of available formats without modifying any
22 core code.
23 Every output plugin has access to each individual new row produced
24 by <command>INSERT</command> and the new row version created
25 by <command>UPDATE</command>. Availability of old row versions for
26 <command>UPDATE</command> and <command>DELETE</command> depends on
27 the configured replica identity (see <xref linkend="sql-altertable-replica-identity"/>).
28 </para>
29
30 <para>
31 Changes can be consumed either using the streaming replication protocol
32 (see <xref linkend="protocol-replication"/> and
33 <xref linkend="logicaldecoding-walsender"/>), or by calling functions
34 via SQL (see <xref linkend="logicaldecoding-sql"/>). It is also possible
35 to write additional methods of consuming the output of a replication slot
36 without modifying core code
37 (see <xref linkend="logicaldecoding-writer"/>).
38 </para>
39
40 <sect1 id="logicaldecoding-example">
41 <title>Logical Decoding Examples</title>
42
43 <para>
44 The following example demonstrates controlling logical decoding using the
45 SQL interface.
46 </para>
47
48 <para>
49 Before you can use logical decoding, you must set
50 <xref linkend="guc-wal-level"/> to <literal>logical</literal> and
51 <xref linkend="guc-max-replication-slots"/> to at least 1. Then, you
52 should connect to the target database (in the example
53 below, <literal>postgres</literal>) as a superuser.
54 </para>
55
56 <programlisting>
57 postgres=# -- Create a slot named 'regression_slot' using the output plugin 'test_decoding'
58 postgres=# SELECT * FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding', false, true);
59 slot_name | lsn
60 -----------------+-----------
61 regression_slot | 0/16B1970
62 (1 row)
63
64 postgres=# SELECT slot_name, plugin, slot_type, database, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots;
65 slot_name | plugin | slot_type | database | active | restart_lsn | confirmed_flush_lsn
66 -----------------+---------------+-----------+----------+--------+-------------+-----------------
67 regression_slot | test_decoding | logical | postgres | f | 0/16A4408 | 0/16A4440
68 (1 row)
69
70 postgres=# -- There are no changes to see yet
71 postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
72 lsn | xid | data
73 -----+-----+------
74 (0 rows)
75
76 postgres=# CREATE TABLE data(id serial primary key, data text);
77 CREATE TABLE
78
79 postgres=# -- DDL isn't replicated, so all you'll see is the transaction
80 postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
81 lsn | xid | data
82 -----------+-------+--------------
83 0/BA2DA58 | 10297 | BEGIN 10297
84 0/BA5A5A0 | 10297 | COMMIT 10297
85 (2 rows)
86
87 postgres=# -- Once changes are read, they're consumed and not emitted
88 postgres=# -- in a subsequent call:
89 postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
90 lsn | xid | data
91 -----+-----+------
92 (0 rows)
93
94 postgres=# BEGIN;
95 postgres=*# INSERT INTO data(data) VALUES('1');
96 postgres=*# INSERT INTO data(data) VALUES('2');
97 postgres=*# COMMIT;
98
99 postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
100 lsn | xid | data
101 -----------+-------+---------------------------------------------------------
102 0/BA5A688 | 10298 | BEGIN 10298
103 0/BA5A6F0 | 10298 | table public.data: INSERT: id[integer]:1 data[text]:'1'
104 0/BA5A7F8 | 10298 | table public.data: INSERT: id[integer]:2 data[text]:'2'
105 0/BA5A8A8 | 10298 | COMMIT 10298
106 (4 rows)
107
108 postgres=# INSERT INTO data(data) VALUES('3');
109
110 postgres=# -- You can also peek ahead in the change stream without consuming changes
111 postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL);
112 lsn | xid | data
113 -----------+-------+---------------------------------------------------------
114 0/BA5A8E0 | 10299 | BEGIN 10299
115 0/BA5A8E0 | 10299 | table public.data: INSERT: id[integer]:3 data[text]:'3'
116 0/BA5A990 | 10299 | COMMIT 10299
117 (3 rows)
118
119 postgres=# -- The next call to pg_logical_slot_peek_changes() returns the same changes again
120 postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL);
121 lsn | xid | data
122 -----------+-------+---------------------------------------------------------
123 0/BA5A8E0 | 10299 | BEGIN 10299
124 0/BA5A8E0 | 10299 | table public.data: INSERT: id[integer]:3 data[text]:'3'
125 0/BA5A990 | 10299 | COMMIT 10299
126 (3 rows)
127
128 postgres=# -- options can be passed to output plugin, to influence the formatting
129 postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL, 'include-timestamp', 'on');
130 lsn | xid | data
131 -----------+-------+---------------------------------------------------------
132 0/BA5A8E0 | 10299 | BEGIN 10299
133 0/BA5A8E0 | 10299 | table public.data: INSERT: id[integer]:3 data[text]:'3'
134 0/BA5A990 | 10299 | COMMIT 10299 (at 2017-05-10 12:07:21.272494-04)
135 (3 rows)
136
137 postgres=# -- Remember to destroy a slot you no longer need to stop it consuming
138 postgres=# -- server resources:
139 postgres=# SELECT pg_drop_replication_slot('regression_slot');
140 pg_drop_replication_slot
141 -----------------------
142
143 (1 row)
144 </programlisting>
145
146 <para>
147 The following examples show how logical decoding is controlled over the
148 streaming replication protocol, using the
149 program <xref linkend="app-pgrecvlogical"/> included in the PostgreSQL
150 distribution. This requires that client authentication is set up to allow
151 replication connections
152 (see <xref linkend="streaming-replication-authentication"/>) and
153 that <varname>max_wal_senders</varname> is set sufficiently high to allow
154 an additional connection. The second example shows how to stream two-phase
155 transactions. Before you use two-phase commands, you must set
156 <xref linkend="guc-max-prepared-transactions"/> to at least 1.
157 </para>
158 <programlisting>
159 Example 1:
160 $ pg_recvlogical -d postgres --slot=test --create-slot
161 $ pg_recvlogical -d postgres --slot=test --start -f -
162 <keycombo action="simul"><keycap>Control</keycap><keycap>Z</keycap></keycombo>
163 $ psql -d postgres -c "INSERT INTO data(data) VALUES('4');"
164 $ fg
165 BEGIN 693
166 table public.data: INSERT: id[integer]:4 data[text]:'4'
167 COMMIT 693
168 <keycombo action="simul"><keycap>Control</keycap><keycap>C</keycap></keycombo>
169 $ pg_recvlogical -d postgres --slot=test --drop-slot
170
171 Example 2:
172 $ pg_recvlogical -d postgres --slot=test --create-slot --enable-two-phase
173 $ pg_recvlogical -d postgres --slot=test --start -f -
174 <keycombo action="simul"><keycap>Control</keycap><keycap>Z</keycap></keycombo>
175 $ psql -d postgres -c "BEGIN;INSERT INTO data(data) VALUES('5');PREPARE TRANSACTION 'test';"
176 $ fg
177 BEGIN 694
178 table public.data: INSERT: id[integer]:5 data[text]:'5'
179 PREPARE TRANSACTION 'test', txid 694
180 <keycombo action="simul"><keycap>Control</keycap><keycap>Z</keycap></keycombo>
181 $ psql -d postgres -c "COMMIT PREPARED 'test';"
182 $ fg
183 COMMIT PREPARED 'test', txid 694
184 <keycombo action="simul"><keycap>Control</keycap><keycap>C</keycap></keycombo>
185 $ pg_recvlogical -d postgres --slot=test --drop-slot
186 </programlisting>
187
188 <para>
189 The following example shows SQL interface that can be used to decode prepared
190 transactions. Before you use two-phase commit commands, you must set
191 <varname>max_prepared_transactions</varname> to at least 1. You must also have
192 set the two-phase parameter as 'true' while creating the slot using
193 <function>pg_create_logical_replication_slot</function>
194 Note that we will stream the entire transaction after the commit if it
195 is not already decoded.
196 </para>
197 <programlisting>
198 postgres=# BEGIN;
199 postgres=*# INSERT INTO data(data) VALUES('5');
200 postgres=*# PREPARE TRANSACTION 'test_prepared1';
201
202 postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
203 lsn | xid | data
204 -----------+-----+---------------------------------------------------------
205 0/1689DC0 | 529 | BEGIN 529
206 0/1689DC0 | 529 | table public.data: INSERT: id[integer]:3 data[text]:'5'
207 0/1689FC0 | 529 | PREPARE TRANSACTION 'test_prepared1', txid 529
208 (3 rows)
209
210 postgres=# COMMIT PREPARED 'test_prepared1';
211 postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL);
212 lsn | xid | data
213 -----------+-----+--------------------------------------------
214 0/168A060 | 529 | COMMIT PREPARED 'test_prepared1', txid 529
215 (4 row)
216
217 postgres=#-- you can also rollback a prepared transaction
218 postgres=# BEGIN;
219 postgres=*# INSERT INTO data(data) VALUES('6');
220 postgres=*# PREPARE TRANSACTION 'test_prepared2';
221 postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL);
222 lsn | xid | data
223 -----------+-----+---------------------------------------------------------
224 0/168A180 | 530 | BEGIN 530
225 0/168A1E8 | 530 | table public.data: INSERT: id[integer]:4 data[text]:'6'
226 0/168A430 | 530 | PREPARE TRANSACTION 'test_prepared2', txid 530
227 (3 rows)
228
229 postgres=# ROLLBACK PREPARED 'test_prepared2';
230 postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL);
231 lsn | xid | data
232 -----------+-----+----------------------------------------------
233 0/168A4B8 | 530 | ROLLBACK PREPARED 'test_prepared2', txid 530
234 (1 row)
235 </programlisting>
236 </sect1>
237
238 <sect1 id="logicaldecoding-explanation">
239 <title>Logical Decoding Concepts</title>
240 <sect2 id="logicaldecoding-explanation-log-dec">
241 <title>Logical Decoding</title>
242
243 <indexterm>
244 <primary>Logical Decoding</primary>
245 </indexterm>
246
247 <para>
248 Logical decoding is the process of extracting all persistent changes
249 to a database's tables into a coherent, easy to understand format which
250 can be interpreted without detailed knowledge of the database's internal
251 state.
252 </para>
253
254 <para>
255 In <productname>PostgreSQL</productname>, logical decoding is implemented
256 by decoding the contents of the <link linkend="wal">write-ahead
257 log</link>, which describe changes on a storage level, into an
258 application-specific form such as a stream of tuples or SQL statements.
259 </para>
260 </sect2>
261
262 <sect2 id="logicaldecoding-replication-slots">
263 <title>Replication Slots</title>
264
265 <indexterm>
266 <primary>replication slot</primary>
267 <secondary>logical replication</secondary>
268 </indexterm>
269
270 <para>
271 In the context of logical replication, a slot represents a stream of
272 changes that can be replayed to a client in the order they were made on
273 the origin server. Each slot streams a sequence of changes from a single
274 database.
275 </para>
276
277 <note>
278 <para><productname>PostgreSQL</productname> also has streaming replication slots
279 (see <xref linkend="streaming-replication"/>), but they are used somewhat
280 differently there.
281 </para>
282 </note>
283
284 <para>
285 A replication slot has an identifier that is unique across all databases
286 in a <productname>PostgreSQL</productname> cluster. Slots persist
287 independently of the connection using them and are crash-safe.
288 </para>
289
290 <para>
291 A logical slot will emit each change just once in normal operation.
292 The current position of each slot is persisted only at checkpoint, so in
293 the case of a crash the slot might return to an earlier LSN, which will
294 then cause recent changes to be sent again when the server restarts.
295 Logical decoding clients are responsible for avoiding ill effects from
296 handling the same message more than once. Clients may wish to record
297 the last LSN they saw when decoding and skip over any repeated data or
298 (when using the replication protocol) request that decoding start from
299 that LSN rather than letting the server determine the start point.
300 The Replication Progress Tracking feature is designed for this purpose,
301 refer to <link linkend="replication-origins">replication origins</link>.
302 </para>
303
304 <para>
305 Multiple independent slots may exist for a single database. Each slot has
306 its own state, allowing different consumers to receive changes from
307 different points in the database change stream. For most applications, a
308 separate slot will be required for each consumer.
309 </para>
310
311 <para>
312 A logical replication slot knows nothing about the state of the
313 receiver(s). It's even possible to have multiple different receivers using
314 the same slot at different times; they'll just get the changes following
315 on from when the last receiver stopped consuming them. Only one receiver
316 may consume changes from a slot at any given time.
317 </para>
318
319 <para>
320 A logical replication slot can also be created on a hot standby. To prevent
321 <command>VACUUM</command> from removing required rows from the system
322 catalogs, <varname>hot_standby_feedback</varname> should be set on the
323 standby. In spite of that, if any required rows get removed, the slot gets
324 invalidated. It's highly recommended to use a physical slot between the
325 primary and the standby. Otherwise, <varname>hot_standby_feedback</varname>
326 will work but only while the connection is alive (for example a node
327 restart would break it). Then, the primary may delete system catalog rows
328 that could be needed by the logical decoding on the standby (as it does
329 not know about the <literal>catalog_xmin</literal> on the standby).
330 Existing logical slots on standby also get invalidated if
331 <varname>wal_level</varname> on the primary is reduced to less than
332 <literal>logical</literal>.
333 This is done as soon as the standby detects such a change in the WAL stream.
334 It means that, for walsenders that are lagging (if any), some WAL records up
335 to the <varname>wal_level</varname> parameter change on the primary won't be
336 decoded.
337 </para>
338
339 <para>
340 Creation of a logical slot requires information about all the currently
341 running transactions. On the primary, this information is available
342 directly, but on a standby, this information has to be obtained from
343 primary. Thus, slot creation may need to wait for some activity to happen
344 on the primary. If the primary is idle, creating a logical slot on
345 standby may take noticeable time. This can be sped up by calling the
346 <function>pg_log_standby_snapshot</function> function on the primary.
347 </para>
348
349 <caution>
350 <para>
351 Replication slots persist across crashes and know nothing about the state
352 of their consumer(s). They will prevent removal of required resources
353 even when there is no connection using them. This consumes storage
354 because neither required WAL nor required rows from the system catalogs
355 can be removed by <command>VACUUM</command> as long as they are required by a replication
356 slot. In extreme cases this could cause the database to shut down to prevent
357 transaction ID wraparound (see <xref linkend="vacuum-for-wraparound"/>).
358 So if a slot is no longer required it should be dropped.
359 </para>
360 </caution>
361
362 </sect2>
363
364 <sect2 id="logicaldecoding-replication-slots-synchronization">
365 <title>Replication Slot Synchronization</title>
366 <para>
367 The logical replication slots on the primary can be synchronized to
368 the hot standby by using the <literal>failover</literal> parameter of
369 <link linkend="pg-create-logical-replication-slot">
370 <function>pg_create_logical_replication_slot</function></link>, or by
371 using the <link linkend="sql-createsubscription-params-with-failover">
372 <literal>failover</literal></link> option of
373 <command>CREATE SUBSCRIPTION</command> during slot creation.
374 Additionally, enabling <link linkend="guc-sync-replication-slots">
375 <varname>sync_replication_slots</varname></link> on the standby
376 is required. By enabling <link linkend="guc-sync-replication-slots">
377 <varname>sync_replication_slots</varname></link>
378 on the standby, the failover slots can be synchronized periodically in
379 the slotsync worker. For the synchronization to work, it is mandatory to
380 have a physical replication slot between the primary and the standby (i.e.,
381 <link linkend="guc-primary-slot-name"><varname>primary_slot_name</varname></link>
382 should be configured on the standby), and
383 <link linkend="guc-hot-standby-feedback"><varname>hot_standby_feedback</varname></link>
384 must be enabled on the standby. It is also necessary to specify a valid
385 <literal>dbname</literal> in the
386 <link linkend="guc-primary-conninfo"><varname>primary_conninfo</varname></link>.
387 It's highly recommended that the said physical replication slot is named in
388 <link linkend="guc-synchronized-standby-slots"><varname>synchronized_standby_slots</varname></link>
389 list on the primary, to prevent the subscriber from consuming changes
390 faster than the hot standby. Even when correctly configured, some latency
391 is expected when sending changes to logical subscribers due to the waiting
392 on slots named in
393 <link linkend="guc-synchronized-standby-slots"><varname>synchronized_standby_slots</varname></link>.
394 When <varname>synchronized_standby_slots</varname> is utilized, the
395 primary server will not completely shut down until the corresponding
396 standbys, associated with the physical replication slots specified
397 in <varname>synchronized_standby_slots</varname>, have confirmed
398 receiving the WAL up to the latest flushed position on the primary server.
399 </para>
400
401 <note>
402 <para>
403 While enabling <link linkend="guc-sync-replication-slots">
404 <varname>sync_replication_slots</varname></link> allows for automatic
405 periodic synchronization of failover slots, they can also be manually
406 synchronized using the <link linkend="pg-sync-replication-slots">
407 <function>pg_sync_replication_slots</function></link> function on the standby.
408 However, this function is primarily intended for testing and debugging and
409 should be used with caution. Unlike automatic synchronization, it does not
410 include cyclic retries, making it more prone to synchronization failures,
411 particularly during initial sync scenarios where the required WAL files
412 or catalog rows for the slot might have already been removed or are at risk
413 of being removed on the standby. In contrast, automatic synchronization
414 via <varname>sync_replication_slots</varname> provides continuous slot
415 updates, enabling seamless failover and supporting high availability.
416 Therefore, it is the recommended method for synchronizing slots.
417 </para>
418 </note>
419
420 <para>
421 When slot synchronization is configured as recommended,
422 and the initial synchronization is performed either automatically or
423 manually via <function>pg_sync_replication_slots</function>, the standby
424 can persist the synchronized slot only if the following condition is met:
425 The logical replication slot on the primary must retain WALs and system
426 catalog rows that are still available on the standby. This ensures data
427 integrity and allows logical replication to continue smoothly after
428 promotion.
429 If the required WALs or catalog rows have already been purged from the
430 standby, the slot will not be persisted to avoid data loss. In such
431 cases, the following log message may appear:
432 <programlisting>
433 LOG: could not synchronize replication slot "failover_slot"
434 DETAIL: Synchronization could lead to data loss, because the remote slot needs WAL at LSN 0/3003F28 and catalog xmin 754, but the standby has LSN 0/3003F28 and catalog xmin 756.
435 </programlisting>
436 If the logical replication slot is actively used by a consumer, no
437 manual intervention is needed; the slot will advance automatically,
438 and synchronization will resume in the next cycle. However, if no
439 consumer is configured, it is advisable to manually advance the slot
440 on the primary using <link linkend="pg-logical-slot-get-changes">
441 <function>pg_logical_slot_get_changes</function></link> or
442 <link linkend="pg-logical-slot-get-binary-changes">
443 <function>pg_logical_slot_get_binary_changes</function></link>,
444 allowing synchronization to proceed.
445 </para>
446
447 <para>
448 The ability to resume logical replication after failover depends upon the
449 <link linkend="view-pg-replication-slots">pg_replication_slots</link>.<structfield>synced</structfield>
450 value for the synchronized slots on the standby at the time of failover.
451 Only persistent slots that have attained synced state as true on the standby
452 before failover can be used for logical replication after failover.
453 Temporary synced slots cannot be used for logical decoding, therefore
454 logical replication for those slots cannot be resumed. For example, if the
455 synchronized slot could not become persistent on the standby due to a
456 disabled subscription, then the subscription cannot be resumed after
457 failover even when it is enabled.
458 </para>
459
460 <para>
461 To resume logical replication after failover from the synced logical
462 slots, the subscription's 'conninfo' must be altered to point to the
463 new primary server. This is done using
464 <link linkend="sql-altersubscription-params-connection"><command>ALTER SUBSCRIPTION ... CONNECTION</command></link>.
465 It is recommended that subscriptions are first disabled before promoting
466 the standby and are re-enabled after altering the connection string.
467 </para>
468 <caution>
469 <para>
470 There is a chance that the old primary is up again during the promotion
471 and if subscriptions are not disabled, the logical subscribers may
472 continue to receive data from the old primary server even after promotion
473 until the connection string is altered. This might result in data
474 inconsistency issues, preventing the logical subscribers from being
475 able to continue replication from the new primary server.
476 </para>
477 </caution>
478 </sect2>
479
480 <sect2 id="logicaldecoding-explanation-output-plugins">
481 <title>Output Plugins</title>
482 <para>
483 Output plugins transform the data from the write-ahead log's internal
484 representation into the format the consumer of a replication slot desires.
485 </para>
486 </sect2>
487
488 <sect2 id="logicaldecoding-explanation-exported-snapshots">
489 <title>Exported Snapshots</title>
490 <para>
491 When a new replication slot is created using the streaming replication
492 interface (see <xref linkend="protocol-replication-create-replication-slot"/>), a
493 snapshot is exported
494 (see <xref linkend="functions-snapshot-synchronization"/>), which will show
495 exactly the state of the database after which all changes will be
496 included in the change stream. This can be used to create a new replica by
497 using <link linkend="sql-set-transaction"><literal>SET TRANSACTION
498 SNAPSHOT</literal></link> to read the state of the database at the moment
499 the slot was created. This transaction can then be used to dump the
500 database's state at that point in time, which afterwards can be updated
501 using the slot's contents without losing any changes.
502 </para>
503 <para>
504 Applications that do not require
505 snapshot export may suppress it with the <literal>SNAPSHOT 'nothing'</literal>
506 option.
507 </para>
508 </sect2>
509 </sect1>
510
511 <sect1 id="logicaldecoding-walsender">
512 <title>Streaming Replication Protocol Interface</title>
513
514 <para>
515 The commands
516 <itemizedlist>
517 <listitem>
518 <para><literal>CREATE_REPLICATION_SLOT <replaceable>slot_name</replaceable> LOGICAL <replaceable>output_plugin</replaceable></literal></para>
519 </listitem>
520
521 <listitem>
522 <para><literal>DROP_REPLICATION_SLOT <replaceable>slot_name</replaceable></literal> <optional> <literal>WAIT</literal> </optional></para>
523 </listitem>
524
525 <listitem>
526 <para><literal>START_REPLICATION SLOT <replaceable>slot_name</replaceable> LOGICAL ...</literal></para>
527 </listitem>
528 </itemizedlist>
529 are used to create, drop, and stream changes from a replication
530 slot, respectively. These commands are only available over a replication
531 connection; they cannot be used via SQL.
532 See <xref linkend="protocol-replication"/> for details on these commands.
533 </para>
534
535 <para>
536 The command <xref linkend="app-pgrecvlogical"/> can be used to control
537 logical decoding over a streaming replication connection. (It uses
538 these commands internally.)
539 </para>
540 </sect1>
541
542 <sect1 id="logicaldecoding-sql">
543 <title>Logical Decoding <acronym>SQL</acronym> Interface</title>
544
545 <para>
546 See <xref linkend="functions-replication"/> for detailed documentation on
547 the SQL-level API for interacting with logical decoding.
548 </para>
549
550 <para>
551 Synchronous replication (see <xref linkend="synchronous-replication"/>) is
552 only supported on replication slots used over the streaming replication interface. The
553 function interface and additional, non-core interfaces do not support
554 synchronous replication.
555 </para>
556 </sect1>
557
558 <sect1 id="logicaldecoding-catalogs">
559 <title>System Catalogs Related to Logical Decoding</title>
560
561 <para>
562 The <link linkend="view-pg-replication-slots"><structname>pg_replication_slots</structname></link>
563 view and the
564 <link linkend="monitoring-pg-stat-replication-view">
565 <structname>pg_stat_replication</structname></link>
566 view provide information about the current state of replication slots and
567 streaming replication connections respectively. These views apply to both physical and
568 logical replication. The
569 <link linkend="monitoring-pg-stat-replication-slots-view">
570 <structname>pg_stat_replication_slots</structname></link>
571 view provides statistics information about the logical replication slots.
572 </para>
573 </sect1>
574
575 <sect1 id="logicaldecoding-output-plugin">
576 <title>Logical Decoding Output Plugins</title>
577 <para>
578 An example output plugin can be found in the
579 <link linkend="test-decoding">
580 <filename>contrib/test_decoding</filename>
581 </link>
582 subdirectory of the PostgreSQL source tree.
583 </para>
584 <sect2 id="logicaldecoding-output-init">
585 <title>Initialization Function</title>
586 <indexterm zone="logicaldecoding-output-init">
587 <primary>_PG_output_plugin_init</primary>
588 </indexterm>
589 <para>
590 An output plugin is loaded by dynamically loading a shared library with
591 the output plugin's name as the library base name. The normal library
592 search path is used to locate the library. To provide the required output
593 plugin callbacks and to indicate that the library is actually an output
594 plugin it needs to provide a function named
595 <function>_PG_output_plugin_init</function>. This function is passed a
596 struct that needs to be filled with the callback function pointers for
597 individual actions.
598 <programlisting>
599 typedef struct OutputPluginCallbacks
600 {
601 LogicalDecodeStartupCB startup_cb;
602 LogicalDecodeBeginCB begin_cb;
603 LogicalDecodeChangeCB change_cb;
604 LogicalDecodeTruncateCB truncate_cb;
605 LogicalDecodeCommitCB commit_cb;
606 LogicalDecodeMessageCB message_cb;
607 LogicalDecodeFilterByOriginCB filter_by_origin_cb;
608 LogicalDecodeShutdownCB shutdown_cb;
609 LogicalDecodeFilterPrepareCB filter_prepare_cb;
610 LogicalDecodeBeginPrepareCB begin_prepare_cb;
611 LogicalDecodePrepareCB prepare_cb;
612 LogicalDecodeCommitPreparedCB commit_prepared_cb;
613 LogicalDecodeRollbackPreparedCB rollback_prepared_cb;
614 LogicalDecodeStreamStartCB stream_start_cb;
615 LogicalDecodeStreamStopCB stream_stop_cb;
616 LogicalDecodeStreamAbortCB stream_abort_cb;
617 LogicalDecodeStreamPrepareCB stream_prepare_cb;
618 LogicalDecodeStreamCommitCB stream_commit_cb;
619 LogicalDecodeStreamChangeCB stream_change_cb;
620 LogicalDecodeStreamMessageCB stream_message_cb;
621 LogicalDecodeStreamTruncateCB stream_truncate_cb;
622 } OutputPluginCallbacks;
623
624 typedef void (*LogicalOutputPluginInit) (struct OutputPluginCallbacks *cb);
625 </programlisting>
626 The <function>begin_cb</function>, <function>change_cb</function>
627 and <function>commit_cb</function> callbacks are required,
628 while <function>startup_cb</function>, <function>truncate_cb</function>,
629 <function>message_cb</function>, <function>filter_by_origin_cb</function>,
630 and <function>shutdown_cb</function> are optional.
631 If <function>truncate_cb</function> is not set but a
632 <command>TRUNCATE</command> is to be decoded, the action will be ignored.
633 </para>
634
635 <para>
636 An output plugin may also define functions to support streaming of large,
637 in-progress transactions. The <function>stream_start_cb</function>,
638 <function>stream_stop_cb</function>, <function>stream_abort_cb</function>,
639 <function>stream_commit_cb</function>, and <function>stream_change_cb</function>
640 are required, while <function>stream_message_cb</function> and
641 <function>stream_truncate_cb</function> are optional. The
642 <function>stream_prepare_cb</function> is also required if the output
643 plugin also support two-phase commits.
644 </para>
645
646 <para>
647 An output plugin may also define functions to support two-phase commits,
648 which allows actions to be decoded on the <command>PREPARE TRANSACTION</command>.
649 The <function>begin_prepare_cb</function>, <function>prepare_cb</function>,
650 <function>commit_prepared_cb</function> and <function>rollback_prepared_cb</function>
651 callbacks are required, while <function>filter_prepare_cb</function> is optional.
652 The <function>stream_prepare_cb</function> is also required if the output plugin
653 also supports the streaming of large in-progress transactions.
654 </para>
655 </sect2>
656
657 <sect2 id="logicaldecoding-capabilities">
658 <title>Capabilities</title>
659
660 <para>
661 To decode, format and output changes, output plugins can use most of the
662 backend's normal infrastructure, including calling output functions. Read
663 only access to relations is permitted as long as only relations are
664 accessed that either have been created by <command>initdb</command> in
665 the <literal>pg_catalog</literal> schema, or have been marked as user
666 provided catalog tables using
667 <programlisting>
668 ALTER TABLE user_catalog_table SET (user_catalog_table = true);
669 CREATE TABLE another_catalog_table(data text) WITH (user_catalog_table = true);
670 </programlisting>
671 Note that access to user catalog tables or regular system catalog tables
672 in the output plugins has to be done via the <literal>systable_*</literal>
673 scan APIs only. Access via the <literal>heap_*</literal> scan APIs will
674 error out. Additionally, any actions leading to transaction ID assignment
675 are prohibited. That, among others, includes writing to tables, performing
676 DDL changes, and calling <literal>pg_current_xact_id()</literal>.
677 </para>
678 </sect2>
679
680 <sect2 id="logicaldecoding-output-mode">
681 <title>Output Modes</title>
682
683 <para>
684 Output plugin callbacks can pass data to the consumer in nearly arbitrary
685 formats. For some use cases, like viewing the changes via SQL, returning
686 data in a data type that can contain arbitrary data (e.g., <type>bytea</type>) is
687 cumbersome. If the output plugin only outputs textual data in the
688 server's encoding, it can declare that by
689 setting <literal>OutputPluginOptions.output_type</literal>
690 to <literal>OUTPUT_PLUGIN_TEXTUAL_OUTPUT</literal> instead
691 of <literal>OUTPUT_PLUGIN_BINARY_OUTPUT</literal> in
692 the <link linkend="logicaldecoding-output-plugin-startup">startup
693 callback</link>. In that case, all the data has to be in the server's encoding
694 so that a <type>text</type> datum can contain it. This is checked in assertion-enabled
695 builds.
696 </para>
697 </sect2>
698
699 <sect2 id="logicaldecoding-output-plugin-callbacks">
700 <title>Output Plugin Callbacks</title>
701
702 <para>
703 An output plugin gets notified about changes that are happening via
704 various callbacks it needs to provide.
705 </para>
706
707 <para>
708 Concurrent transactions are decoded in commit order, and only changes
709 belonging to a specific transaction are decoded between
710 the <literal>begin</literal> and <literal>commit</literal>
711 callbacks. Transactions that were rolled back explicitly or implicitly
712 never get
713 decoded. Successful savepoints are
714 folded into the transaction containing them in the order they were
715 executed within that transaction. A transaction that is prepared for
716 a two-phase commit using <command>PREPARE TRANSACTION</command> will
717 also be decoded if the output plugin callbacks needed for decoding
718 them are provided. It is possible that the current prepared transaction
719 which is being decoded is aborted concurrently via a
720 <command>ROLLBACK PREPARED</command> command. In that case, the logical
721 decoding of this transaction will be aborted too. All the changes of such
722 a transaction are skipped once the abort is detected and the
723 <function>prepare_cb</function> callback is invoked. Thus even in case of
724 a concurrent abort, enough information is provided to the output plugin
725 for it to properly deal with <command>ROLLBACK PREPARED</command> once
726 that is decoded.
727 </para>
728
729 <note>
730 <para>
731 Only transactions that have already safely been flushed to disk will be
732 decoded. That can lead to a <command>COMMIT</command> not immediately being decoded in a
733 directly following <literal>pg_logical_slot_get_changes()</literal>
734 when <varname>synchronous_commit</varname> is set
735 to <literal>off</literal>.
736 </para>
737 </note>
738
739 <sect3 id="logicaldecoding-output-plugin-startup">
740 <title>Startup Callback</title>
741 <para>
742 The optional <function>startup_cb</function> callback is called whenever
743 a replication slot is created or asked to stream changes, independent
744 of the number of changes that are ready to be put out.
745 <programlisting>
746 typedef void (*LogicalDecodeStartupCB) (struct LogicalDecodingContext *ctx,
747 OutputPluginOptions *options,
748 bool is_init);
749 </programlisting>
750 The <literal>is_init</literal> parameter will be true when the
751 replication slot is being created and false
752 otherwise. <parameter>options</parameter> points to a struct of options
753 that output plugins can set:
754 <programlisting>
755 typedef struct OutputPluginOptions
756 {
757 OutputPluginOutputType output_type;
758 bool receive_rewrites;
759 } OutputPluginOptions;
760 </programlisting>
761 <literal>output_type</literal> has to either be set to
762 <literal>OUTPUT_PLUGIN_TEXTUAL_OUTPUT</literal>
763 or <literal>OUTPUT_PLUGIN_BINARY_OUTPUT</literal>. See also
764 <xref linkend="logicaldecoding-output-mode"/>.
765 If <literal>receive_rewrites</literal> is true, the output plugin will
766 also be called for changes made by heap rewrites during certain DDL
767 operations. These are of interest to plugins that handle DDL
768 replication, but they require special handling.
769 </para>
770
771 <para>
772 The startup callback should validate the options present in
773 <literal>ctx-&gt;output_plugin_options</literal>. If the output plugin
774 needs to have a state, it can
775 use <literal>ctx-&gt;output_plugin_private</literal> to store it.
776 </para>
777 </sect3>
778
779 <sect3 id="logicaldecoding-output-plugin-shutdown">
780 <title>Shutdown Callback</title>
781
782 <para>
783 The optional <function>shutdown_cb</function> callback is called
784 whenever a formerly active replication slot is not used anymore and can
785 be used to deallocate resources private to the output plugin. The slot
786 isn't necessarily being dropped, streaming is just being stopped.
787 <programlisting>
788 typedef void (*LogicalDecodeShutdownCB) (struct LogicalDecodingContext *ctx);
789 </programlisting>
790 </para>
791 </sect3>
792
793 <sect3 id="logicaldecoding-output-plugin-begin">
794 <title>Transaction Begin Callback</title>
795
796 <para>
797 The required <function>begin_cb</function> callback is called whenever a
798 start of a committed transaction has been decoded. Aborted transactions
799 and their contents never get decoded.
800 <programlisting>
801 typedef void (*LogicalDecodeBeginCB) (struct LogicalDecodingContext *ctx,
802 ReorderBufferTXN *txn);
803 </programlisting>
804 The <parameter>txn</parameter> parameter contains meta information about
805 the transaction, like the time stamp at which it has been committed and
806 its XID.
807 </para>
808 </sect3>
809
810 <sect3 id="logicaldecoding-output-plugin-commit">
811 <title>Transaction End Callback</title>
812
813 <para>
814 The required <function>commit_cb</function> callback is called whenever
815 a transaction commit has been
816 decoded. The <function>change_cb</function> callbacks for all modified
817 rows will have been called before this, if there have been any modified
818 rows.
819 <programlisting>
820 typedef void (*LogicalDecodeCommitCB) (struct LogicalDecodingContext *ctx,
821 ReorderBufferTXN *txn,
822 XLogRecPtr commit_lsn);
823 </programlisting>
824 </para>
825 </sect3>
826
827 <sect3 id="logicaldecoding-output-plugin-change">
828 <title>Change Callback</title>
829
830 <para>
831 The required <function>change_cb</function> callback is called for every
832 individual row modification inside a transaction, may it be
833 an <command>INSERT</command>, <command>UPDATE</command>,
834 or <command>DELETE</command>. Even if the original command modified
835 several rows at once the callback will be called individually for each
836 row. The <function>change_cb</function> callback may access system or
837 user catalog tables to aid in the process of outputting the row
838 modification details. In case of decoding a prepared (but yet
839 uncommitted) transaction or decoding of an uncommitted transaction, this
840 change callback might also error out due to simultaneous rollback of
841 this very same transaction. In that case, the logical decoding of this
842 aborted transaction is stopped gracefully.
843 <programlisting>
844 typedef void (*LogicalDecodeChangeCB) (struct LogicalDecodingContext *ctx,
845 ReorderBufferTXN *txn,
846 Relation relation,
847 ReorderBufferChange *change);
848 </programlisting>
849 The <parameter>ctx</parameter> and <parameter>txn</parameter> parameters
850 have the same contents as for the <function>begin_cb</function>
851 and <function>commit_cb</function> callbacks, but additionally the
852 relation descriptor <parameter>relation</parameter> points to the
853 relation the row belongs to and a struct
854 <parameter>change</parameter> describing the row modification are passed
855 in.
856 </para>
857
858 <note>
859 <para>
860 Only changes in user defined tables that are not unlogged
861 (see <xref linkend="sql-createtable-unlogged"/>) and not temporary
862 (see <xref linkend="sql-createtable-temporary"/>) can be extracted using
863 logical decoding.
864 </para>
865 </note>
866 </sect3>
867
868 <sect3 id="logicaldecoding-output-plugin-truncate">
869 <title>Truncate Callback</title>
870
871 <para>
872 The optional <function>truncate_cb</function> callback is called for a
873 <command>TRUNCATE</command> command.
874 <programlisting>
875 typedef void (*LogicalDecodeTruncateCB) (struct LogicalDecodingContext *ctx,
876 ReorderBufferTXN *txn,
877 int nrelations,
878 Relation relations[],
879 ReorderBufferChange *change);
880 </programlisting>
881 The parameters are analogous to the <function>change_cb</function>
882 callback. However, because <command>TRUNCATE</command> actions on
883 tables connected by foreign keys need to be executed together, this
884 callback receives an array of relations instead of just a single one.
885 See the description of the <xref linkend="sql-truncate"/> statement for
886 details.
887 </para>
888 </sect3>
889
890 <sect3 id="logicaldecoding-output-plugin-filter-origin">
891 <title>Origin Filter Callback</title>
892
893 <para>
894 The optional <function>filter_by_origin_cb</function> callback
895 is called to determine whether data that has been replayed
896 from <parameter>origin_id</parameter> is of interest to the
897 output plugin.
898 <programlisting>
899 typedef bool (*LogicalDecodeFilterByOriginCB) (struct LogicalDecodingContext *ctx,
900 RepOriginId origin_id);
901 </programlisting>
902 The <parameter>ctx</parameter> parameter has the same contents
903 as for the other callbacks. No information but the origin is
904 available. To signal that changes originating on the passed in
905 node are irrelevant, return true, causing them to be filtered
906 away; false otherwise. The other callbacks will not be called
907 for transactions and changes that have been filtered away.
908 </para>
909 <para>
910 This is useful when implementing cascading or multidirectional
911 replication solutions. Filtering by the origin allows to
912 prevent replicating the same changes back and forth in such
913 setups. While transactions and changes also carry information
914 about the origin, filtering via this callback is noticeably
915 more efficient.
916 </para>
917 </sect3>
918
919 <sect3 id="logicaldecoding-output-plugin-message">
920 <title>Generic Message Callback</title>
921
922 <para>
923 The optional <function>message_cb</function> callback is called whenever
924 a logical decoding message has been decoded.
925 <programlisting>
926 typedef void (*LogicalDecodeMessageCB) (struct LogicalDecodingContext *ctx,
927 ReorderBufferTXN *txn,
928 XLogRecPtr message_lsn,
929 bool transactional,
930 const char *prefix,
931 Size message_size,
932 const char *message);
933 </programlisting>
934 The <parameter>txn</parameter> parameter contains meta information about
935 the transaction, like the time stamp at which it has been committed and
936 its XID. Note however that it can be NULL when the message is
937 non-transactional and the XID was not assigned yet in the transaction
938 which logged the message. The <parameter>lsn</parameter> has WAL
939 location of the message. The <parameter>transactional</parameter> says
940 if the message was sent as transactional or not. Similar to the change
941 callback, in case of decoding a prepared (but yet uncommitted)
942 transaction or decoding of an uncommitted transaction, this message
943 callback might also error out due to simultaneous rollback of
944 this very same transaction. In that case, the logical decoding of this
945 aborted transaction is stopped gracefully.
946 The <parameter>prefix</parameter> is arbitrary null-terminated prefix
947 which can be used for identifying interesting messages for the current
948 plugin. And finally the <parameter>message</parameter> parameter holds
949 the actual message of <parameter>message_size</parameter> size.
950 </para>
951 <para>
952 Extra care should be taken to ensure that the prefix the output plugin
953 considers interesting is unique. Using name of the extension or the
954 output plugin itself is often a good choice.
955 </para>
956 </sect3>
957
958 <sect3 id="logicaldecoding-output-plugin-filter-prepare">
959 <title>Prepare Filter Callback</title>
960
961 <para>
962 The optional <function>filter_prepare_cb</function> callback
963 is called to determine whether data that is part of the current
964 two-phase commit transaction should be considered for decoding
965 at this prepare stage or later as a regular one-phase transaction at
966 <command>COMMIT PREPARED</command> time. To signal that
967 decoding should be skipped, return <literal>true</literal>;
968 <literal>false</literal> otherwise. When the callback is not
969 defined, <literal>false</literal> is assumed (i.e. no filtering, all
970 transactions using two-phase commit are decoded in two phases as well).
971 <programlisting>
972 typedef bool (*LogicalDecodeFilterPrepareCB) (struct LogicalDecodingContext *ctx,
973 TransactionId xid,
974 const char *gid);
975 </programlisting>
976 The <parameter>ctx</parameter> parameter has the same contents as for
977 the other callbacks. The parameters <parameter>xid</parameter>
978 and <parameter>gid</parameter> provide two different ways to identify
979 the transaction. The later <command>COMMIT PREPARED</command> or
980 <command>ROLLBACK PREPARED</command> carries both identifiers,
981 providing an output plugin the choice of what to use.
982 </para>
983 <para>
984 The callback may be invoked multiple times per transaction to decode
985 and must provide the same static answer for a given pair of
986 <parameter>xid</parameter> and <parameter>gid</parameter> every time
987 it is called.
988 </para>
989 </sect3>
990
991 <sect3 id="logicaldecoding-output-plugin-begin-prepare">
992 <title>Transaction Begin Prepare Callback</title>
993
994 <para>
995 The required <function>begin_prepare_cb</function> callback is called
996 whenever the start of a prepared transaction has been decoded. The
997 <parameter>gid</parameter> field, which is part of the
998 <parameter>txn</parameter> parameter, can be used in this callback to
999 check if the plugin has already received this <command>PREPARE</command>
1000 in which case it can either error out or skip the remaining changes of
1001 the transaction.
1002 <programlisting>
1003 typedef void (*LogicalDecodeBeginPrepareCB) (struct LogicalDecodingContext *ctx,
1004 ReorderBufferTXN *txn);
1005 </programlisting>
1006 </para>
1007 </sect3>
1008
1009 <sect3 id="logicaldecoding-output-plugin-prepare">
1010 <title>Transaction Prepare Callback</title>
1011
1012 <para>
1013 The required <function>prepare_cb</function> callback is called whenever
1014 a transaction which is prepared for two-phase commit has been
1015 decoded. The <function>change_cb</function> callback for all modified
1016 rows will have been called before this, if there have been any modified
1017 rows. The <parameter>gid</parameter> field, which is part of the
1018 <parameter>txn</parameter> parameter, can be used in this callback.
1019 <programlisting>
1020 typedef void (*LogicalDecodePrepareCB) (struct LogicalDecodingContext *ctx,
1021 ReorderBufferTXN *txn,
1022 XLogRecPtr prepare_lsn);
1023 </programlisting>
1024 </para>
1025 </sect3>
1026
1027 <sect3 id="logicaldecoding-output-plugin-commit-prepared">
1028 <title>Transaction Commit Prepared Callback</title>
1029
1030 <para>
1031 The required <function>commit_prepared_cb</function> callback is called
1032 whenever a transaction <command>COMMIT PREPARED</command> has been decoded.
1033 The <parameter>gid</parameter> field, which is part of the
1034 <parameter>txn</parameter> parameter, can be used in this callback.
1035 <programlisting>
1036 typedef void (*LogicalDecodeCommitPreparedCB) (struct LogicalDecodingContext *ctx,
1037 ReorderBufferTXN *txn,
1038 XLogRecPtr commit_lsn);
1039 </programlisting>
1040 </para>
1041 </sect3>
1042
1043 <sect3 id="logicaldecoding-output-plugin-rollback-prepared">
1044 <title>Transaction Rollback Prepared Callback</title>
1045
1046 <para>
1047 The required <function>rollback_prepared_cb</function> callback is called
1048 whenever a transaction <command>ROLLBACK PREPARED</command> has been
1049 decoded. The <parameter>gid</parameter> field, which is part of the
1050 <parameter>txn</parameter> parameter, can be used in this callback. The
1051 parameters <parameter>prepare_end_lsn</parameter> and
1052 <parameter>prepare_time</parameter> can be used to check if the plugin
1053 has received this <command>PREPARE TRANSACTION</command> in which case
1054 it can apply the rollback, otherwise, it can skip the rollback operation. The
1055 <parameter>gid</parameter> alone is not sufficient because the downstream
1056 node can have a prepared transaction with same identifier.
1057 <programlisting>
1058 typedef void (*LogicalDecodeRollbackPreparedCB) (struct LogicalDecodingContext *ctx,
1059 ReorderBufferTXN *txn,
1060 XLogRecPtr prepare_end_lsn,
1061 TimestampTz prepare_time);
1062 </programlisting>
1063 </para>
1064 </sect3>
1065
1066 <sect3 id="logicaldecoding-output-plugin-stream-start">
1067 <title>Stream Start Callback</title>
1068 <para>
1069 The required <function>stream_start_cb</function> callback is called when
1070 opening a block of streamed changes from an in-progress transaction.
1071 <programlisting>
1072 typedef void (*LogicalDecodeStreamStartCB) (struct LogicalDecodingContext *ctx,
1073 ReorderBufferTXN *txn);
1074 </programlisting>
1075 </para>
1076 </sect3>
1077
1078 <sect3 id="logicaldecoding-output-plugin-stream-stop">
1079 <title>Stream Stop Callback</title>
1080 <para>
1081 The required <function>stream_stop_cb</function> callback is called when
1082 closing a block of streamed changes from an in-progress transaction.
1083 <programlisting>
1084 typedef void (*LogicalDecodeStreamStopCB) (struct LogicalDecodingContext *ctx,
1085 ReorderBufferTXN *txn);
1086 </programlisting>
1087 </para>
1088 </sect3>
1089
1090 <sect3 id="logicaldecoding-output-plugin-stream-abort">
1091 <title>Stream Abort Callback</title>
1092 <para>
1093 The required <function>stream_abort_cb</function> callback is called to
1094 abort a previously streamed transaction.
1095 <programlisting>
1096 typedef void (*LogicalDecodeStreamAbortCB) (struct LogicalDecodingContext *ctx,
1097 ReorderBufferTXN *txn,
1098 XLogRecPtr abort_lsn);
1099 </programlisting>
1100 </para>
1101 </sect3>
1102
1103 <sect3 id="logicaldecoding-output-plugin-stream-prepare">
1104 <title>Stream Prepare Callback</title>
1105 <para>
1106 The <function>stream_prepare_cb</function> callback is called to prepare
1107 a previously streamed transaction as part of a two-phase commit. This
1108 callback is required when the output plugin supports both the streaming
1109 of large in-progress transactions and two-phase commits.
1110 <programlisting>
1111 typedef void (*LogicalDecodeStreamPrepareCB) (struct LogicalDecodingContext *ctx,
1112 ReorderBufferTXN *txn,
1113 XLogRecPtr prepare_lsn);
1114 </programlisting>
1115 </para>
1116 </sect3>
1117
1118 <sect3 id="logicaldecoding-output-plugin-stream-commit">
1119 <title>Stream Commit Callback</title>
1120 <para>
1121 The required <function>stream_commit_cb</function> callback is called to
1122 commit a previously streamed transaction.
1123 <programlisting>
1124 typedef void (*LogicalDecodeStreamCommitCB) (struct LogicalDecodingContext *ctx,
1125 ReorderBufferTXN *txn,
1126 XLogRecPtr commit_lsn);
1127 </programlisting>
1128 </para>
1129 </sect3>
1130
1131 <sect3 id="logicaldecoding-output-plugin-stream-change">
1132 <title>Stream Change Callback</title>
1133 <para>
1134 The required <function>stream_change_cb</function> callback is called
1135 when sending a change in a block of streamed changes (demarcated by
1136 <function>stream_start_cb</function> and <function>stream_stop_cb</function> calls).
1137 The actual changes are not displayed as the transaction can abort at a later
1138 point in time and we don't decode changes for aborted transactions.
1139 <programlisting>
1140 typedef void (*LogicalDecodeStreamChangeCB) (struct LogicalDecodingContext *ctx,
1141 ReorderBufferTXN *txn,
1142 Relation relation,
1143 ReorderBufferChange *change);
1144 </programlisting>
1145 </para>
1146 </sect3>
1147
1148 <sect3 id="logicaldecoding-output-plugin-stream-message">
1149 <title>Stream Message Callback</title>
1150 <para>
1151 The optional <function>stream_message_cb</function> callback is called when
1152 sending a generic message in a block of streamed changes (demarcated by
1153 <function>stream_start_cb</function> and <function>stream_stop_cb</function> calls).
1154 The message contents for transactional messages are not displayed as the transaction
1155 can abort at a later point in time and we don't decode changes for aborted
1156 transactions.
1157 <programlisting>
1158 typedef void (*LogicalDecodeStreamMessageCB) (struct LogicalDecodingContext *ctx,
1159 ReorderBufferTXN *txn,
1160 XLogRecPtr message_lsn,
1161 bool transactional,
1162 const char *prefix,
1163 Size message_size,
1164 const char *message);
1165 </programlisting>
1166 </para>
1167 </sect3>
1168
1169 <sect3 id="logicaldecoding-output-plugin-stream-truncate">
1170 <title>Stream Truncate Callback</title>
1171 <para>
1172 The optional <function>stream_truncate_cb</function> callback is called
1173 for a <command>TRUNCATE</command> command in a block of streamed changes
1174 (demarcated by <function>stream_start_cb</function> and
1175 <function>stream_stop_cb</function> calls).
1176 <programlisting>
1177 typedef void (*LogicalDecodeStreamTruncateCB) (struct LogicalDecodingContext *ctx,
1178 ReorderBufferTXN *txn,
1179 int nrelations,
1180 Relation relations[],
1181 ReorderBufferChange *change);
1182 </programlisting>
1183 The parameters are analogous to the <function>stream_change_cb</function>
1184 callback. However, because <command>TRUNCATE</command> actions on
1185 tables connected by foreign keys need to be executed together, this
1186 callback receives an array of relations instead of just a single one.
1187 See the description of the <xref linkend="sql-truncate"/> statement for
1188 details.
1189 </para>
1190 </sect3>
1191
1192 </sect2>
1193
1194 <sect2 id="logicaldecoding-output-plugin-output">
1195 <title>Functions for Producing Output</title>
1196
1197 <para>
1198 To actually produce output, output plugins can write data to
1199 the <literal>StringInfo</literal> output buffer
1200 in <literal>ctx-&gt;out</literal> when inside
1201 the <function>begin_cb</function>, <function>commit_cb</function>,
1202 or <function>change_cb</function> callbacks. Before writing to the output
1203 buffer, <function>OutputPluginPrepareWrite(ctx, last_write)</function> has
1204 to be called, and after finishing writing to the
1205 buffer, <function>OutputPluginWrite(ctx, last_write)</function> has to be
1206 called to perform the write. The <parameter>last_write</parameter>
1207 indicates whether a particular write was the callback's last write.
1208 </para>
1209
1210 <para>
1211 The following example shows how to output data to the consumer of an
1212 output plugin:
1213 <programlisting>
1214 OutputPluginPrepareWrite(ctx, true);
1215 appendStringInfo(ctx->out, "BEGIN %u", txn->xid);
1216 OutputPluginWrite(ctx, true);
1217 </programlisting>
1218 </para>
1219 </sect2>
1220 </sect1>
1221
1222 <sect1 id="logicaldecoding-writer">
1223 <title>Logical Decoding Output Writers</title>
1224
1225 <para>
1226 It is possible to add more output methods for logical decoding.
1227 For details, see
1228 <filename>src/backend/replication/logical/logicalfuncs.c</filename>.
1229 Essentially, three functions need to be provided: one to read WAL, one to
1230 prepare writing output, and one to write the output
1231 (see <xref linkend="logicaldecoding-output-plugin-output"/>).
1232 </para>
1233 </sect1>
1234
1235 <sect1 id="logicaldecoding-synchronous">
1236 <title>Synchronous Replication Support for Logical Decoding</title>
1237 <sect2 id="logicaldecoding-synchronous-overview">
1238 <title>Overview</title>
1239
1240 <para>
1241 Logical decoding can be used to build
1242 <link linkend="synchronous-replication">synchronous
1243 replication</link> solutions with the same user interface as synchronous
1244 replication for <link linkend="streaming-replication">streaming
1245 replication</link>. To do this, the streaming replication interface
1246 (see <xref linkend="logicaldecoding-walsender"/>) must be used to stream out
1247 data. Clients have to send <literal>Standby status update (F)</literal>
1248 (see <xref linkend="protocol-replication"/>) messages, just like streaming
1249 replication clients do.
1250 </para>
1251
1252 <note>
1253 <para>
1254 A synchronous replica receiving changes via logical decoding will work in
1255 the scope of a single database. Since, in contrast to
1256 that, <parameter>synchronous_standby_names</parameter> currently is
1257 server wide, this means this technique will not work properly if more
1258 than one database is actively used.
1259 </para>
1260 </note>
1261 </sect2>
1262
1263 <sect2 id="logicaldecoding-synchronous-caveats">
1264 <title>Caveats</title>
1265
1266 <para>
1267 In synchronous replication setup, a deadlock can happen, if the transaction
1268 has locked [user] catalog tables exclusively. See
1269 <xref linkend="logicaldecoding-capabilities"/> for information on user
1270 catalog tables. This is because logical decoding of transactions can lock
1271 catalog tables to access them. To avoid this users must refrain from taking
1272 an exclusive lock on [user] catalog tables. This can happen in the following
1273 ways:
1274
1275 <itemizedlist>
1276 <listitem>
1277 <para>
1278 Issuing an explicit <command>LOCK</command> on <structname>pg_class</structname>
1279 in a transaction.
1280 </para>
1281 </listitem>
1282
1283 <listitem>
1284 <para>
1285 Perform <command>CLUSTER</command> on <structname>pg_class</structname> in
1286 a transaction.
1287 </para>
1288 </listitem>
1289
1290 <listitem>
1291 <para>
1292 <command>PREPARE TRANSACTION</command> after <command>LOCK</command> command
1293 on <structname>pg_class</structname> and allow logical decoding of two-phase
1294 transactions.
1295 </para>
1296 </listitem>
1297
1298 <listitem>
1299 <para>
1300 <command>PREPARE TRANSACTION</command> after <command>CLUSTER</command>
1301 command on <structname>pg_trigger</structname> and allow logical decoding of
1302 two-phase transactions. This will lead to deadlock only when published table
1303 have a trigger.
1304 </para>
1305 </listitem>
1306
1307 <listitem>
1308 <para>
1309 Executing <command>TRUNCATE</command> on [user] catalog table in a
1310 transaction.
1311 </para>
1312 </listitem>
1313 </itemizedlist>
1314
1315 Note that these commands can cause deadlocks not only for the system
1316 catalog tables listed above but for other catalog tables.
1317 </para>
1318 </sect2>
1319 </sect1>
1320
1321 <sect1 id="logicaldecoding-streaming">
1322 <title>Streaming of Large Transactions for Logical Decoding</title>
1323
1324 <para>
1325 The basic output plugin callbacks (e.g., <function>begin_cb</function>,
1326 <function>change_cb</function>, <function>commit_cb</function> and
1327 <function>message_cb</function>) are only invoked when the transaction
1328 actually commits. The changes are still decoded from the transaction
1329 log, but are only passed to the output plugin at commit (and discarded
1330 if the transaction aborts).
1331 </para>
1332
1333 <para>
1334 This means that while the decoding happens incrementally, and may spill
1335 to disk to keep memory usage under control, all the decoded changes have
1336 to be transmitted when the transaction finally commits (or more precisely,
1337 when the commit is decoded from the transaction log). Depending on the
1338 size of the transaction and network bandwidth, the transfer time may
1339 significantly increase the apply lag.
1340 </para>
1341
1342 <para>
1343 To reduce the apply lag caused by large transactions, an output plugin
1344 may provide additional callback to support incremental streaming of
1345 in-progress transactions. There are multiple required streaming callbacks
1346 (<function>stream_start_cb</function>, <function>stream_stop_cb</function>,
1347 <function>stream_abort_cb</function>, <function>stream_commit_cb</function>
1348 and <function>stream_change_cb</function>) and two optional callbacks
1349 (<function>stream_message_cb</function> and <function>stream_truncate_cb</function>).
1350 Also, if streaming of two-phase commands is to be supported, then additional
1351 callbacks must be provided. (See <xref linkend="logicaldecoding-two-phase-commits"/>
1352 for details).
1353 </para>
1354
1355 <para>
1356 When streaming an in-progress transaction, the changes (and messages) are
1357 streamed in blocks demarcated by <function>stream_start_cb</function>
1358 and <function>stream_stop_cb</function> callbacks. Once all the decoded
1359 changes are transmitted, the transaction can be committed using the
1360 <function>stream_commit_cb</function> callback
1361 (or possibly aborted using the <function>stream_abort_cb</function> callback).
1362 If two-phase commits are supported, the transaction can be prepared using the
1363 <function>stream_prepare_cb</function> callback,
1364 <command>COMMIT PREPARED</command> using the
1365 <function>commit_prepared_cb</function> callback or aborted using the
1366 <function>rollback_prepared_cb</function>.
1367 </para>
1368
1369 <para>
1370 One example sequence of streaming callback calls for one transaction may
1371 look like this:
1372 <programlisting>
1373 stream_start_cb(...); &lt;-- start of first block of changes
1374 stream_change_cb(...);
1375 stream_change_cb(...);
1376 stream_message_cb(...);
1377 stream_change_cb(...);
1378 ...
1379 stream_change_cb(...);
1380 stream_stop_cb(...); &lt;-- end of first block of changes
1381
1382 stream_start_cb(...); &lt;-- start of second block of changes
1383 stream_change_cb(...);
1384 stream_change_cb(...);
1385 stream_change_cb(...);
1386 ...
1387 stream_message_cb(...);
1388 stream_change_cb(...);
1389 stream_stop_cb(...); &lt;-- end of second block of changes
1390
1391
1392 [a. when using normal commit]
1393 stream_commit_cb(...); &lt;-- commit of the streamed transaction
1394
1395 [b. when using two-phase commit]
1396 stream_prepare_cb(...); &lt;-- prepare the streamed transaction
1397 commit_prepared_cb(...); &lt;-- commit of the prepared transaction
1398 </programlisting>
1399 </para>
1400
1401 <para>
1402 The actual sequence of callback calls may be more complicated, of course.
1403 There may be blocks for multiple streamed transactions, some of the
1404 transactions may get aborted, etc.
1405 </para>
1406
1407 <para>
1408 Similar to spill-to-disk behavior, streaming is triggered when the total
1409 amount of changes decoded from the WAL (for all in-progress transactions)
1410 exceeds the limit defined by <varname>logical_decoding_work_mem</varname> setting.
1411 At that point, the largest top-level transaction (measured by the amount of memory
1412 currently used for decoded changes) is selected and streamed. However, in
1413 some cases we still have to spill to disk even if streaming is enabled
1414 because we exceed the memory threshold but still have not decoded the
1415 complete tuple e.g., only decoded toast table insert but not the main table
1416 insert.
1417 </para>
1418
1419 <para>
1420 Even when streaming large transactions, the changes are still applied in
1421 commit order, preserving the same guarantees as the non-streaming mode.
1422 </para>
1423
1424 </sect1>
1425
1426 <sect1 id="logicaldecoding-two-phase-commits">
1427 <title>Two-phase Commit Support for Logical Decoding</title>
1428
1429 <para>
1430 With the basic output plugin callbacks (eg., <function>begin_cb</function>,
1431 <function>change_cb</function>, <function>commit_cb</function> and
1432 <function>message_cb</function>) two-phase commit commands like
1433 <command>PREPARE TRANSACTION</command>, <command>COMMIT PREPARED</command>
1434 and <command>ROLLBACK PREPARED</command> are not decoded. While the
1435 <command>PREPARE TRANSACTION</command> is ignored,
1436 <command>COMMIT PREPARED</command> is decoded as a <command>COMMIT</command>
1437 and <command>ROLLBACK PREPARED</command> is decoded as a
1438 <command>ROLLBACK</command>.
1439 </para>
1440
1441 <para>
1442 To support the streaming of two-phase commands, an output plugin needs to
1443 provide additional callbacks. There are multiple two-phase commit callbacks
1444 that are required, (<function>begin_prepare_cb</function>,
1445 <function>prepare_cb</function>, <function>commit_prepared_cb</function>,
1446 <function>rollback_prepared_cb</function> and
1447 <function>stream_prepare_cb</function>) and an optional callback
1448 (<function>filter_prepare_cb</function>).
1449 </para>
1450
1451 <para>
1452 If the output plugin callbacks for decoding two-phase commit commands are
1453 provided, then on <command>PREPARE TRANSACTION</command>, the changes of
1454 that transaction are decoded, passed to the output plugin, and the
1455 <function>prepare_cb</function> callback is invoked. This differs from the
1456 basic decoding setup where changes are only passed to the output plugin
1457 when a transaction is committed. The start of a prepared transaction is
1458 indicated by the <function>begin_prepare_cb</function> callback.
1459 </para>
1460
1461 <para>
1462 When a prepared transaction is rolled back using the
1463 <command>ROLLBACK PREPARED</command>, then the
1464 <function>rollback_prepared_cb</function> callback is invoked and when the
1465 prepared transaction is committed using <command>COMMIT PREPARED</command>,
1466 then the <function>commit_prepared_cb</function> callback is invoked.
1467 </para>
1468
1469 <para>
1470 Optionally the output plugin can define filtering rules via
1471 <function>filter_prepare_cb</function> to decode only specific transaction
1472 in two phases. This can be achieved by pattern matching on the
1473 <parameter>gid</parameter> or via lookups using the
1474 <parameter>xid</parameter>.
1475 </para>
1476
1477 <para>
1478 The users that want to decode prepared transactions need to be careful about
1479 below mentioned points:
1480
1481 <itemizedlist>
1482 <listitem>
1483 <para>
1484 If the prepared transaction has locked [user] catalog tables exclusively
1485 then decoding prepare can block till the main transaction is committed.
1486 </para>
1487 </listitem>
1488
1489 <listitem>
1490 <para>
1491 The logical replication solution that builds distributed two phase commit
1492 using this feature can deadlock if the prepared transaction has locked
1493 [user] catalog tables exclusively. To avoid this users must refrain from
1494 having locks on catalog tables (e.g. explicit <command>LOCK</command> command)
1495 in such transactions.
1496 See <xref linkend="logicaldecoding-synchronous-caveats"/> for the details.
1497 </para>
1498 </listitem>
1499 </itemizedlist>
1500 </para>
1501
1502 </sect1>
1503 </chapter>