begriffs open source - ai-pg/blob - full-docs/html/logicaldecoding-example.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>47.1. Logical Decoding Examples</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="logicaldecoding.html" title="Chapter 47. Logical Decoding" /><link rel="next" href="logicaldecoding-explanation.html" title="47.2. Logical Decoding Concepts" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">47.1. Logical Decoding Examples</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="logicaldecoding.html" title="Chapter 47. Logical Decoding">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="logicaldecoding.html" title="Chapter 47. Logical Decoding">Up</a></td><th width="60%" align="center">Chapter 47. Logical Decoding</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="logicaldecoding-explanation.html" title="47.2. Logical Decoding Concepts">Next</a></td></tr></table><hr /></div><div class="sect1" id="LOGICALDECODING-EXAMPLE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">47.1. Logical Decoding Examples <a href="#LOGICALDECODING-EXAMPLE" class="id_link">#</a></h2></div></div></div><p>
   3     The following example demonstrates controlling logical decoding using the
   4     SQL interface.
   5    </p><p>
   6     Before you can use logical decoding, you must set
   7     <a class="xref" href="runtime-config-wal.html#GUC-WAL-LEVEL">wal_level</a> to <code class="literal">logical</code> and
   8     <a class="xref" href="runtime-config-replication.html#GUC-MAX-REPLICATION-SLOTS">max_replication_slots</a> to at least 1.  Then, you
   9     should connect to the target database (in the example
  10     below, <code class="literal">postgres</code>) as a superuser.
  11    </p><pre class="programlisting">
  12 postgres=# -- Create a slot named 'regression_slot' using the output plugin 'test_decoding'
  13 postgres=# SELECT * FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding', false, true);
  14     slot_name    |    lsn
  15 -----------------+-----------
  16  regression_slot | 0/16B1970
  17 (1 row)
  18
  19 postgres=# SELECT slot_name, plugin, slot_type, database, active, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots;
  20     slot_name    |    plugin     | slot_type | database | active | restart_lsn | confirmed_flush_lsn
  21 -----------------+---------------+-----------+----------+--------+-------------+-----------------
  22  regression_slot | test_decoding | logical   | postgres | f      | 0/16A4408   | 0/16A4440
  23 (1 row)
  24
  25 postgres=# -- There are no changes to see yet
  26 postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
  27  lsn | xid | data
  28 -----+-----+------
  29 (0 rows)
  30
  31 postgres=# CREATE TABLE data(id serial primary key, data text);
  32 CREATE TABLE
  33
  34 postgres=# -- DDL isn't replicated, so all you'll see is the transaction
  35 postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
  36     lsn    |  xid  |     data
  37 -----------+-------+--------------
  38  0/BA2DA58 | 10297 | BEGIN 10297
  39  0/BA5A5A0 | 10297 | COMMIT 10297
  40 (2 rows)
  41
  42 postgres=# -- Once changes are read, they're consumed and not emitted
  43 postgres=# -- in a subsequent call:
  44 postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
  45  lsn | xid | data
  46 -----+-----+------
  47 (0 rows)
  48
  49 postgres=# BEGIN;
  50 postgres=*# INSERT INTO data(data) VALUES('1');
  51 postgres=*# INSERT INTO data(data) VALUES('2');
  52 postgres=*# COMMIT;
  53
  54 postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
  55     lsn    |  xid  |                          data
  56 -----------+-------+---------------------------------------------------------
  57  0/BA5A688 | 10298 | BEGIN 10298
  58  0/BA5A6F0 | 10298 | table public.data: INSERT: id[integer]:1 data[text]:'1'
  59  0/BA5A7F8 | 10298 | table public.data: INSERT: id[integer]:2 data[text]:'2'
  60  0/BA5A8A8 | 10298 | COMMIT 10298
  61 (4 rows)
  62
  63 postgres=# INSERT INTO data(data) VALUES('3');
  64
  65 postgres=# -- You can also peek ahead in the change stream without consuming changes
  66 postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL);
  67     lsn    |  xid  |                          data
  68 -----------+-------+---------------------------------------------------------
  69  0/BA5A8E0 | 10299 | BEGIN 10299
  70  0/BA5A8E0 | 10299 | table public.data: INSERT: id[integer]:3 data[text]:'3'
  71  0/BA5A990 | 10299 | COMMIT 10299
  72 (3 rows)
  73
  74 postgres=# -- The next call to pg_logical_slot_peek_changes() returns the same changes again
  75 postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL);
  76     lsn    |  xid  |                          data
  77 -----------+-------+---------------------------------------------------------
  78  0/BA5A8E0 | 10299 | BEGIN 10299
  79  0/BA5A8E0 | 10299 | table public.data: INSERT: id[integer]:3 data[text]:'3'
  80  0/BA5A990 | 10299 | COMMIT 10299
  81 (3 rows)
  82
  83 postgres=# -- options can be passed to output plugin, to influence the formatting
  84 postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL, 'include-timestamp', 'on');
  85     lsn    |  xid  |                          data
  86 -----------+-------+---------------------------------------------------------
  87  0/BA5A8E0 | 10299 | BEGIN 10299
  88  0/BA5A8E0 | 10299 | table public.data: INSERT: id[integer]:3 data[text]:'3'
  89  0/BA5A990 | 10299 | COMMIT 10299 (at 2017-05-10 12:07:21.272494-04)
  90 (3 rows)
  91
  92 postgres=# -- Remember to destroy a slot you no longer need to stop it consuming
  93 postgres=# -- server resources:
  94 postgres=# SELECT pg_drop_replication_slot('regression_slot');
  95  pg_drop_replication_slot
  96 -----------------------
  97
  98 (1 row)
  99 </pre><p>
 100     The following examples show how logical decoding is controlled over the
 101     streaming replication protocol, using the
 102     program <a class="xref" href="app-pgrecvlogical.html" title="pg_recvlogical"><span class="refentrytitle"><span class="application">pg_recvlogical</span></span></a> included in the PostgreSQL
 103     distribution.  This requires that client authentication is set up to allow
 104     replication connections
 105     (see <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-AUTHENTICATION" title="26.2.5.1. Authentication">Section 26.2.5.1</a>) and
 106     that <code class="varname">max_wal_senders</code> is set sufficiently high to allow
 107     an additional connection.  The second example shows how to stream two-phase
 108     transactions.  Before you use two-phase commands, you must set
 109     <a class="xref" href="runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS">max_prepared_transactions</a> to at least 1.
 110    </p><pre class="programlisting">
 111 Example 1:
 112 $ pg_recvlogical -d postgres --slot=test --create-slot
 113 $ pg_recvlogical -d postgres --slot=test --start -f -
 114 <span class="keycap"><strong>Control</strong></span>+<span class="keycap"><strong>Z</strong></span>
 115 $ psql -d postgres -c "INSERT INTO data(data) VALUES('4');"
 116 $ fg
 117 BEGIN 693
 118 table public.data: INSERT: id[integer]:4 data[text]:'4'
 119 COMMIT 693
 120 <span class="keycap"><strong>Control</strong></span>+<span class="keycap"><strong>C</strong></span>
 121 $ pg_recvlogical -d postgres --slot=test --drop-slot
 122
 123 Example 2:
 124 $ pg_recvlogical -d postgres --slot=test --create-slot --enable-two-phase
 125 $ pg_recvlogical -d postgres --slot=test --start -f -
 126 <span class="keycap"><strong>Control</strong></span>+<span class="keycap"><strong>Z</strong></span>
 127 $ psql -d postgres -c "BEGIN;INSERT INTO data(data) VALUES('5');PREPARE TRANSACTION 'test';"
 128 $ fg
 129 BEGIN 694
 130 table public.data: INSERT: id[integer]:5 data[text]:'5'
 131 PREPARE TRANSACTION 'test', txid 694
 132 <span class="keycap"><strong>Control</strong></span>+<span class="keycap"><strong>Z</strong></span>
 133 $ psql -d postgres -c "COMMIT PREPARED 'test';"
 134 $ fg
 135 COMMIT PREPARED 'test', txid 694
 136 <span class="keycap"><strong>Control</strong></span>+<span class="keycap"><strong>C</strong></span>
 137 $ pg_recvlogical -d postgres --slot=test --drop-slot
 138 </pre><p>
 139   The following example shows SQL interface that can be used to decode prepared
 140   transactions. Before you use two-phase commit commands, you must set
 141   <code class="varname">max_prepared_transactions</code> to at least 1. You must also have
 142   set the two-phase parameter as 'true' while creating the slot using
 143   <code class="function">pg_create_logical_replication_slot</code>
 144   Note that we will stream the entire transaction after the commit if it
 145   is not already decoded.
 146   </p><pre class="programlisting">
 147 postgres=# BEGIN;
 148 postgres=*# INSERT INTO data(data) VALUES('5');
 149 postgres=*# PREPARE TRANSACTION 'test_prepared1';
 150
 151 postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);
 152     lsn    | xid |                          data
 153 -----------+-----+---------------------------------------------------------
 154  0/1689DC0 | 529 | BEGIN 529
 155  0/1689DC0 | 529 | table public.data: INSERT: id[integer]:3 data[text]:'5'
 156  0/1689FC0 | 529 | PREPARE TRANSACTION 'test_prepared1', txid 529
 157 (3 rows)
 158
 159 postgres=# COMMIT PREPARED 'test_prepared1';
 160 postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL);
 161     lsn    | xid |                    data
 162 -----------+-----+--------------------------------------------
 163  0/168A060 | 529 | COMMIT PREPARED 'test_prepared1', txid 529
 164 (4 row)
 165
 166 postgres=#-- you can also rollback a prepared transaction
 167 postgres=# BEGIN;
 168 postgres=*# INSERT INTO data(data) VALUES('6');
 169 postgres=*# PREPARE TRANSACTION 'test_prepared2';
 170 postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL);
 171     lsn    | xid |                          data
 172 -----------+-----+---------------------------------------------------------
 173  0/168A180 | 530 | BEGIN 530
 174  0/168A1E8 | 530 | table public.data: INSERT: id[integer]:4 data[text]:'6'
 175  0/168A430 | 530 | PREPARE TRANSACTION 'test_prepared2', txid 530
 176 (3 rows)
 177
 178 postgres=# ROLLBACK PREPARED 'test_prepared2';
 179 postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL);
 180     lsn    | xid |                     data
 181 -----------+-----+----------------------------------------------
 182  0/168A4B8 | 530 | ROLLBACK PREPARED 'test_prepared2', txid 530
 183 (1 row)
 184 </pre></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="logicaldecoding.html" title="Chapter 47. Logical Decoding">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="logicaldecoding.html" title="Chapter 47. Logical Decoding">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="logicaldecoding-explanation.html" title="47.2. Logical Decoding Concepts">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 47. Logical Decoding </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 47.2. Logical Decoding Concepts</td></tr></table></div></body></html>