Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
logical-replication-subscription.html395 linesDownload Raw Back to 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>31.2. Subscription</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="logical-replication-publication.html" title="31.1. Publication" /><link rel="next" href="logical-replication-row-filter.html" title="31.3. Row Filters" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">31.2. Subscription</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="logical-replication-publication.html" title="31.1. Publication">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="logical-replication.html" title="Chapter 31. Logical Replication">Up</a></td><th width="60%" align="center">Chapter 31. Logical Replication</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="logical-replication-row-filter.html" title="31.3. Row Filters">Next</a></td></tr></table><hr /></div><div class="sect1" id="LOGICAL-REPLICATION-SUBSCRIPTION"><div class="titlepage"><div><div><h2 class="title" style="clear: both">31.2. Subscription <a href="#LOGICAL-REPLICATION-SUBSCRIPTION" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT">31.2.1. Replication Slot Management</a></span></dt><dt><span class="sect2"><a href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES">31.2.2. Examples: Set Up Logical Replication</a></span></dt><dt><span class="sect2"><a href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES-DEFERRED-SLOT">31.2.3. Examples: Deferred Replication Slot Creation</a></span></dt></dl></div><p>3   A <em class="firstterm">subscription</em> is the downstream side of logical4   replication.  The node where a subscription is defined is referred to as5   the <em class="firstterm">subscriber</em>.  A subscription defines the connection6   to another database and set of publications (one or more) to which it wants7   to subscribe.8  </p><p>9   The subscriber database behaves in the same way as any other PostgreSQL10   instance and can be used as a publisher for other databases by defining its11   own publications.12  </p><p>13   A subscriber node may have multiple subscriptions if desired.  It is14   possible to define multiple subscriptions between a single15   publisher-subscriber pair, in which case care must be taken to ensure16   that the subscribed publication objects don't overlap.17  </p><p>18   Each subscription will receive changes via one replication slot (see19   <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="27.2.6. Replication Slots">Section 27.2.6</a>).  Additional replication20   slots may be required for the initial data synchronization of21   pre-existing table data and those will be dropped at the end of data22   synchronization.23  </p><p>24   A logical replication subscription can be a standby for synchronous25   replication (see <a class="xref" href="warm-standby.html#SYNCHRONOUS-REPLICATION" title="27.2.8. Synchronous Replication">Section 27.2.8</a>).  The standby26   name is by default the subscription name.  An alternative name can be27   specified as <code class="literal">application_name</code> in the connection28   information of the subscription.29  </p><p>30   Subscriptions are dumped by <code class="command">pg_dump</code> if the current user31   is a superuser.  Otherwise a warning is written and subscriptions are32   skipped, because non-superusers cannot read all subscription information33   from the <code class="structname">pg_subscription</code> catalog.34  </p><p>35   The subscription is added using <a class="link" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION"><code class="command">CREATE SUBSCRIPTION</code></a> and36   can be stopped/resumed at any time using the37   <a class="link" href="sql-altersubscription.html" title="ALTER SUBSCRIPTION"><code class="command">ALTER SUBSCRIPTION</code></a> command and removed using38   <a class="link" href="sql-dropsubscription.html" title="DROP SUBSCRIPTION"><code class="command">DROP SUBSCRIPTION</code></a>.39  </p><p>40   When a subscription is dropped and recreated, the synchronization41   information is lost.  This means that the data has to be resynchronized42   afterwards.43  </p><p>44   The schema definitions are not replicated, and the published tables must45   exist on the subscriber.  Only regular tables may be46   the target of replication.  For example, you can't replicate to a view.47  </p><p>48   The tables are matched between the publisher and the subscriber using the49   fully qualified table name.  Replication to differently-named tables on the50   subscriber is not supported.51  </p><p>52   Columns of a table are also matched by name.  The order of columns in the53   subscriber table does not need to match that of the publisher.  The data54   types of the columns do not need to match, as long as the text55   representation of the data can be converted to the target type.  For56   example, you can replicate from a column of type <code class="type">integer</code> to a57   column of type <code class="type">bigint</code>.  The target table can also have58   additional columns not provided by the published table.  Any such columns59   will be filled with the default value as specified in the definition of the60   target table. However, logical replication in binary format is more61   restrictive. See the62   <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-BINARY"><code class="literal">binary</code></a>63   option of <code class="command">CREATE SUBSCRIPTION</code> for details.64  </p><div class="sect2" id="LOGICAL-REPLICATION-SUBSCRIPTION-SLOT"><div class="titlepage"><div><div><h3 class="title">31.2.1. Replication Slot Management <a href="#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT" class="id_link">#</a></h3></div></div></div><p>65    As mentioned earlier, each (active) subscription receives changes from a66    replication slot on the remote (publishing) side.67   </p><p>68    Additional table synchronization slots are normally transient, created69    internally to perform initial table synchronization and dropped70    automatically when they are no longer needed. These table synchronization71    slots have generated names: <span class="quote">“<span class="quote"><code class="literal">pg_%u_sync_%u_%llu</code></span>”</span>72    (parameters: Subscription <em class="parameter"><code>oid</code></em>,73    Table <em class="parameter"><code>relid</code></em>, system identifier <em class="parameter"><code>sysid</code></em>)74   </p><p>75    Normally, the remote replication slot is created automatically when the76    subscription is created using <code class="command">CREATE SUBSCRIPTION</code> and it77    is dropped automatically when the subscription is dropped using78    <code class="command">DROP SUBSCRIPTION</code>.  In some situations, however, it can79    be useful or necessary to manipulate the subscription and the underlying80    replication slot separately.  Here are some scenarios:81 82    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>83       When creating a subscription, the replication slot already exists.  In84       that case, the subscription can be created using85       the <code class="literal">create_slot = false</code> option to associate with the86       existing slot.87      </p></li><li class="listitem"><p>88       When creating a subscription, the remote host is not reachable or in an89       unclear state.  In that case, the subscription can be created using90       the <code class="literal">connect = false</code> option.  The remote host will then not91       be contacted at all.  This is what <span class="application">pg_dump</span>92       uses.  The remote replication slot will then have to be created93       manually before the subscription can be activated.94      </p></li><li class="listitem"><p>95       When dropping a subscription, the replication slot should be kept.96       This could be useful when the subscriber database is being moved to a97       different host and will be activated from there.  In that case,98       disassociate the slot from the subscription using <code class="command">ALTER99       SUBSCRIPTION</code> before attempting to drop the subscription.100      </p></li><li class="listitem"><p>101       When dropping a subscription, the remote host is not reachable.  In102       that case, disassociate the slot from the subscription103       using <code class="command">ALTER SUBSCRIPTION</code> before attempting to drop104       the subscription.  If the remote database instance no longer exists, no105       further action is then necessary.  If, however, the remote database106       instance is just unreachable, the replication slot (and any still107       remaining table synchronization slots) should then be108       dropped manually; otherwise it/they would continue to reserve WAL and might109       eventually cause the disk to fill up.  Such cases should be carefully110       investigated.111      </p></li></ul></div><p>112   </p></div><div class="sect2" id="LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES"><div class="titlepage"><div><div><h3 class="title">31.2.2. Examples: Set Up Logical Replication <a href="#LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES" class="id_link">#</a></h3></div></div></div><p>113     Create some test tables on the publisher.114</p><pre class="programlisting">115test_pub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a));116CREATE TABLE117test_pub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c));118CREATE TABLE119test_pub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e));120CREATE TABLE121</pre><p>122     Create the same tables on the subscriber.123</p><pre class="programlisting">124test_sub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a));125CREATE TABLE126test_sub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c));127CREATE TABLE128test_sub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e));129CREATE TABLE130</pre><p>131     Insert data to the tables at the publisher side.132</p><pre class="programlisting">133test_pub=# INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');134INSERT 0 3135test_pub=# INSERT INTO t2 VALUES (1, 'A'), (2, 'B'), (3, 'C');136INSERT 0 3137test_pub=# INSERT INTO t3 VALUES (1, 'i'), (2, 'ii'), (3, 'iii');138INSERT 0 3139</pre><p>140     Create publications for the tables. The publications <code class="literal">pub2</code>141     and <code class="literal">pub3a</code> disallow some142     <a class="link" href="sql-createpublication.html#SQL-CREATEPUBLICATION-WITH-PUBLISH"><code class="literal">publish</code></a>143     operations. The publication <code class="literal">pub3b</code> has a row filter (see144     <a class="xref" href="logical-replication-row-filter.html" title="31.3. Row Filters">Section 31.3</a>).145</p><pre class="programlisting">146test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1;147CREATE PUBLICATION148test_pub=# CREATE PUBLICATION pub2 FOR TABLE t2 WITH (publish = 'truncate');149CREATE PUBLICATION150test_pub=# CREATE PUBLICATION pub3a FOR TABLE t3 WITH (publish = 'truncate');151CREATE PUBLICATION152test_pub=# CREATE PUBLICATION pub3b FOR TABLE t3 WHERE (e &gt; 5);153CREATE PUBLICATION154</pre><p>155     Create subscriptions for the publications. The subscription156     <code class="literal">sub3</code> subscribes to both <code class="literal">pub3a</code> and157     <code class="literal">pub3b</code>. All subscriptions will copy initial data by default.158</p><pre class="programlisting">159test_sub=# CREATE SUBSCRIPTION sub1160test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub1'161test_sub-# PUBLICATION pub1;162CREATE SUBSCRIPTION163test_sub=# CREATE SUBSCRIPTION sub2164test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub2'165test_sub-# PUBLICATION pub2;166CREATE SUBSCRIPTION167test_sub=# CREATE SUBSCRIPTION sub3168test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub3'169test_sub-# PUBLICATION pub3a, pub3b;170CREATE SUBSCRIPTION171</pre><p>172     Observe that initial table data is copied, regardless of the173     <code class="literal">publish</code> operation of the publication.174</p><pre class="programlisting">175test_sub=# SELECT * FROM t1;176 a |   b177---+-------178 1 | one179 2 | two180 3 | three181(3 rows)182 183test_sub=# SELECT * FROM t2;184 c | d185---+---186 1 | A187 2 | B188 3 | C189(3 rows)190</pre><p>191     Furthermore, because the initial data copy ignores the <code class="literal">publish</code>192     operation, and because publication <code class="literal">pub3a</code> has no row filter,193     it means the copied table <code class="literal">t3</code> contains all rows even when194     they do not match the row filter of publication <code class="literal">pub3b</code>.195</p><pre class="programlisting">196test_sub=# SELECT * FROM t3;197 e |  f198---+-----199 1 | i200 2 | ii201 3 | iii202(3 rows)203</pre><p>204    Insert more data to the tables at the publisher side.205</p><pre class="programlisting">206test_pub=# INSERT INTO t1 VALUES (4, 'four'), (5, 'five'), (6, 'six');207INSERT 0 3208test_pub=# INSERT INTO t2 VALUES (4, 'D'), (5, 'E'), (6, 'F');209INSERT 0 3210test_pub=# INSERT INTO t3 VALUES (4, 'iv'), (5, 'v'), (6, 'vi');211INSERT 0 3212</pre><p>213    Now the publisher side data looks like:214</p><pre class="programlisting">215test_pub=# SELECT * FROM t1;216 a |   b217---+-------218 1 | one219 2 | two220 3 | three221 4 | four222 5 | five223 6 | six224(6 rows)225 226test_pub=# SELECT * FROM t2;227 c | d228---+---229 1 | A230 2 | B231 3 | C232 4 | D233 5 | E234 6 | F235(6 rows)236 237test_pub=# SELECT * FROM t3;238 e |  f239---+-----240 1 | i241 2 | ii242 3 | iii243 4 | iv244 5 | v245 6 | vi246(6 rows)247</pre><p>248    Observe that during normal replication the appropriate249    <code class="literal">publish</code> operations are used. This means publications250    <code class="literal">pub2</code> and <code class="literal">pub3a</code> will not replicate the251    <code class="literal">INSERT</code>. Also, publication <code class="literal">pub3b</code> will252    only replicate data that matches the row filter of <code class="literal">pub3b</code>.253    Now the subscriber side data looks like:254</p><pre class="programlisting">255test_sub=# SELECT * FROM t1;256 a |   b257---+-------258 1 | one259 2 | two260 3 | three261 4 | four262 5 | five263 6 | six264(6 rows)265 266test_sub=# SELECT * FROM t2;267 c | d268---+---269 1 | A270 2 | B271 3 | C272(3 rows)273 274test_sub=# SELECT * FROM t3;275 e |  f276---+-----277 1 | i278 2 | ii279 3 | iii280 6 | vi281(4 rows)282</pre></div><div class="sect2" id="LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES-DEFERRED-SLOT"><div class="titlepage"><div><div><h3 class="title">31.2.3. Examples: Deferred Replication Slot Creation <a href="#LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES-DEFERRED-SLOT" class="id_link">#</a></h3></div></div></div><p>283    There are some cases (e.g.284    <a class="xref" href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-SLOT" title="31.2.1. Replication Slot Management">Section 31.2.1</a>) where, if the285    remote replication slot was not created automatically, the user must create286    it manually before the subscription can be activated. The steps to create287    the slot and activate the subscription are shown in the following examples.288    These examples specify the standard logical decoding output plugin289    (<code class="literal">pgoutput</code>), which is what the built-in logical290    replication uses.291   </p><p>292    First, create a publication for the examples to use.293</p><pre class="programlisting">294test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES;295CREATE PUBLICATION296</pre><p>297    Example 1: Where the subscription says <code class="literal">connect = false</code>298   </p><p>299    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>300       Create the subscription.301</p><pre class="programlisting">302test_sub=# CREATE SUBSCRIPTION sub1303test_sub-# CONNECTION 'host=localhost dbname=test_pub'304test_sub-# PUBLICATION pub1305test_sub-# WITH (connect=false);306WARNING:  subscription was created, but is not connected307HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.308CREATE SUBSCRIPTION309</pre></li><li class="listitem"><p>310       On the publisher, manually create a slot. Because the name was not311       specified during <code class="literal">CREATE SUBSCRIPTION</code>, the name of the312       slot to create is same as the subscription name, e.g. "sub1".313</p><pre class="programlisting">314test_pub=# SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput');315 slot_name |    lsn316-----------+-----------317 sub1      | 0/19404D0318(1 row)319</pre></li><li class="listitem"><p>320       On the subscriber, complete the activation of the subscription. After321       this the tables of <code class="literal">pub1</code> will start replicating.322</p><pre class="programlisting">323test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;324ALTER SUBSCRIPTION325test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;326ALTER SUBSCRIPTION327</pre></li></ul></div><p>328   </p><p>329    Example 2: Where the subscription says <code class="literal">connect = false</code>,330    but also specifies the331    <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-SLOT-NAME"><code class="literal">slot_name</code></a>332    option.333    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>334       Create the subscription.335</p><pre class="programlisting">336test_sub=# CREATE SUBSCRIPTION sub1337test_sub-# CONNECTION 'host=localhost dbname=test_pub'338test_sub-# PUBLICATION pub1339test_sub-# WITH (connect=false, slot_name='myslot');340WARNING:  subscription was created, but is not connected341HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.342CREATE SUBSCRIPTION343</pre></li><li class="listitem"><p>344       On the publisher, manually create a slot using the same name that was345       specified during <code class="literal">CREATE SUBSCRIPTION</code>, e.g. "myslot".346</p><pre class="programlisting">347test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');348 slot_name |    lsn349-----------+-----------350 myslot    | 0/19059A0351(1 row)352</pre></li><li class="listitem"><p>353       On the subscriber, the remaining subscription activation steps are the354       same as before.355</p><pre class="programlisting">356test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;357ALTER SUBSCRIPTION358test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;359ALTER SUBSCRIPTION360</pre></li></ul></div><p>361   </p><p>362    Example 3: Where the subscription specifies <code class="literal">slot_name = NONE</code>363    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>364       Create the subscription. When <code class="literal">slot_name = NONE</code> then365       <code class="literal">enabled = false</code>, and366       <code class="literal">create_slot = false</code> are also needed.367</p><pre class="programlisting">368test_sub=# CREATE SUBSCRIPTION sub1369test_sub-# CONNECTION 'host=localhost dbname=test_pub'370test_sub-# PUBLICATION pub1371test_sub-# WITH (slot_name=NONE, enabled=false, create_slot=false);372CREATE SUBSCRIPTION373</pre></li><li class="listitem"><p>374       On the publisher, manually create a slot using any name, e.g. "myslot".375</p><pre class="programlisting">376test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');377 slot_name |    lsn378-----------+-----------379 myslot    | 0/1905930380(1 row)381</pre></li><li class="listitem"><p>382       On the subscriber, associate the subscription with the slot name just383       created.384</p><pre class="programlisting">385test_sub=# ALTER SUBSCRIPTION sub1 SET (slot_name='myslot');386ALTER SUBSCRIPTION387</pre></li><li class="listitem"><p>388       The remaining subscription activation steps are same as before.389</p><pre class="programlisting">390test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;391ALTER SUBSCRIPTION392test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;393ALTER SUBSCRIPTION394</pre></li></ul></div><p>395   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="logical-replication-publication.html" title="31.1. Publication">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="logical-replication.html" title="Chapter 31. Logical Replication">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="logical-replication-row-filter.html" title="31.3. Row Filters">Next</a></td></tr><tr><td width="40%" align="left" valign="top">31.1. Publication </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"> 31.3. Row Filters</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai