begriffs open source - ai-pg/blob - full-docs/src/sgml/html/sql-altersubscription.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>ALTER 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="sql-alterstatistics.html" title="ALTER STATISTICS" /><link rel="next" href="sql-altersystem.html" title="ALTER SYSTEM" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">ALTER SUBSCRIPTION</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-alterstatistics.html" title="ALTER STATISTICS">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</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="sql-altersystem.html" title="ALTER SYSTEM">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-ALTERSUBSCRIPTION"><div class="titlepage"></div><a id="id-1.9.3.33.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ALTER SUBSCRIPTION</span></h2><p>ALTER SUBSCRIPTION — change the definition of a subscription</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
   3 ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> CONNECTION '<em class="replaceable"><code>conninfo</code></em>'
   4 ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> SET PUBLICATION <em class="replaceable"><code>publication_name</code></em> [, ...] [ WITH ( <em class="replaceable"><code>publication_option</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) ]
   5 ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> ADD PUBLICATION <em class="replaceable"><code>publication_name</code></em> [, ...] [ WITH ( <em class="replaceable"><code>publication_option</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) ]
   6 ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> DROP PUBLICATION <em class="replaceable"><code>publication_name</code></em> [, ...] [ WITH ( <em class="replaceable"><code>publication_option</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) ]
   7 ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> REFRESH PUBLICATION [ WITH ( <em class="replaceable"><code>refresh_option</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) ]
   8 ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> ENABLE
   9 ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> DISABLE
  10 ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> SET ( <em class="replaceable"><code>subscription_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] )
  11 ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> SKIP ( <em class="replaceable"><code>skip_option</code></em> = <em class="replaceable"><code>value</code></em> )
  12 ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
  13 ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> RENAME TO <em class="replaceable"><code>new_name</code></em>
  14 </pre></div><div class="refsect1" id="id-1.9.3.33.5"><h2>Description</h2><p>
  15    <code class="command">ALTER SUBSCRIPTION</code> can change most of the subscription
  16    properties that can be specified
  17    in <a class="xref" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION"><span class="refentrytitle">CREATE SUBSCRIPTION</span></a>.
  18   </p><p>
  19    You must own the subscription to use <code class="command">ALTER SUBSCRIPTION</code>.
  20    To rename a subscription or alter the owner, you must have
  21    <code class="literal">CREATE</code> permission on the database. In addition,
  22    to alter the owner, you must be able to <code class="literal">SET ROLE</code> to the
  23    new owning role. If the subscription has
  24    <code class="literal">password_required=false</code>, only superusers can modify it.
  25   </p><p>
  26    When refreshing a publication we remove the relations that are no longer
  27    part of the publication and we also remove the table synchronization slots
  28    if there are any. It is necessary to remove these slots so that the resources
  29    allocated for the subscription on the remote host are released. If due to
  30    network breakdown or some other error, <span class="productname">PostgreSQL</span>
  31    is unable to remove the slots, an error will be reported. To proceed in this
  32    situation, the user either needs to retry the operation or disassociate the
  33    slot from the subscription and drop the subscription as explained in
  34    <a class="xref" href="sql-dropsubscription.html" title="DROP SUBSCRIPTION"><span class="refentrytitle">DROP SUBSCRIPTION</span></a>.
  35   </p><p>
  36    Commands <code class="command">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code>,
  37    <code class="command">ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...</code>
  38    with <code class="literal">refresh</code> option as <code class="literal">true</code>,
  39    <code class="command">ALTER SUBSCRIPTION ... SET (failover = true|false)</code> and
  40    <code class="command">ALTER SUBSCRIPTION ... SET (two_phase = false)</code>
  41    cannot be executed inside a transaction block.
  42   </p><p>
  43    Commands <code class="command">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code> and
  44    <code class="command">ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...</code>
  45    with <code class="literal">refresh</code> option as <code class="literal">true</code> also cannot
  46    be executed when the subscription has
  47    <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE"><code class="literal">two_phase</code></a>
  48    commit enabled, unless
  49    <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-COPY-DATA"><code class="literal">copy_data</code></a>
  50    is <code class="literal">false</code>. See column <code class="structfield">subtwophasestate</code>
  51    of <a class="link" href="catalog-pg-subscription.html" title="52.54. pg_subscription"><code class="structname">pg_subscription</code></a>
  52    to know the actual two-phase state.
  53   </p></div><div class="refsect1" id="id-1.9.3.33.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt id="SQL-ALTERSUBSCRIPTION-PARAMS-NAME"><span class="term"><em class="replaceable"><code>name</code></em></span> <a href="#SQL-ALTERSUBSCRIPTION-PARAMS-NAME" class="id_link">#</a></dt><dd><p>
  54       The name of a subscription whose properties are to be altered.
  55      </p></dd><dt id="SQL-ALTERSUBSCRIPTION-PARAMS-CONNECTION"><span class="term"><code class="literal">CONNECTION '<em class="replaceable"><code>conninfo</code></em>'</code></span> <a href="#SQL-ALTERSUBSCRIPTION-PARAMS-CONNECTION" class="id_link">#</a></dt><dd><p>
  56       This clause replaces the connection string originally set by
  57       <a class="xref" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION"><span class="refentrytitle">CREATE SUBSCRIPTION</span></a>.  See there for more
  58       information.
  59      </p></dd><dt id="SQL-ALTERSUBSCRIPTION-PARAMS-SETADDDROP-PUBLICATION"><span class="term"><code class="literal">SET PUBLICATION <em class="replaceable"><code>publication_name</code></em></code><br /></span><span class="term"><code class="literal">ADD PUBLICATION <em class="replaceable"><code>publication_name</code></em></code><br /></span><span class="term"><code class="literal">DROP PUBLICATION <em class="replaceable"><code>publication_name</code></em></code></span> <a href="#SQL-ALTERSUBSCRIPTION-PARAMS-SETADDDROP-PUBLICATION" class="id_link">#</a></dt><dd><p>
  60       These forms change the list of subscribed publications.
  61       <code class="literal">SET</code>
  62       replaces the entire list of publications with a new list,
  63       <code class="literal">ADD</code> adds additional publications to the list of
  64       publications, and <code class="literal">DROP</code> removes the publications from
  65       the list of publications.  We allow non-existent publications to be
  66       specified in <code class="literal">ADD</code> and <code class="literal">SET</code> variants
  67       so that users can add those later.  See <a class="xref" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION"><span class="refentrytitle">CREATE SUBSCRIPTION</span></a>
  68       for more information.  By default, this command will also act like
  69       <code class="literal">REFRESH PUBLICATION</code>.
  70      </p><p>
  71       <em class="replaceable"><code>publication_option</code></em> specifies additional
  72       options for this operation.  The supported options are:
  73
  74       </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">refresh</code> (<code class="type">boolean</code>)</span></dt><dd><p>
  75           When false, the command will not try to refresh table information.
  76           <code class="literal">REFRESH PUBLICATION</code> should then be executed separately.
  77           The default is <code class="literal">true</code>.
  78          </p></dd></dl></div><p>
  79
  80       Additionally, the options described under
  81       <code class="literal">REFRESH PUBLICATION</code> may be specified, to control the
  82       implicit refresh operation.
  83      </p></dd><dt id="SQL-ALTERSUBSCRIPTION-PARAMS-REFRESH-PUBLICATION"><span class="term"><code class="literal">REFRESH PUBLICATION</code></span> <a href="#SQL-ALTERSUBSCRIPTION-PARAMS-REFRESH-PUBLICATION" class="id_link">#</a></dt><dd><p>
  84       Fetch missing table information from publisher.  This will start
  85       replication of tables that were added to the subscribed-to publications
  86       since <a class="link" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION">
  87       <code class="command">CREATE SUBSCRIPTION</code></a> or
  88       the last invocation of <code class="command">REFRESH PUBLICATION</code>.
  89      </p><p>
  90       <em class="replaceable"><code>refresh_option</code></em> specifies additional options for the
  91       refresh operation.  The supported options are:
  92
  93       </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">copy_data</code> (<code class="type">boolean</code>)</span></dt><dd><p>
  94           Specifies whether to copy pre-existing data in the publications
  95           that are being subscribed to when the replication starts.
  96           The default is <code class="literal">true</code>.
  97          </p><p>
  98           Previously subscribed tables are not copied, even if a table's row
  99           filter <code class="literal">WHERE</code> clause has since been modified.
 100          </p><p>
 101           See <a class="xref" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-NOTES" title="Notes">Notes</a> for details of
 102           how <code class="literal">copy_data = true</code> can interact with the
 103           <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-ORIGIN"><code class="literal">origin</code></a>
 104           parameter.
 105          </p><p>
 106           See the
 107           <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-BINARY"><code class="literal">binary</code></a>
 108           parameter of <code class="command">CREATE SUBSCRIPTION</code> for details about
 109           copying pre-existing data in binary format.
 110          </p></dd></dl></div></dd><dt id="SQL-ALTERSUBSCRIPTION-PARAMS-ENABLE"><span class="term"><code class="literal">ENABLE</code></span> <a href="#SQL-ALTERSUBSCRIPTION-PARAMS-ENABLE" class="id_link">#</a></dt><dd><p>
 111       Enables a previously disabled subscription, starting the logical
 112       replication worker at the end of the transaction.
 113      </p></dd><dt id="SQL-ALTERSUBSCRIPTION-PARAMS-DISABLE"><span class="term"><code class="literal">DISABLE</code></span> <a href="#SQL-ALTERSUBSCRIPTION-PARAMS-DISABLE" class="id_link">#</a></dt><dd><p>
 114       Disables a running subscription, stopping the logical replication
 115       worker at the end of the transaction.
 116      </p></dd><dt id="SQL-ALTERSUBSCRIPTION-PARAMS-SET"><span class="term"><code class="literal">SET ( <em class="replaceable"><code>subscription_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] )</code></span> <a href="#SQL-ALTERSUBSCRIPTION-PARAMS-SET" class="id_link">#</a></dt><dd><p>
 117       This clause alters parameters originally set by
 118       <a class="xref" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION"><span class="refentrytitle">CREATE SUBSCRIPTION</span></a>.  See there for more
 119       information.  The parameters that can be altered are
 120       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME"><code class="literal">slot_name</code></a>,
 121       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SYNCHRONOUS-COMMIT"><code class="literal">synchronous_commit</code></a>,
 122       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-BINARY"><code class="literal">binary</code></a>,
 123       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-STREAMING"><code class="literal">streaming</code></a>,
 124       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-DISABLE-ON-ERROR"><code class="literal">disable_on_error</code></a>,
 125       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-PASSWORD-REQUIRED"><code class="literal">password_required</code></a>,
 126       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-RUN-AS-OWNER"><code class="literal">run_as_owner</code></a>,
 127       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-ORIGIN"><code class="literal">origin</code></a>,
 128       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER"><code class="literal">failover</code></a>, and
 129       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE"><code class="literal">two_phase</code></a>.
 130       Only a superuser can set <code class="literal">password_required = false</code>.
 131      </p><p>
 132       When altering the
 133       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME"><code class="literal">slot_name</code></a>,
 134       the <code class="literal">failover</code> and <code class="literal">two_phase</code> property
 135       values of the named slot may differ from the counterpart
 136       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER"><code class="literal">failover</code></a>
 137       and <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE"><code class="literal">two_phase</code></a>
 138       parameters specified in the subscription. When creating the slot, ensure
 139       the slot properties <code class="literal">failover</code> and <code class="literal">two_phase</code>
 140       match their counterpart parameters of the subscription.
 141       Otherwise, the slot on the publisher may behave differently from what these
 142       subscription options say: for example, the slot on the publisher could either be
 143       synced to the standbys even when the subscription's
 144       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER"><code class="literal">failover</code></a>
 145       option is disabled or could be disabled for sync
 146       even when the subscription's
 147       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER"><code class="literal">failover</code></a>
 148       option is enabled.
 149      </p><p>
 150       The <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER"><code class="literal">failover</code></a>
 151       and <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE"><code class="literal">two_phase</code></a>
 152       parameters can only be altered when the subscription is disabled.
 153      </p><p>
 154       When altering <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE"><code class="literal">two_phase</code></a>
 155       from <code class="literal">true</code> to <code class="literal">false</code>, the backend
 156       process reports an error if any prepared transactions done by the
 157       logical replication worker (from when <code class="literal">two_phase</code>
 158       parameter was still <code class="literal">true</code>) are found. You can resolve
 159       prepared transactions on the publisher node, or manually roll back them
 160       on the subscriber, and then try again. The transactions prepared by
 161       logical replication worker corresponding to a particular subscription have
 162       the following pattern: <span class="quote">“<span class="quote"><code class="literal">pg_gid_%u_%u</code></span>”</span>
 163       (parameters: subscription <em class="parameter"><code>oid</code></em>, remote transaction id <em class="parameter"><code>xid</code></em>).
 164       To resolve such transactions manually, you need to roll back all
 165       the prepared transactions with corresponding subscription IDs in their
 166       names. Applications can check
 167       <a class="link" href="view-pg-prepared-xacts.html" title="53.17. pg_prepared_xacts"><code class="structname">pg_prepared_xacts</code></a>
 168       to find the required prepared transactions. After the <code class="literal">two_phase</code>
 169       option is changed from <code class="literal">true</code> to <code class="literal">false</code>,
 170       the publisher will replicate the transactions again when they are committed.
 171      </p></dd><dt id="SQL-ALTERSUBSCRIPTION-PARAMS-SKIP"><span class="term"><code class="literal">SKIP ( <em class="replaceable"><code>skip_option</code></em> = <em class="replaceable"><code>value</code></em> )</code></span> <a href="#SQL-ALTERSUBSCRIPTION-PARAMS-SKIP" class="id_link">#</a></dt><dd><p>
 172       Skips applying all changes of the remote transaction.  If incoming data
 173       violates any constraints, logical replication will stop until it is
 174       resolved.  By using the <code class="command">ALTER SUBSCRIPTION ... SKIP</code> command,
 175       the logical replication worker skips all data modification changes within
 176       the transaction.  This option has no effect on the transactions that are
 177       already prepared by enabling
 178       <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE"><code class="literal">two_phase</code></a>
 179       on the subscriber.
 180       After the logical replication worker successfully skips the transaction or
 181       finishes a transaction, the LSN (stored in
 182       <code class="structname">pg_subscription</code>.<code class="structfield">subskiplsn</code>)
 183       is cleared.  See <a class="xref" href="logical-replication-conflicts.html" title="29.7. Conflicts">Section 29.7</a> for
 184       the details of logical replication conflicts.
 185      </p><p>
 186       <em class="replaceable"><code>skip_option</code></em> specifies options for this operation.
 187       The supported option is:
 188
 189       </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">lsn</code> (<code class="type">pg_lsn</code>)</span></dt><dd><p>
 190           Specifies the finish LSN of the remote transaction whose changes
 191           are to be skipped by the logical replication worker.  The finish LSN
 192           is the LSN at which the transaction is either committed or prepared.
 193           Skipping individual subtransactions is not supported.  Setting
 194           <code class="literal">NONE</code> resets the LSN.
 195          </p></dd></dl></div></dd><dt id="SQL-ALTERSUBSCRIPTION-PARAMS-NEW-OWNER"><span class="term"><em class="replaceable"><code>new_owner</code></em></span> <a href="#SQL-ALTERSUBSCRIPTION-PARAMS-NEW-OWNER" class="id_link">#</a></dt><dd><p>
 196       The user name of the new owner of the subscription.
 197      </p></dd><dt id="SQL-ALTERSUBSCRIPTION-PARAMS-NEW-NAME"><span class="term"><em class="replaceable"><code>new_name</code></em></span> <a href="#SQL-ALTERSUBSCRIPTION-PARAMS-NEW-NAME" class="id_link">#</a></dt><dd><p>
 198       The new name for the subscription.
 199      </p></dd></dl></div><p>
 200    When specifying a parameter of type <code class="type">boolean</code>, the
 201    <code class="literal">=</code> <em class="replaceable"><code>value</code></em>
 202    part can be omitted, which is equivalent to
 203    specifying <code class="literal">TRUE</code>.
 204   </p></div><div class="refsect1" id="id-1.9.3.33.7"><h2>Examples</h2><p>
 205    Change the publication subscribed by a subscription to
 206    <code class="literal">insert_only</code>:
 207 </p><pre class="programlisting">
 208 ALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;
 209 </pre><p>
 210   </p><p>
 211    Disable (stop) the subscription:
 212 </p><pre class="programlisting">
 213 ALTER SUBSCRIPTION mysub DISABLE;
 214 </pre></div><div class="refsect1" id="id-1.9.3.33.8"><h2>Compatibility</h2><p>
 215    <code class="command">ALTER SUBSCRIPTION</code> is a <span class="productname">PostgreSQL</span>
 216    extension.
 217   </p></div><div class="refsect1" id="id-1.9.3.33.9"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION"><span class="refentrytitle">CREATE SUBSCRIPTION</span></a>, <a class="xref" href="sql-dropsubscription.html" title="DROP SUBSCRIPTION"><span class="refentrytitle">DROP SUBSCRIPTION</span></a>, <a class="xref" href="sql-createpublication.html" title="CREATE PUBLICATION"><span class="refentrytitle">CREATE PUBLICATION</span></a>, <a class="xref" href="sql-alterpublication.html" title="ALTER PUBLICATION"><span class="refentrytitle">ALTER PUBLICATION</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-alterstatistics.html" title="ALTER STATISTICS">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-altersystem.html" title="ALTER SYSTEM">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER STATISTICS </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"> ALTER SYSTEM</td></tr></table></div></body></html>