| 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->output_plugin_options</literal>. If the output plugin |
| 774 |
needs to have a state, it can |
| 775 |
use <literal>ctx->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->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(...); <-- 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(...); <-- end of first block of changes |
| 1381 |
|
| 1382 |
stream_start_cb(...); <-- 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(...); <-- end of second block of changes |
| 1390 |
|
| 1391 |
|
| 1392 |
[a. when using normal commit] |
| 1393 |
stream_commit_cb(...); <-- commit of the streamed transaction |
| 1394 |
|
| 1395 |
[b. when using two-phase commit] |
| 1396 |
stream_prepare_cb(...); <-- prepare the streamed transaction |
| 1397 |
commit_prepared_cb(...); <-- 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> |