1 <!-- doc/src/sgml/logicaldecoding.sgml -->
2 <chapter id="logicaldecoding">
3 <title>Logical Decoding</title>
4 <indexterm zone="logicaldecoding">
5 <primary>Logical Decoding</primary>
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.
14 Changes are sent out in streams identified by logical replication slots.
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
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-CREATETABLE-REPLICA-IDENTITY">).
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">).
40 <sect1 id="logicaldecoding-example">
41 <title>Logical Decoding Examples</title>
44 The following example demonstrates controlling logical decoding using the
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.
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');
60 -----------------+-----------
61 regression_slot | 0/16B1970
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
70 postgres=# -- There are no changes to see yet
71 postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
76 postgres=# CREATE TABLE data(id serial primary key, data text);
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);
82 -----------+-------+--------------
83 0/BA2DA58 | 10297 | BEGIN 10297
84 0/BA5A5A0 | 10297 | COMMIT 10297
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);
95 postgres=# INSERT INTO data(data) VALUES('1');
96 postgres=# INSERT INTO data(data) VALUES('2');
99 postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
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
108 postgres=# INSERT INTO data(data) VALUES('3');
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);
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
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);
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
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');
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)
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 -----------------------
147 The following example shows 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.
157 $ pg_recvlogical -d postgres --slot test --create-slot
158 $ pg_recvlogical -d postgres --slot test --start -f -
159 <keycombo action="simul"><keycap>Control</><keycap>Z</></>
160 $ psql -d postgres -c "INSERT INTO data(data) VALUES('4');"
163 table public.data: INSERT: id[integer]:4 data[text]:'4'
165 <keycombo action="simul"><keycap>Control</><keycap>C</></>
166 $ pg_recvlogical -d postgres --slot test --drop-slot
170 <sect1 id="logicaldecoding-explanation">
171 <title>Logical Decoding Concepts</title>
173 <title>Logical Decoding</title>
176 <primary>Logical Decoding</primary>
180 Logical decoding is the process of extracting all persistent changes
181 to a database's tables into a coherent, easy to understand format which
182 can be interpreted without detailed knowledge of the database's internal
187 In <productname>PostgreSQL</productname>, logical decoding is implemented
188 by decoding the contents of the <link linkend="wal">write-ahead
189 log</link>, which describe changes on a storage level, into an
190 application-specific form such as a stream of tuples or SQL statements.
194 <sect2 id="logicaldecoding-replication-slots">
195 <title>Replication Slots</title>
198 <primary>replication slot</primary>
199 <secondary>logical replication</secondary>
203 In the context of logical replication, a slot represents a stream of
204 changes that can be replayed to a client in the order they were made on
205 the origin server. Each slot streams a sequence of changes from a single
210 <para><productname>PostgreSQL</productname> also has streaming replication slots
211 (see <xref linkend="streaming-replication">), but they are used somewhat
217 A replication slot has an identifier that is unique across all databases
218 in a <productname>PostgreSQL</productname> cluster. Slots persist
219 independently of the connection using them and are crash-safe.
223 A logical slot will emit each change just once in normal operation.
224 The current position of each slot is persisted only at checkpoint, so in
225 the case of a crash the slot may return to an earlier LSN, which will
226 then cause recent changes to be resent when the server restarts.
227 Logical decoding clients are responsible for avoiding ill effects from
228 handling the same message more than once. Clients may wish to record
229 the last LSN they saw when decoding and skip over any repeated data or
230 (when using the replication protocol) request that decoding start from
231 that LSN rather than letting the server determine the start point.
232 The Replication Progress Tracking feature is designed for this purpose,
233 refer to <link linkend="replication-origins">replication origins</link>.
237 Multiple independent slots may exist for a single database. Each slot has
238 its own state, allowing different consumers to receive changes from
239 different points in the database change stream. For most applications, a
240 separate slot will be required for each consumer.
244 A logical replication slot knows nothing about the state of the
245 receiver(s). It's even possible to have multiple different receivers using
246 the same slot at different times; they'll just get the changes following
247 on from when the last receiver stopped consuming them. Only one receiver
248 may consume changes from a slot at any given time.
253 Replication slots persist across crashes and know nothing about the state
254 of their consumer(s). They will prevent removal of required resources
255 even when there is no connection using them. This consumes storage
256 because neither required WAL nor required rows from the system catalogs
257 can be removed by <command>VACUUM</command> as long as they are required by a replication
258 slot. So if a slot is no longer required it should be dropped.
264 <title>Output Plugins</title>
266 Output plugins transform the data from the write-ahead log's internal
267 representation into the format the consumer of a replication slot desires.
272 <title>Exported Snapshots</title>
274 When a new replication slot is created using the streaming replication
275 interface (see <xref linkend="protocol-replication-create-slot">), a
277 (see <xref linkend="functions-snapshot-synchronization">), which will show
278 exactly the state of the database after which all changes will be
279 included in the change stream. This can be used to create a new replica by
280 using <link linkend="sql-set-transaction"><literal>SET TRANSACTION
281 SNAPSHOT</literal></link> to read the state of the database at the moment
282 the slot was created. This transaction can then be used to dump the
283 database's state at that point in time, which afterwards can be updated
284 using the slot's contents without losing any changes.
287 Creation of a snapshot is not always possible. In particular, it will
288 fail when connected to a hot standby. Applications that do not require
289 snapshot export may suppress it with the <literal>NOEXPORT_SNAPSHOT</>
295 <sect1 id="logicaldecoding-walsender">
296 <title>Streaming Replication Protocol Interface</title>
302 <para><literal>CREATE_REPLICATION_SLOT <replaceable>slot_name</replaceable> LOGICAL <replaceable>output_plugin</replaceable></literal></para>
306 <para><literal>DROP_REPLICATION_SLOT <replaceable>slot_name</replaceable></literal></para>
310 <para><literal>START_REPLICATION SLOT <replaceable>slot_name</replaceable> LOGICAL ...</literal></para>
313 are used to create, drop, and stream changes from a replication
314 slot, respectively. These commands are only available over a replication
315 connection; they cannot be used via SQL.
316 See <xref linkend="protocol-replication"> for details on these commands.
320 The command <xref linkend="app-pgrecvlogical"> can be used to control
321 logical decoding over a streaming replication connection. (It uses
322 these commands internally.)
326 <sect1 id="logicaldecoding-sql">
327 <title>Logical Decoding <acronym>SQL</acronym> Interface</title>
330 See <xref linkend="functions-replication"> for detailed documentation on
331 the SQL-level API for interacting with logical decoding.
335 Synchronous replication (see <xref linkend="synchronous-replication">) is
336 only supported on replication slots used over the streaming replication interface. The
337 function interface and additional, non-core interfaces do not support
338 synchronous replication.
342 <sect1 id="logicaldecoding-catalogs">
343 <title>System Catalogs Related to Logical Decoding</title>
346 The <link linkend="view-pg-replication-slots"><structname>pg_replication_slots</structname></link>
348 <link linkend="monitoring-stats-views-table"><structname>pg_stat_replication</structname></link>
349 view provide information about the current state of replication slots and
350 streaming replication connections respectively. These views apply to both physical and
355 <sect1 id="logicaldecoding-output-plugin">
356 <title>Logical Decoding Output Plugins</title>
358 An example output plugin can be found in the
359 <link linkend="test-decoding">
360 <filename>contrib/test_decoding</filename>
362 subdirectory of the PostgreSQL source tree.
364 <sect2 id="logicaldecoding-output-init">
365 <title>Initialization Function</title>
366 <indexterm zone="logicaldecoding">
367 <primary>_PG_output_plugin_init</primary>
370 An output plugin is loaded by dynamically loading a shared library with
371 the output plugin's name as the library base name. The normal library
372 search path is used to locate the library. To provide the required output
373 plugin callbacks and to indicate that the library is actually an output
374 plugin it needs to provide a function named
375 <function>_PG_output_plugin_init</function>. This function is passed a
376 struct that needs to be filled with the callback function pointers for
379 typedef struct OutputPluginCallbacks
381 LogicalDecodeStartupCB startup_cb;
382 LogicalDecodeBeginCB begin_cb;
383 LogicalDecodeChangeCB change_cb;
384 LogicalDecodeCommitCB commit_cb;
385 LogicalDecodeMessageCB message_cb;
386 LogicalDecodeFilterByOriginCB filter_by_origin_cb;
387 LogicalDecodeShutdownCB shutdown_cb;
388 } OutputPluginCallbacks;
390 typedef void (*LogicalOutputPluginInit) (struct OutputPluginCallbacks *cb);
392 The <function>begin_cb</function>, <function>change_cb</function>
393 and <function>commit_cb</function> callbacks are required,
394 while <function>startup_cb</function>,
395 <function>filter_by_origin_cb</function>
396 and <function>shutdown_cb</function> are optional.
400 <sect2 id="logicaldecoding-capabilities">
401 <title>Capabilities</title>
404 To decode, format and output changes, output plugins can use most of the
405 backend's normal infrastructure, including calling output functions. Read
406 only access to relations is permitted as long as only relations are
407 accessed that either have been created by <command>initdb</command> in
408 the <literal>pg_catalog</literal> schema, or have been marked as user
409 provided catalog tables using
411 ALTER TABLE user_catalog_table SET (user_catalog_table = true);
412 CREATE TABLE another_catalog_table(data text) WITH (user_catalog_table = true);
414 Any actions leading to transaction ID assignment are prohibited. That, among others,
415 includes writing to tables, performing DDL changes, and
416 calling <literal>txid_current()</literal>.
420 <sect2 id="logicaldecoding-output-mode">
421 <title>Output Modes</title>
424 Output plugin callbacks can pass data to the consumer in nearly arbitrary
425 formats. For some use cases, like viewing the changes via SQL, returning
426 data in a data type that can contain arbitrary data (e.g., <type>bytea</type>) is
427 cumbersome. If the output plugin only outputs textual data in the
428 server's encoding, it can declare that by
429 setting <literal>OutputPluginOptions.output_type</>
430 to <literal>OUTPUT_PLUGIN_TEXTUAL_OUTPUT</> instead
431 of <literal>OUTPUT_PLUGIN_BINARY_OUTPUT</> in
432 the <link linkend="logicaldecoding-output-plugin-startup">startup
433 callback</>. In that case, all the data has to be in the server's encoding
434 so that a <type>text</> datum can contain it. This is checked in assertion-enabled
439 <sect2 id="logicaldecoding-output-plugin-callbacks">
440 <title>Output Plugin Callbacks</title>
443 An output plugin gets notified about changes that are happening via
444 various callbacks it needs to provide.
448 Concurrent transactions are decoded in commit order, and only changes
449 belonging to a specific transaction are decoded between
450 the <literal>begin</literal> and <literal>commit</literal>
451 callbacks. Transactions that were rolled back explicitly or implicitly
453 decoded. Successful savepoints are
454 folded into the transaction containing them in the order they were
455 executed within that transaction.
460 Only transactions that have already safely been flushed to disk will be
461 decoded. That can lead to a <command>COMMIT</command> not immediately being decoded in a
462 directly following <literal>pg_logical_slot_get_changes()</literal>
463 when <varname>synchronous_commit</varname> is set
464 to <literal>off</literal>.
468 <sect3 id="logicaldecoding-output-plugin-startup">
469 <title>Startup Callback</title>
471 The optional <function>startup_cb</function> callback is called whenever
472 a replication slot is created or asked to stream changes, independent
473 of the number of changes that are ready to be put out.
475 typedef void (*LogicalDecodeStartupCB) (struct LogicalDecodingContext *ctx,
476 OutputPluginOptions *options,
479 The <literal>is_init</literal> parameter will be true when the
480 replication slot is being created and false
481 otherwise. <parameter>options</parameter> points to a struct of options
482 that output plugins can set:
484 typedef struct OutputPluginOptions
486 OutputPluginOutputType output_type;
487 } OutputPluginOptions;
489 <literal>output_type</literal> has to either be set to
490 <literal>OUTPUT_PLUGIN_TEXTUAL_OUTPUT</literal>
491 or <literal>OUTPUT_PLUGIN_BINARY_OUTPUT</literal>. See also
492 <xref linkend="logicaldecoding-output-mode">.
496 The startup callback should validate the options present in
497 <literal>ctx->output_plugin_options</literal>. If the output plugin
498 needs to have a state, it can
499 use <literal>ctx->output_plugin_private</literal> to store it.
503 <sect3 id="logicaldecoding-output-plugin-shutdown">
504 <title>Shutdown Callback</title>
507 The optional <function>shutdown_cb</function> callback is called
508 whenever a formerly active replication slot is not used anymore and can
509 be used to deallocate resources private to the output plugin. The slot
510 isn't necessarily being dropped, streaming is just being stopped.
512 typedef void (*LogicalDecodeShutdownCB) (struct LogicalDecodingContext *ctx);
517 <sect3 id="logicaldecoding-output-plugin-begin">
518 <title>Transaction Begin Callback</title>
521 The required <function>begin_cb</function> callback is called whenever a
522 start of a committed transaction has been decoded. Aborted transactions
523 and their contents never get decoded.
525 typedef void (*LogicalDecodeBeginCB) (struct LogicalDecodingContext *ctx,
526 ReorderBufferTXN *txn);
528 The <parameter>txn</parameter> parameter contains meta information about
529 the transaction, like the time stamp at which it has been committed and
534 <sect3 id="logicaldecoding-output-plugin-commit">
535 <title>Transaction End Callback</title>
538 The required <function>commit_cb</function> callback is called whenever
539 a transaction commit has been
540 decoded. The <function>change_cb</function> callbacks for all modified
541 rows will have been called before this, if there have been any modified
544 typedef void (*LogicalDecodeCommitCB) (struct LogicalDecodingContext *ctx,
545 ReorderBufferTXN *txn,
546 XLogRecPtr commit_lsn);
551 <sect3 id="logicaldecoding-output-plugin-change">
552 <title>Change Callback</title>
555 The required <function>change_cb</function> callback is called for every
556 individual row modification inside a transaction, may it be
557 an <command>INSERT</command>, <command>UPDATE</command>,
558 or <command>DELETE</command>. Even if the original command modified
559 several rows at once the callback will be called individually for each
562 typedef void (*LogicalDecodeChangeCB) (struct LogicalDecodingContext *ctx,
563 ReorderBufferTXN *txn,
565 ReorderBufferChange *change);
567 The <parameter>ctx</parameter> and <parameter>txn</parameter> parameters
568 have the same contents as for the <function>begin_cb</function>
569 and <function>commit_cb</function> callbacks, but additionally the
570 relation descriptor <parameter>relation</parameter> points to the
571 relation the row belongs to and a struct
572 <parameter>change</parameter> describing the row modification are passed
578 Only changes in user defined tables that are not unlogged
579 (see <xref linkend="SQL-CREATETABLE-UNLOGGED">) and not temporary
580 (see <xref linkend="SQL-CREATETABLE-TEMPORARY">) can be extracted using
586 <sect3 id="logicaldecoding-output-plugin-filter-origin">
587 <title>Origin Filter Callback</title>
590 The optional <function>filter_by_origin_cb</function> callback
591 is called to determine whether data that has been replayed
592 from <parameter>origin_id</parameter> is of interest to the
595 typedef bool (*LogicalDecodeFilterByOriginCB) (struct LogicalDecodingContext *ctx,
596 RepOriginId origin_id);
598 The <parameter>ctx</parameter> parameter has the same contents
599 as for the other callbacks. No information but the origin is
600 available. To signal that changes originating on the passed in
601 node are irrelevant, return true, causing them to be filtered
602 away; false otherwise. The other callbacks will not be called
603 for transactions and changes that have been filtered away.
606 This is useful when implementing cascading or multidirectional
607 replication solutions. Filtering by the origin allows to
608 prevent replicating the same changes back and forth in such
609 setups. While transactions and changes also carry information
610 about the origin, filtering via this callback is noticeably
615 <sect3 id="logicaldecoding-output-plugin-message">
616 <title>Generic Message Callback</title>
619 The optional <function>message_cb</function> callback is called whenever
620 a logical decoding message has been decoded.
622 typedef void (*LogicalDecodeMessageCB) (struct LogicalDecodingContext *ctx,
623 ReorderBufferTXN *txn,
624 XLogRecPtr message_lsn,
628 const char *message);
630 The <parameter>txn</parameter> parameter contains meta information about
631 the transaction, like the time stamp at which it has been committed and
632 its XID. Note however that it can be NULL when the message is
633 non-transactional and the XID was not assigned yet in the transaction
634 which logged the message. The <parameter>lsn</parameter> has WAL
635 location of the message. The <parameter>transactional</parameter> says
636 if the message was sent as transactional or not.
637 The <parameter>prefix</parameter> is arbitrary null-terminated prefix
638 which can be used for identifying interesting messages for the current
639 plugin. And finally the <parameter>message</parameter> parameter holds
640 the actual message of <parameter>message_size</parameter> size.
643 Extra care should be taken to ensure that the prefix the output plugin
644 considers interesting is unique. Using name of the extension or the
645 output plugin itself is often a good choice.
651 <sect2 id="logicaldecoding-output-plugin-output">
652 <title>Functions for Producing Output</title>
655 To actually produce output, output plugins can write data to
656 the <literal>StringInfo</literal> output buffer
657 in <literal>ctx->out</literal> when inside
658 the <function>begin_cb</function>, <function>commit_cb</function>,
659 or <function>change_cb</function> callbacks. Before writing to the output
660 buffer, <function>OutputPluginPrepareWrite(ctx, last_write)</function> has
661 to be called, and after finishing writing to the
662 buffer, <function>OutputPluginWrite(ctx, last_write)</function> has to be
663 called to perform the write. The <parameter>last_write</parameter>
664 indicates whether a particular write was the callback's last write.
668 The following example shows how to output data to the consumer of an
671 OutputPluginPrepareWrite(ctx, true);
672 appendStringInfo(ctx->out, "BEGIN %u", txn->xid);
673 OutputPluginWrite(ctx, true);
679 <sect1 id="logicaldecoding-writer">
680 <title>Logical Decoding Output Writers</title>
683 It is possible to add more output methods for logical decoding.
685 <filename>src/backend/replication/logical/logicalfuncs.c</filename>.
686 Essentially, three functions need to be provided: one to read WAL, one to
687 prepare writing output, and one to write the output
688 (see <xref linkend="logicaldecoding-output-plugin-output">).
692 <sect1 id="logicaldecoding-synchronous">
693 <title>Synchronous Replication Support for Logical Decoding</title>
696 Logical decoding can be used to build
697 <link linkend="synchronous-replication">synchronous
698 replication</link> solutions with the same user interface as synchronous
699 replication for <link linkend="streaming-replication">streaming
700 replication</link>. To do this, the streaming replication interface
701 (see <xref linkend="logicaldecoding-walsender">) must be used to stream out
702 data. Clients have to send <literal>Standby status update (F)</literal>
703 (see <xref linkend="protocol-replication">) messages, just like streaming
704 replication clients do.
709 A synchronous replica receiving changes via logical decoding will work in
710 the scope of a single database. Since, in contrast to
711 that, <parameter>synchronous_standby_names</parameter> currently is
712 server wide, this means this technique will not work properly if more
713 than one database is actively used.