codekingpro/portable-devtools
115k
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 16.3 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">3ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> CONNECTION '<em class="replaceable"><code>conninfo</code></em>'4ALTER 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>] [, ... ] ) ]5ALTER 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>] [, ... ] ) ]6ALTER 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>] [, ... ] ) ]7ALTER 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>] [, ... ] ) ]8ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> ENABLE9ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> DISABLE10ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> SET ( <em class="replaceable"><code>subscription_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] )11ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> SKIP ( <em class="replaceable"><code>skip_option</code></em> = <em class="replaceable"><code>value</code></em> )12ALTER SUBSCRIPTION <em class="replaceable"><code>name</code></em> OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }13ALTER 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 subscription16 properties that can be specified17 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 have21 <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 the23 new owning role. If the subscription has24 <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 longer27 part of the publication and we also remove the table synchronization slots28 if there are any. It is necessary to remove these slots so that the resources29 allocated for the subscription on the remote host are released. If due to30 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 this32 situation, the user either needs to retry the operation or disassociate the33 slot from the subscription and drop the subscription as explained in34 <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> and37 <code class="command">ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...</code>38 with <code class="literal">refresh</code> option as <code class="literal">true</code> cannot be39 executed inside a transaction block.40 41 These commands also cannot be executed when the subscription has42 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-TWO-PHASE"><code class="literal">two_phase</code></a>43 commit enabled, unless44 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-COPY-DATA"><code class="literal">copy_data</code></a>45 is <code class="literal">false</code>. See column <code class="structfield">subtwophasestate</code>46 of <a class="link" href="catalog-pg-subscription.html" title="53.54. pg_subscription"><code class="structname">pg_subscription</code></a>47 to know the actual two-phase state.48 </p></div><div class="refsect1" id="id-1.9.3.33.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>49 The name of a subscription whose properties are to be altered.50 </p></dd><dt><span class="term"><code class="literal">CONNECTION '<em class="replaceable"><code>conninfo</code></em>'</code></span></dt><dd><p>51 This clause replaces the connection string originally set by52 <a class="xref" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION"><span class="refentrytitle">CREATE SUBSCRIPTION</span></a>. See there for more53 information.54 </p></dd><dt><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></dt><dd><p>55 These forms change the list of subscribed publications.56 <code class="literal">SET</code>57 replaces the entire list of publications with a new list,58 <code class="literal">ADD</code> adds additional publications to the list of59 publications, and <code class="literal">DROP</code> removes the publications from60 the list of publications. We allow non-existent publications to be61 specified in <code class="literal">ADD</code> and <code class="literal">SET</code> variants62 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>63 for more information. By default, this command will also act like64 <code class="literal">REFRESH PUBLICATION</code>.65 </p><p>66 <em class="replaceable"><code>publication_option</code></em> specifies additional67 options for this operation. The supported options are:68 69 </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>70 When false, the command will not try to refresh table information.71 <code class="literal">REFRESH PUBLICATION</code> should then be executed separately.72 The default is <code class="literal">true</code>.73 </p></dd></dl></div><p>74 75 Additionally, the options described under76 <code class="literal">REFRESH PUBLICATION</code> may be specified, to control the77 implicit refresh operation.78 </p></dd><dt><span class="term"><code class="literal">REFRESH PUBLICATION</code></span></dt><dd><p>79 Fetch missing table information from publisher. This will start80 replication of tables that were added to the subscribed-to publications81 since <code class="command">CREATE SUBSCRIPTION</code> or82 the last invocation of <code class="command">REFRESH PUBLICATION</code>.83 </p><p>84 <em class="replaceable"><code>refresh_option</code></em> specifies additional options for the85 refresh operation. The supported options are:86 87 </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>88 Specifies whether to copy pre-existing data in the publications89 that are being subscribed to when the replication starts.90 The default is <code class="literal">true</code>.91 </p><p>92 Previously subscribed tables are not copied, even if a table's row93 filter <code class="literal">WHERE</code> clause has since been modified.94 </p><p>95 See <a class="xref" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-NOTES" title="Notes">Notes</a> for details of96 how <code class="literal">copy_data = true</code> can interact with the97 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-ORIGIN"><code class="literal">origin</code></a>98 parameter.99 </p><p>100 See the101 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-BINARY"><code class="literal">binary</code></a>102 parameter of <code class="command">CREATE SUBSCRIPTION</code> for details about103 copying pre-existing data in binary format.104 </p></dd></dl></div></dd><dt><span class="term"><code class="literal">ENABLE</code></span></dt><dd><p>105 Enables a previously disabled subscription, starting the logical106 replication worker at the end of the transaction.107 </p></dd><dt><span class="term"><code class="literal">DISABLE</code></span></dt><dd><p>108 Disables a running subscription, stopping the logical replication109 worker at the end of the transaction.110 </p></dd><dt><span class="term"><code class="literal">SET ( <em class="replaceable"><code>subscription_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] )</code></span></dt><dd><p>111 This clause alters parameters originally set by112 <a class="xref" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION"><span class="refentrytitle">CREATE SUBSCRIPTION</span></a>. See there for more113 information. The parameters that can be altered are114 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-SLOT-NAME"><code class="literal">slot_name</code></a>,115 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-SYNCHRONOUS-COMMIT"><code class="literal">synchronous_commit</code></a>,116 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-BINARY"><code class="literal">binary</code></a>,117 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-STREAMING"><code class="literal">streaming</code></a>,118 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-DISABLE-ON-ERROR"><code class="literal">disable_on_error</code></a>,119 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-PASSWORD-REQUIRED"><code class="literal">password_required</code></a>,120 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-RUN-AS-OWNER"><code class="literal">run_as_owner</code></a>, and121 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-ORIGIN"><code class="literal">origin</code></a>.122 Only a superuser can set <code class="literal">password_required = false</code>.123 </p></dd><dt><span class="term"><code class="literal">SKIP ( <em class="replaceable"><code>skip_option</code></em> = <em class="replaceable"><code>value</code></em> )</code></span></dt><dd><p>124 Skips applying all changes of the remote transaction. If incoming data125 violates any constraints, logical replication will stop until it is126 resolved. By using the <code class="command">ALTER SUBSCRIPTION ... SKIP</code> command,127 the logical replication worker skips all data modification changes within128 the transaction. This option has no effect on the transactions that are129 already prepared by enabling130 <a class="link" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-WITH-TWO-PHASE"><code class="literal">two_phase</code></a>131 on the subscriber.132 After the logical replication worker successfully skips the transaction or133 finishes a transaction, the LSN (stored in134 <code class="structname">pg_subscription</code>.<code class="structfield">subskiplsn</code>)135 is cleared. See <a class="xref" href="logical-replication-conflicts.html" title="31.5. Conflicts">Section 31.5</a> for136 the details of logical replication conflicts.137 </p><p>138 <em class="replaceable"><code>skip_option</code></em> specifies options for this operation.139 The supported option is:140 141 </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>142 Specifies the finish LSN of the remote transaction whose changes143 are to be skipped by the logical replication worker. The finish LSN144 is the LSN at which the transaction is either committed or prepared.145 Skipping individual subtransactions is not supported. Setting146 <code class="literal">NONE</code> resets the LSN.147 </p></dd></dl></div></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p>148 The user name of the new owner of the subscription.149 </p></dd><dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt><dd><p>150 The new name for the subscription.151 </p></dd></dl></div><p>152 When specifying a parameter of type <code class="type">boolean</code>, the153 <code class="literal">=</code> <em class="replaceable"><code>value</code></em>154 part can be omitted, which is equivalent to155 specifying <code class="literal">TRUE</code>.156 </p></div><div class="refsect1" id="id-1.9.3.33.7"><h2>Examples</h2><p>157 Change the publication subscribed by a subscription to158 <code class="literal">insert_only</code>:159</p><pre class="programlisting">160ALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;161</pre><p>162 </p><p>163 Disable (stop) the subscription:164</p><pre class="programlisting">165ALTER SUBSCRIPTION mysub DISABLE;166</pre></div><div class="refsect1" id="id-1.9.3.33.8"><h2>Compatibility</h2><p>167 <code class="command">ALTER SUBSCRIPTION</code> is a <span class="productname">PostgreSQL</span>168 extension.169 </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER SYSTEM</td></tr></table></div></body></html>