diff options
Diffstat (limited to 'doc/src/sgml/html/logicaldecoding-example.html')
| -rw-r--r-- | doc/src/sgml/html/logicaldecoding-example.html | 119 |
1 files changed, 119 insertions, 0 deletions
diff --git a/doc/src/sgml/html/logicaldecoding-example.html b/doc/src/sgml/html/logicaldecoding-example.html new file mode 100644 index 0000000..390e239 --- /dev/null +++ b/doc/src/sgml/html/logicaldecoding-example.html @@ -0,0 +1,119 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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>48.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 V1.79.1" /><link rel="prev" href="logicaldecoding.html" title="Chapter 48. Logical Decoding" /><link rel="next" href="logicaldecoding-explanation.html" title="48.2. Logical Decoding Concepts" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">48.1. Logical Decoding Examples</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="logicaldecoding.html" title="Chapter 48. Logical Decoding">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="logicaldecoding.html" title="Chapter 48. Logical Decoding">Up</a></td><th width="60%" align="center">Chapter 48. Logical Decoding</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="logicaldecoding-explanation.html" title="48.2. Logical Decoding Concepts">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="LOGICALDECODING-EXAMPLE"><div class="titlepage"><div><div><h2 class="title" style="clear: both">48.1. Logical Decoding Examples</h2></div></div></div><p> + The following example demonstrates controlling logical decoding using the + SQL interface. + </p><p> + Before you can use logical decoding, you must set + <a class="xref" href="runtime-config-wal.html#GUC-WAL-LEVEL">wal_level</a> to <code class="literal">logical</code> and + <a class="xref" href="runtime-config-replication.html#GUC-MAX-REPLICATION-SLOTS">max_replication_slots</a> to at least 1. Then, you + should connect to the target database (in the example + below, <code class="literal">postgres</code>) as a superuser. + </p><pre class="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'); + 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 +-----------+-------+--------------------------------------------------------- + 0/BA5A688 | 10298 | BEGIN 10298 + 0/BA5A6F0 | 10298 | table public.data: INSERT: id[integer]:1 data[text]:'1' + 0/BA5A7F8 | 10298 | table public.data: INSERT: id[integer]:2 data[text]:'2' + 0/BA5A8A8 | 10298 | COMMIT 10298 +(4 rows) + +postgres=# INSERT INTO data(data) VALUES('3'); + +postgres=# -- You can also peek ahead in the change stream without consuming changes +postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL); + lsn | xid | data +-----------+-------+--------------------------------------------------------- + 0/BA5A8E0 | 10299 | BEGIN 10299 + 0/BA5A8E0 | 10299 | table public.data: INSERT: id[integer]:3 data[text]:'3' + 0/BA5A990 | 10299 | COMMIT 10299 +(3 rows) + +postgres=# -- The next call to pg_logical_slot_peek_changes() returns the same changes again +postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL); + lsn | xid | data +-----------+-------+--------------------------------------------------------- + 0/BA5A8E0 | 10299 | BEGIN 10299 + 0/BA5A8E0 | 10299 | table public.data: INSERT: id[integer]:3 data[text]:'3' + 0/BA5A990 | 10299 | COMMIT 10299 +(3 rows) + +postgres=# -- options can be passed to output plugin, to influence the formatting +postgres=# SELECT * FROM pg_logical_slot_peek_changes('regression_slot', NULL, NULL, 'include-timestamp', 'on'); + lsn | xid | data +-----------+-------+--------------------------------------------------------- + 0/BA5A8E0 | 10299 | BEGIN 10299 + 0/BA5A8E0 | 10299 | table public.data: INSERT: id[integer]:3 data[text]:'3' + 0/BA5A990 | 10299 | COMMIT 10299 (at 2017-05-10 12:07:21.272494-04) +(3 rows) + +postgres=# -- Remember to destroy a slot you no longer need to stop it consuming +postgres=# -- server resources: +postgres=# SELECT pg_drop_replication_slot('regression_slot'); + pg_drop_replication_slot +----------------------- + +(1 row) +</pre><p> + The following example shows how logical decoding is controlled over the + streaming replication protocol, using the + 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 + distribution. This requires that client authentication is set up to allow + replication connections + (see <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-AUTHENTICATION" title="26.2.5.1. Authentication">Section 26.2.5.1</a>) and + that <code class="varname">max_wal_senders</code> is set sufficiently high to allow + an additional connection. + </p><pre class="programlisting"> +$ pg_recvlogical -d postgres --slot=test --create-slot +$ pg_recvlogical -d postgres --slot=test --start -f - +<span class="keycap"><strong>Control</strong></span>+<span class="keycap"><strong>Z</strong></span> +$ psql -d postgres -c "INSERT INTO data(data) VALUES('4');" +$ fg +BEGIN 693 +table public.data: INSERT: id[integer]:4 data[text]:'4' +COMMIT 693 +<span class="keycap"><strong>Control</strong></span>+<span class="keycap"><strong>C</strong></span> +$ pg_recvlogical -d postgres --slot=test --drop-slot +</pre></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="logicaldecoding.html" title="Chapter 48. Logical Decoding">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="logicaldecoding.html" title="Chapter 48. Logical Decoding">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="logicaldecoding-explanation.html" title="48.2. Logical Decoding Concepts">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 48. Logical Decoding </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 48.2. Logical Decoding Concepts</td></tr></table></div></body></html>
\ No newline at end of file |