Debian Codegraph @

doc/src/sgml/ref/alter_subscription.sgml

raw
1 <!--
2 doc/src/sgml/ref/alter_subscription.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-altersubscription">
7 <indexterm zone="sql-altersubscription">
8 <primary>ALTER SUBSCRIPTION</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>ALTER SUBSCRIPTION</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>ALTER SUBSCRIPTION</refname>
19 <refpurpose>change the definition of a subscription</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> CONNECTION '<replaceable>conninfo</replaceable>'
25 ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> SET PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...] [ WITH ( <replaceable class="parameter">publication_option</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
26 ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> ADD PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...] [ WITH ( <replaceable class="parameter">publication_option</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
27 ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> DROP PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...] [ WITH ( <replaceable class="parameter">publication_option</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
28 ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> REFRESH PUBLICATION [ WITH ( <replaceable class="parameter">refresh_option</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
29 ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> ENABLE
30 ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> DISABLE
31 ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">subscription_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
32 ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> SKIP ( <replaceable class="parameter">skip_option</replaceable> = <replaceable class="parameter">value</replaceable> )
33 ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
34 ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
35 </synopsis>
36 </refsynopsisdiv>
37
38 <refsect1>
39 <title>Description</title>
40
41 <para>
42 <command>ALTER SUBSCRIPTION</command> can change most of the subscription
43 properties that can be specified
44 in <xref linkend="sql-createsubscription"/>.
45 </para>
46
47 <para>
48 You must own the subscription to use <command>ALTER SUBSCRIPTION</command>.
49 To rename a subscription or alter the owner, you must have
50 <literal>CREATE</literal> permission on the database. In addition,
51 to alter the owner, you must be able to <literal>SET ROLE</literal> to the
52 new owning role. If the subscription has
53 <literal>password_required=false</literal>, only superusers can modify it.
54 </para>
55
56 <para>
57 When refreshing a publication we remove the relations that are no longer
58 part of the publication and we also remove the table synchronization slots
59 if there are any. It is necessary to remove these slots so that the resources
60 allocated for the subscription on the remote host are released. If due to
61 network breakdown or some other error, <productname>PostgreSQL</productname>
62 is unable to remove the slots, an error will be reported. To proceed in this
63 situation, the user either needs to retry the operation or disassociate the
64 slot from the subscription and drop the subscription as explained in
65 <xref linkend="sql-dropsubscription"/>.
66 </para>
67
68 <para>
69 Commands <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command>,
70 <command>ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...</command>
71 with <literal>refresh</literal> option as <literal>true</literal>,
72 <command>ALTER SUBSCRIPTION ... SET (failover = true|false)</command> and
73 <command>ALTER SUBSCRIPTION ... SET (two_phase = false)</command>
74 cannot be executed inside a transaction block.
75 </para>
76
77 <para>
78 Commands <command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command> and
79 <command>ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...</command>
80 with <literal>refresh</literal> option as <literal>true</literal> also cannot
81 be executed when the subscription has
82 <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
83 commit enabled, unless
84 <link linkend="sql-createsubscription-params-with-copy-data"><literal>copy_data</literal></link>
85 is <literal>false</literal>. See column <structfield>subtwophasestate</structfield>
86 of <link linkend="catalog-pg-subscription"><structname>pg_subscription</structname></link>
87 to know the actual two-phase state.
88 </para>
89 </refsect1>
90
91 <refsect1>
92 <title>Parameters</title>
93
94 <variablelist>
95 <varlistentry id="sql-altersubscription-params-name">
96 <term><replaceable class="parameter">name</replaceable></term>
97 <listitem>
98 <para>
99 The name of a subscription whose properties are to be altered.
100 </para>
101 </listitem>
102 </varlistentry>
103
104 <varlistentry id="sql-altersubscription-params-connection">
105 <term><literal>CONNECTION '<replaceable class="parameter">conninfo</replaceable>'</literal></term>
106 <listitem>
107 <para>
108 This clause replaces the connection string originally set by
109 <xref linkend="sql-createsubscription"/>. See there for more
110 information.
111 </para>
112 </listitem>
113 </varlistentry>
114
115 <varlistentry id="sql-altersubscription-params-setadddrop-publication">
116 <term><literal>SET PUBLICATION <replaceable class="parameter">publication_name</replaceable></literal></term>
117 <term><literal>ADD PUBLICATION <replaceable class="parameter">publication_name</replaceable></literal></term>
118 <term><literal>DROP PUBLICATION <replaceable class="parameter">publication_name</replaceable></literal></term>
119 <listitem>
120 <para>
121 These forms change the list of subscribed publications.
122 <literal>SET</literal>
123 replaces the entire list of publications with a new list,
124 <literal>ADD</literal> adds additional publications to the list of
125 publications, and <literal>DROP</literal> removes the publications from
126 the list of publications. We allow non-existent publications to be
127 specified in <literal>ADD</literal> and <literal>SET</literal> variants
128 so that users can add those later. See <xref linkend="sql-createsubscription"/>
129 for more information. By default, this command will also act like
130 <literal>REFRESH PUBLICATION</literal>.
131 </para>
132
133 <para>
134 <replaceable>publication_option</replaceable> specifies additional
135 options for this operation. The supported options are:
136
137 <variablelist>
138 <varlistentry>
139 <term><literal>refresh</literal> (<type>boolean</type>)</term>
140 <listitem>
141 <para>
142 When false, the command will not try to refresh table information.
143 <literal>REFRESH PUBLICATION</literal> should then be executed separately.
144 The default is <literal>true</literal>.
145 </para>
146 </listitem>
147 </varlistentry>
148 </variablelist>
149
150 Additionally, the options described under
151 <literal>REFRESH PUBLICATION</literal> may be specified, to control the
152 implicit refresh operation.
153 </para>
154 </listitem>
155 </varlistentry>
156
157 <varlistentry id="sql-altersubscription-params-refresh-publication">
158 <term><literal>REFRESH PUBLICATION</literal></term>
159 <listitem>
160 <para>
161 Fetch missing table information from publisher. This will start
162 replication of tables that were added to the subscribed-to publications
163 since <link linkend="sql-createsubscription">
164 <command>CREATE SUBSCRIPTION</command></link> or
165 the last invocation of <command>REFRESH PUBLICATION</command>.
166 </para>
167
168 <para>
169 <replaceable>refresh_option</replaceable> specifies additional options for the
170 refresh operation. The supported options are:
171
172 <variablelist>
173 <varlistentry>
174 <term><literal>copy_data</literal> (<type>boolean</type>)</term>
175 <listitem>
176 <para>
177 Specifies whether to copy pre-existing data in the publications
178 that are being subscribed to when the replication starts.
179 The default is <literal>true</literal>.
180 </para>
181 <para>
182 Previously subscribed tables are not copied, even if a table's row
183 filter <literal>WHERE</literal> clause has since been modified.
184 </para>
185 <para>
186 See <xref linkend="sql-createsubscription-notes"/> for details of
187 how <literal>copy_data = true</literal> can interact with the
188 <link linkend="sql-createsubscription-params-with-origin"><literal>origin</literal></link>
189 parameter.
190 </para>
191 <para>
192 See the
193 <link linkend="sql-createsubscription-params-with-binary"><literal>binary</literal></link>
194 parameter of <command>CREATE SUBSCRIPTION</command> for details about
195 copying pre-existing data in binary format.
196 </para>
197 </listitem>
198 </varlistentry>
199 </variablelist></para>
200 </listitem>
201 </varlistentry>
202
203 <varlistentry id="sql-altersubscription-params-enable">
204 <term><literal>ENABLE</literal></term>
205 <listitem>
206 <para>
207 Enables a previously disabled subscription, starting the logical
208 replication worker at the end of the transaction.
209 </para>
210 </listitem>
211 </varlistentry>
212
213 <varlistentry id="sql-altersubscription-params-disable">
214 <term><literal>DISABLE</literal></term>
215 <listitem>
216 <para>
217 Disables a running subscription, stopping the logical replication
218 worker at the end of the transaction.
219 </para>
220 </listitem>
221 </varlistentry>
222
223 <varlistentry id="sql-altersubscription-params-set">
224 <term><literal>SET ( <replaceable class="parameter">subscription_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
225 <listitem>
226 <para>
227 This clause alters parameters originally set by
228 <xref linkend="sql-createsubscription"/>. See there for more
229 information. The parameters that can be altered are
230 <link linkend="sql-createsubscription-params-with-slot-name"><literal>slot_name</literal></link>,
231 <link linkend="sql-createsubscription-params-with-synchronous-commit"><literal>synchronous_commit</literal></link>,
232 <link linkend="sql-createsubscription-params-with-binary"><literal>binary</literal></link>,
233 <link linkend="sql-createsubscription-params-with-streaming"><literal>streaming</literal></link>,
234 <link linkend="sql-createsubscription-params-with-disable-on-error"><literal>disable_on_error</literal></link>,
235 <link linkend="sql-createsubscription-params-with-password-required"><literal>password_required</literal></link>,
236 <link linkend="sql-createsubscription-params-with-run-as-owner"><literal>run_as_owner</literal></link>,
237 <link linkend="sql-createsubscription-params-with-origin"><literal>origin</literal></link>,
238 <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>, and
239 <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>.
240 Only a superuser can set <literal>password_required = false</literal>.
241 </para>
242
243 <para>
244 When altering the
245 <link linkend="sql-createsubscription-params-with-slot-name"><literal>slot_name</literal></link>,
246 the <literal>failover</literal> and <literal>two_phase</literal> property
247 values of the named slot may differ from the counterpart
248 <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>
249 and <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
250 parameters specified in the subscription. When creating the slot, ensure
251 the slot properties <literal>failover</literal> and <literal>two_phase</literal>
252 match their counterpart parameters of the subscription.
253 Otherwise, the slot on the publisher may behave differently from what these
254 subscription options say: for example, the slot on the publisher could either be
255 synced to the standbys even when the subscription's
256 <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>
257 option is disabled or could be disabled for sync
258 even when the subscription's
259 <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>
260 option is enabled.
261 </para>
262
263 <para>
264 The <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>
265 and <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
266 parameters can only be altered when the subscription is disabled.
267 </para>
268
269 <para>
270 When altering <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
271 from <literal>true</literal> to <literal>false</literal>, the backend
272 process reports an error if any prepared transactions done by the
273 logical replication worker (from when <literal>two_phase</literal>
274 parameter was still <literal>true</literal>) are found. You can resolve
275 prepared transactions on the publisher node, or manually roll them back
276 on the subscriber, and then try again. The transactions prepared by
277 logical replication worker corresponding to a particular subscription have
278 the following pattern: <quote><literal>pg_gid_%u_%u</literal></quote>
279 (parameters: subscription <parameter>oid</parameter>, remote transaction id <parameter>xid</parameter>).
280 To resolve such transactions manually, you need to roll back all
281 the prepared transactions with corresponding subscription IDs in their
282 names. Applications can check
283 <link linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link>
284 to find the required prepared transactions. After the <literal>two_phase</literal>
285 option is changed from <literal>true</literal> to <literal>false</literal>,
286 the publisher will replicate the transactions again when they are committed.
287 </para>
288 </listitem>
289 </varlistentry>
290
291 <varlistentry id="sql-altersubscription-params-skip">
292 <term><literal>SKIP ( <replaceable class="parameter">skip_option</replaceable> = <replaceable class="parameter">value</replaceable> )</literal></term>
293 <listitem>
294 <para>
295 Skips applying all changes of the remote transaction. If incoming data
296 violates any constraints, logical replication will stop until it is
297 resolved. By using the <command>ALTER SUBSCRIPTION ... SKIP</command> command,
298 the logical replication worker skips all data modification changes within
299 the transaction. This option has no effect on the transactions that are
300 already prepared by enabling
301 <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>
302 on the subscriber.
303 After the logical replication worker successfully skips the transaction or
304 finishes a transaction, the LSN (stored in
305 <structname>pg_subscription</structname>.<structfield>subskiplsn</structfield>)
306 is cleared. See <xref linkend="logical-replication-conflicts"/> for
307 the details of logical replication conflicts.
308 </para>
309
310 <para>
311 <replaceable>skip_option</replaceable> specifies options for this operation.
312 The supported option is:
313
314 <variablelist>
315 <varlistentry>
316 <term><literal>lsn</literal> (<type>pg_lsn</type>)</term>
317 <listitem>
318 <para>
319 Specifies the finish LSN of the remote transaction whose changes
320 are to be skipped by the logical replication worker. The finish LSN
321 is the LSN at which the transaction is either committed or prepared.
322 Skipping individual subtransactions is not supported. Setting
323 <literal>NONE</literal> resets the LSN.
324 </para>
325 </listitem>
326 </varlistentry>
327 </variablelist></para>
328 </listitem>
329 </varlistentry>
330
331 <varlistentry id="sql-altersubscription-params-new-owner">
332 <term><replaceable class="parameter">new_owner</replaceable></term>
333 <listitem>
334 <para>
335 The user name of the new owner of the subscription.
336 </para>
337 </listitem>
338 </varlistentry>
339
340 <varlistentry id="sql-altersubscription-params-new-name">
341 <term><replaceable class="parameter">new_name</replaceable></term>
342 <listitem>
343 <para>
344 The new name for the subscription.
345 </para>
346 </listitem>
347 </varlistentry>
348 </variablelist>
349
350 <para>
351 When specifying a parameter of type <type>boolean</type>, the
352 <literal>=</literal> <replaceable class="parameter">value</replaceable>
353 part can be omitted, which is equivalent to
354 specifying <literal>TRUE</literal>.
355 </para>
356 </refsect1>
357
358 <refsect1>
359 <title>Examples</title>
360
361 <para>
362 Change the publication subscribed by a subscription to
363 <literal>insert_only</literal>:
364 <programlisting>
365 ALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;
366 </programlisting>
367 </para>
368
369 <para>
370 Disable (stop) the subscription:
371 <programlisting>
372 ALTER SUBSCRIPTION mysub DISABLE;
373 </programlisting></para>
374 </refsect1>
375
376 <refsect1>
377 <title>Compatibility</title>
378
379 <para>
380 <command>ALTER SUBSCRIPTION</command> is a <productname>PostgreSQL</productname>
381 extension.
382 </para>
383 </refsect1>
384
385 <refsect1>
386 <title>See Also</title>
387
388 <simplelist type="inline">
389 <member><xref linkend="sql-createsubscription"/></member>
390 <member><xref linkend="sql-dropsubscription"/></member>
391 <member><xref linkend="sql-createpublication"/></member>
392 <member><xref linkend="sql-alterpublication"/></member>
393 </simplelist>
394 </refsect1>
395 </refentry>