<!-- doc/src/sgml/logicaldecoding.sgml -->
 <chapter id="logicaldecoding">
  <title>Logical Decoding</title>
  <indexterm zone="logicaldecoding">
   <primary>Logical Decoding</primary>
  </indexterm>
  <para>
   PostgreSQL provides infrastructure to stream the modifications performed
   via SQL to external consumers.  This functionality can be used for a
   variety of purposes, including replication solutions and auditing.
  </para>

  <para>
   Changes are sent out in streams identified by logical replication slots.
  </para>

  <para>
   The format in which those changes are streamed is determined by the output
   plugin used.  An example plugin is provided in the PostgreSQL distribution.
   Additional plugins can be
   written to extend the choice of available formats without modifying any
   core code.
   Every output plugin has access to each individual new row produced
   by <command>INSERT</command> and the new row version created
   by <command>UPDATE</command>.  Availability of old row versions for
   <command>UPDATE</command> and <command>DELETE</command> depends on
   the configured replica identity (see <xref linkend="sql-altertable-replica-identity"/>).
  </para>

  <para>
   Changes can be consumed either using the streaming replication protocol
   (see <xref linkend="protocol-replication"/> and
   <xref linkend="logicaldecoding-walsender"/>), or by calling functions
   via SQL (see <xref linkend="logicaldecoding-sql"/>). It is also possible
   to write additional methods of consuming the output of a replication slot
   without modifying core code
   (see <xref linkend="logicaldecoding-writer"/>).
  </para>

  <sect1 id="logicaldecoding-example">
   <title>Logical Decoding Examples</title>

   <para>
    The following example demonstrates controlling logical decoding using the
    SQL interface.
   </para>

   <para>
    Before you can use logical decoding, you must set
    <xref linkend="guc-wal-level"/> to <literal>logical</literal> and
    <xref linkend="guc-max-replication-slots"/> to at least 1.  Then, you
    should connect to the target database (in the example
    below, <literal>postgres</literal>) as a superuser.
   </para>

<programlisting>
postgres=# -- Create a slot named 'regression_slot' using the output plugin 'test_decoding'
postgres=# SELECT * FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding', false, true);
    slot_name    |    lsn
-----------------+-----------
 regression_slot | 0/16B1970
(1 row)

postgres=# SELECT slot_name, plugin, slot_type, database, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots;
    slot_name    |    plugin     | slot_type | database | active | restart_lsn | confirmed_flush_lsn
-----------------+---------------+-----------+----------+--------+-------------+-----------------
 regression_slot | test_decoding | logical   | postgres | f      | 0/16A4408   | 0/16A4440
(1 row)

postgres=# -- There are no changes to see yet
postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
 lsn | xid | data
-----+-----+------
(0 rows)

postgres=# CREATE TABLE data(id serial primary key, data text);
CREATE TABLE

postgres=# -- DDL isn't replicated, so all you'll see is the transaction
postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
    lsn    |  xid  |     data
-----------+-------+--------------
 0/BA2DA58 | 10297 | BEGIN 10297
 0/BA5A5A0 | 10297 | COMMIT 10297
(2 rows)

postgres=# -- Once changes are read, they're consumed and not emitted
postgres=# -- in a subsequent call:
postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
 lsn | xid | data
-----+-----+------
(0 rows)

postgres=# BEGIN;
postgres=*# INSERT INTO data(data) VALUES('1');
postgres=*# INSERT INTO data(data) VALUES('2');
postgres=*# COMMIT;

postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
    lsn    |  xid  |                          data
-----------+-------+---------------------------------------------------------