Debian Codegraph @

doc/src/sgml/ref/alter_publication.sgml

raw
1 <!--
2 doc/src/sgml/ref/alter_publication.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-alterpublication">
7 <indexterm zone="sql-alterpublication">
8 <primary>ALTER PUBLICATION</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>ALTER PUBLICATION</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>ALTER PUBLICATION</refname>
19 <refpurpose>change the definition of a publication</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD <replaceable class="parameter">publication_object</replaceable> [, ...]
25 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET <replaceable class="parameter">publication_object</replaceable> [, ...]
26 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP <replaceable class="parameter">publication_drop_object</replaceable> [, ...]
27 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
28 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
29 ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
30
31 <phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
32
33 TABLE <replaceable class="parameter">table_and_columns</replaceable> [, ... ]
34 TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
35
36 <phrase>and <replaceable class="parameter">publication_drop_object</replaceable> is one of:</phrase>
37
38 TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
39 TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
40
41 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
42
43 [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ]
44 </synopsis>
45 </refsynopsisdiv>
46
47 <refsect1>
48 <title>Description</title>
49
50 <para>
51 The command <command>ALTER PUBLICATION</command> can change the attributes
52 of a publication.
53 </para>
54
55 <para>
56 The first three variants change which tables/schemas are part of the
57 publication. The <literal>SET</literal> clause will replace the list of
58 tables/schemas in the publication with the specified list; the existing
59 tables/schemas that were present in the publication will be removed. The
60 <literal>ADD</literal> and <literal>DROP</literal> clauses will add and
61 remove one or more tables/schemas from the publication. Note that adding
62 tables/schemas to a publication that is already subscribed to will require an
63 <link linkend="sql-altersubscription-params-refresh-publication">
64 <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal></link> action on the
65 subscribing side in order to become effective. Note also that
66 <literal>DROP TABLES IN SCHEMA</literal> will not drop any schema tables
67 that were specified using
68 <link linkend="sql-createpublication-params-for-table"><literal>FOR TABLE</literal></link>/
69 <literal>ADD TABLE</literal>.
70 </para>
71
72 <para>
73 The fourth variant of this command listed in the synopsis can change
74 all of the publication properties specified in
75 <xref linkend="sql-createpublication"/>. Properties not mentioned in the
76 command retain their previous settings.
77 </para>
78
79 <para>
80 The remaining variants change the owner and the name of the publication.
81 </para>
82
83 <para>
84 You must own the publication to use <command>ALTER PUBLICATION</command>.
85 Adding a table to a publication additionally requires owning that table.
86 The <literal>ADD TABLES IN SCHEMA</literal> and
87 <literal>SET TABLES IN SCHEMA</literal> to a publication requires the
88 invoking user to be a superuser.
89 To alter the owner, you must be able to <literal>SET ROLE</literal> to the
90 new owning role, and that role must have <literal>CREATE</literal>
91 privilege on the database.
92 Also, the new owner of a
93 <link linkend="sql-createpublication-params-for-all-tables"><literal>FOR ALL TABLES</literal></link>
94 or <link linkend="sql-createpublication-params-for-tables-in-schema"><literal>FOR TABLES IN SCHEMA</literal></link>
95 publication must be a superuser. However, a superuser can
96 change the ownership of a publication regardless of these restrictions.
97 </para>
98
99 <para>
100 Adding/Setting any schema when the publication also publishes a table with a
101 column list, and vice versa is not supported.
102 </para>
103 </refsect1>
104
105 <refsect1>
106 <title>Parameters</title>
107
108 <variablelist>
109 <varlistentry>
110 <term><replaceable class="parameter">name</replaceable></term>
111 <listitem>
112 <para>
113 The name of an existing publication whose definition is to be altered.
114 </para>
115 </listitem>
116 </varlistentry>
117
118 <varlistentry>
119 <term><replaceable class="parameter">table_name</replaceable></term>
120 <listitem>
121 <para>
122 Name of an existing table. If <literal>ONLY</literal> is specified before the
123 table name, only that table is affected. If <literal>ONLY</literal> is not
124 specified, the table and all its descendant tables (if any) are
125 affected. Optionally, <literal>*</literal> can be specified after the table
126 name to explicitly indicate that descendant tables are included.
127 </para>
128
129 <para>
130 Optionally, a column list can be specified. See <xref
131 linkend="sql-createpublication"/> for details. Note that a subscription
132 having several publications in which the same table has been published
133 with different column lists is not supported. See
134 <xref linkend="logical-replication-col-list-combining"/> for details of
135 potential problems when altering column lists.
136 </para>
137
138 <para>
139 If the optional <literal>WHERE</literal> clause is specified, rows for
140 which the <replaceable class="parameter">expression</replaceable>
141 evaluates to false or null will not be published. Note that parentheses
142 are required around the expression. The
143 <replaceable class="parameter">expression</replaceable> is evaluated with
144 the role used for the replication connection.
145 </para>
146 </listitem>
147 </varlistentry>
148
149 <varlistentry>
150 <term><replaceable class="parameter">schema_name</replaceable></term>
151 <listitem>
152 <para>
153 Name of an existing schema.
154 </para>
155 </listitem>
156 </varlistentry>
157
158 <varlistentry>
159 <term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
160 <listitem>
161 <para>
162 This clause alters publication parameters originally set by
163 <xref linkend="sql-createpublication"/>. See there for more information.
164 </para>
165 <caution>
166 <para>
167 Altering the <literal>publish_via_partition_root</literal> parameter can
168 lead to data loss or duplication at the subscriber because it changes
169 the identity and schema of the published tables. Note this happens only
170 when a partition root table is specified as the replication target.
171 </para>
172 <para>
173 This problem can be avoided by refraining from modifying partition leaf
174 tables after the <command>ALTER PUBLICATION ... SET</command> until the
175 <link linkend="sql-altersubscription"><command>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</command></link>
176 is executed and by only refreshing using the <literal>copy_data = off</literal>
177 option.
178 </para>
179 </caution>
180 </listitem>
181 </varlistentry>
182
183 <varlistentry>
184 <term><replaceable class="parameter">new_owner</replaceable></term>
185 <listitem>
186 <para>
187 The user name of the new owner of the publication.
188 </para>
189 </listitem>
190 </varlistentry>
191
192 <varlistentry>
193 <term><replaceable class="parameter">new_name</replaceable></term>
194 <listitem>
195 <para>
196 The new name for the publication.
197 </para>
198 </listitem>
199 </varlistentry>
200 </variablelist>
201 </refsect1>
202
203 <refsect1>
204 <title>Examples</title>
205
206 <para>
207 Change the publication to publish only deletes and updates:
208 <programlisting>
209 ALTER PUBLICATION noinsert SET (publish = 'update, delete');
210 </programlisting>
211 </para>
212
213 <para>
214 Add some tables to the publication:
215 <programlisting>
216 ALTER PUBLICATION mypublication ADD TABLE users (user_id, firstname), departments;
217 </programlisting></para>
218
219 <para>
220 Change the set of columns published for a table:
221 <programlisting>
222 ALTER PUBLICATION mypublication SET TABLE users (user_id, firstname, lastname), TABLE departments;
223 </programlisting></para>
224
225 <para>
226 Add schemas <structname>marketing</structname> and
227 <structname>sales</structname> to the publication
228 <structname>sales_publication</structname>:
229 <programlisting>
230 ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA marketing, sales;
231 </programlisting>
232 </para>
233
234 <para>
235 Add tables <structname>users</structname>,
236 <structname>departments</structname> and schema
237 <structname>production</structname> to the publication
238 <structname>production_publication</structname>:
239 <programlisting>
240 ALTER PUBLICATION production_publication ADD TABLE users, departments, TABLES IN SCHEMA production;
241 </programlisting></para>
242 </refsect1>
243
244 <refsect1>
245 <title>Compatibility</title>
246
247 <para>
248 <command>ALTER PUBLICATION</command> is a <productname>PostgreSQL</productname>
249 extension.
250 </para>
251 </refsect1>
252
253 <refsect1>
254 <title>See Also</title>
255
256 <simplelist type="inline">
257 <member><xref linkend="sql-createpublication"/></member>
258 <member><xref linkend="sql-droppublication"/></member>
259 <member><xref linkend="sql-createsubscription"/></member>
260 <member><xref linkend="sql-altersubscription"/></member>
261 </simplelist>
262 </refsect1>
263 </refentry>