codekingpro/portable-devtools

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>49.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 49. Logical Decoding" /><link rel="next" href="logicaldecoding-explanation.html" title="49.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">49.1. Logical Decoding Examples</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="logicaldecoding.html" title="Chapter 49. Logical Decoding">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="logicaldecoding.html" title="Chapter 49. Logical Decoding">Up</a></td><th width="60%" align="center">Chapter 49. Logical Decoding</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="logicaldecoding-explanation.html" title="49.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">49.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 the4    SQL interface.5   </p><p>6    Before you can use logical decoding, you must set7    <a class="xref" href="runtime-config-wal.html#GUC-WAL-LEVEL">wal_level</a> to <code class="literal">logical</code> and8    <a class="xref" href="runtime-config-replication.html#GUC-MAX-REPLICATION-SLOTS">max_replication_slots</a> to at least 1.  Then, you9    should connect to the target database (in the example10    below, <code class="literal">postgres</code>) as a superuser.11   </p><pre class="programlisting">12postgres=# -- Create a slot named 'regression_slot' using the output plugin 'test_decoding'13postgres=# SELECT * FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding', false, true);14    slot_name    |    lsn15-----------------+-----------16 regression_slot | 0/16B197017(1 row)18 19postgres=# 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_lsn21-----------------+---------------+-----------+----------+--------+-------------+-----------------22 regression_slot | test_decoding | logical   | postgres | f      | 0/16A4408   | 0/16A444023(1 row)24 25postgres=# -- There are no changes to see yet26postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);27 lsn | xid | data28-----+-----+------29(0 rows)30 31postgres=# CREATE TABLE data(id serial primary key, data text);32CREATE TABLE33 34postgres=# -- DDL isn't replicated, so all you'll see is the transaction35postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);36    lsn    |  xid  |     data37-----------+-------+--------------38 0/BA2DA58 | 10297 | BEGIN 1029739 0/BA5A5A0 | 10297 | COMMIT 1029740(2 rows)41 42postgres=# -- Once changes are read, they're consumed and not emitted43postgres=# -- in a subsequent call:44postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);45 lsn | xid | data46-----+-----+------47(0 rows)48 49postgres=# BEGIN;50postgres=*# INSERT INTO data(data) VALUES('1');51postgres=*# INSERT INTO data(data) VALUES('2');52postgres=*# COMMIT;53 54postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);55    lsn    |  xid  |                          data56-----------+-------+---------------------------------------------------------57 0/BA5A688 | 10298 | BEGIN 1029858 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 1029861(4 rows)62 63postgres=# INSERT INTO data(data) VALUES('3');64 65postgres=# -- You can also peek ahead in the change stream without consuming changes66postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL);67    lsn    |  xid  |                          data68-----------+-------+---------------------------------------------------------69 0/BA5A8E0 | 10299 | BEGIN 1029970 0/BA5A8E0 | 10299 | table public.data: INSERT: id[integer]:3 data[text]:'3'71 0/BA5A990 | 10299 | COMMIT 1029972(3 rows)73 74postgres=# -- The next call to pg_logical_slot_peek_changes() returns the same changes again75postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL);76    lsn    |  xid  |                          data77-----------+-------+---------------------------------------------------------78 0/BA5A8E0 | 10299 | BEGIN 1029979 0/BA5A8E0 | 10299 | table public.data: INSERT: id[integer]:3 data[text]:'3'80 0/BA5A990 | 10299 | COMMIT 1029981(3 rows)82 83postgres=# -- options can be passed to output plugin, to influence the formatting84postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL, 'include-timestamp', 'on');85    lsn    |  xid  |                          data86-----------+-------+---------------------------------------------------------87 0/BA5A8E0 | 10299 | BEGIN 1029988 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 92postgres=# -- Remember to destroy a slot you no longer need to stop it consuming93postgres=# -- server resources:94postgres=# SELECT pg_drop_replication_slot('regression_slot');95 pg_drop_replication_slot96-----------------------97 98(1 row)99</pre><p>100    The following examples shows how logical decoding is controlled over the101    streaming replication protocol, using the102    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 PostgreSQL103    distribution.  This requires that client authentication is set up to allow104    replication connections105    (see <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-AUTHENTICATION" title="27.2.5.1. Authentication">Section 27.2.5.1</a>) and106    that <code class="varname">max_wal_senders</code> is set sufficiently high to allow107    an additional connection.  The second example shows how to stream two-phase108    transactions.  Before you use two-phase commands, you must set109    <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">111Example 1:112$ pg_recvlogical -d postgres --slot=test --create-slot113$ 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$ fg117BEGIN 693118table public.data: INSERT: id[integer]:4 data[text]:'4'119COMMIT 693120<span class="keycap"><strong>Control</strong></span>+<span class="keycap"><strong>C</strong></span>121$ pg_recvlogical -d postgres --slot=test --drop-slot122 123Example 2:124$ pg_recvlogical -d postgres --slot=test --create-slot --two-phase125$ 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$ fg129BEGIN 694130table public.data: INSERT: id[integer]:5 data[text]:'5'131PREPARE TRANSACTION 'test', txid 694132<span class="keycap"><strong>Control</strong></span>+<span class="keycap"><strong>Z</strong></span>133$ psql -d postgres -c "COMMIT PREPARED 'test';"134$ fg135COMMIT PREPARED 'test', txid 694136<span class="keycap"><strong>Control</strong></span>+<span class="keycap"><strong>C</strong></span>137$ pg_recvlogical -d postgres --slot=test --drop-slot138</pre><p>139  The following example shows SQL interface that can be used to decode prepared140  transactions. Before you use two-phase commit commands, you must set141  <code class="varname">max_prepared_transactions</code> to at least 1. You must also have142  set the two-phase parameter as 'true' while creating the slot using143  <code class="function">pg_create_logical_replication_slot</code>144  Note that we will stream the entire transaction after the commit if it145  is not already decoded.146  </p><pre class="programlisting">147postgres=# BEGIN;148postgres=*# INSERT INTO data(data) VALUES('5');149postgres=*# PREPARE TRANSACTION 'test_prepared1';150 151postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL);152    lsn    | xid |                          data153-----------+-----+---------------------------------------------------------154 0/1689DC0 | 529 | BEGIN 529155 0/1689DC0 | 529 | table public.data: INSERT: id[integer]:3 data[text]:'5'156 0/1689FC0 | 529 | PREPARE TRANSACTION 'test_prepared1', txid 529157(3 rows)158 159postgres=# COMMIT PREPARED 'test_prepared1';160postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL);161    lsn    | xid |                    data162-----------+-----+--------------------------------------------163 0/168A060 | 529 | COMMIT PREPARED 'test_prepared1', txid 529164(4 row)165 166postgres=#-- you can also rollback a prepared transaction167postgres=# BEGIN;168postgres=*# INSERT INTO data(data) VALUES('6');169postgres=*# PREPARE TRANSACTION 'test_prepared2';170postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL);171    lsn    | xid |                          data172-----------+-----+---------------------------------------------------------173 0/168A180 | 530 | BEGIN 530174 0/168A1E8 | 530 | table public.data: INSERT: id[integer]:4 data[text]:'6'175 0/168A430 | 530 | PREPARE TRANSACTION 'test_prepared2', txid 530176(3 rows)177 178postgres=# ROLLBACK PREPARED 'test_prepared2';179postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL);180    lsn    | xid |                     data181-----------+-----+----------------------------------------------182 0/168A4B8 | 530 | ROLLBACK PREPARED 'test_prepared2', txid 530183(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 49. Logical Decoding">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="logicaldecoding.html" title="Chapter 49. Logical Decoding">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="logicaldecoding-explanation.html" title="49.2. Logical Decoding Concepts">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 49. Logical Decoding </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 49.2. Logical Decoding Concepts</td></tr></table></div></body></html>