Debian Codegraph @

doc/src/sgml/ref/create_publication.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_publication.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createpublication">
7 <indexterm zone="sql-createpublication">
8 <primary>CREATE PUBLICATION</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE PUBLICATION</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE PUBLICATION</refname>
19 <refpurpose>define a new publication</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
25 [ FOR ALL TABLES
26 | FOR <replaceable class="parameter">publication_object</replaceable> [, ... ] ]
27 [ WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
28
29 <phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
30
31 TABLE <replaceable class="parameter">table_and_columns</replaceable> [, ... ]
32 TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
33
34 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
35
36 [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ]
37 </synopsis>
38 </refsynopsisdiv>
39
40 <refsect1>
41 <title>Description</title>
42
43 <para>
44 <command>CREATE PUBLICATION</command> adds a new publication
45 into the current database. The publication name must be distinct from
46 the name of any existing publication in the current database.
47 </para>
48
49 <para>
50 A publication is essentially a group of tables whose data changes are
51 intended to be replicated through logical replication. See
52 <xref linkend="logical-replication-publication"/> for details about how
53 publications fit into the logical replication setup.
54 </para>
55 </refsect1>
56
57 <refsect1>
58 <title>Parameters</title>
59
60 <variablelist>
61 <varlistentry id="sql-createpublication-params-name">
62 <term><replaceable class="parameter">name</replaceable></term>
63 <listitem>
64 <para>
65 The name of the new publication.
66 </para>
67 </listitem>
68 </varlistentry>
69
70 <varlistentry id="sql-createpublication-params-for-table">
71 <term><literal>FOR TABLE</literal></term>
72 <listitem>
73 <para>
74 Specifies a list of tables to add to the publication. If
75 <literal>ONLY</literal> is specified before the table name, only
76 that table is added to the publication. If <literal>ONLY</literal> is not
77 specified, the table and all its descendant tables (if any) are added.
78 Optionally, <literal>*</literal> can be specified after the table name to
79 explicitly indicate that descendant tables are included.
80 This does not apply to a partitioned table, however. The partitions of
81 a partitioned table are always implicitly considered part of the
82 publication, so they are never explicitly added to the publication.
83 </para>
84
85 <para>
86 If the optional <literal>WHERE</literal> clause is specified, it defines a
87 <firstterm>row filter</firstterm> expression. Rows for
88 which the <replaceable class="parameter">expression</replaceable>
89 evaluates to false or null will not be published. Note that parentheses
90 are required around the expression. It has no effect on
91 <literal>TRUNCATE</literal> commands.
92 </para>
93
94 <para>
95 When a column list is specified, only the named columns are replicated.
96 The column list can contain stored generated columns as well. If the
97 column list is omitted, the publication will replicate all non-generated
98 columns (including any added in the future) by default. Stored generated
99 columns can also be replicated if <literal>publish_generated_columns</literal>
100 is set to <literal>stored</literal>. Specifying a column list has no
101 effect on <literal>TRUNCATE</literal> commands. See
102 <xref linkend="logical-replication-col-lists"/> for details about column
103 lists.
104 </para>
105
106 <para>
107 Only persistent base tables and partitioned tables can be part of a
108 publication. Temporary tables, unlogged tables, foreign tables,
109 materialized views, and regular views cannot be part of a publication.
110 </para>
111
112 <para>
113 Specifying a column list when the publication also publishes
114 <literal>FOR TABLES IN SCHEMA</literal> is not supported.
115 </para>
116
117 <para>
118 When a partitioned table is added to a publication, all of its existing
119 and future partitions are implicitly considered to be part of the
120 publication. So, even operations that are performed directly on a
121 partition are also published via publications that its ancestors are
122 part of.
123 </para>
124 </listitem>
125 </varlistentry>
126
127 <varlistentry id="sql-createpublication-params-for-all-tables">
128 <term><literal>FOR ALL TABLES</literal></term>
129 <listitem>
130 <para>
131 Marks the publication as one that replicates changes for all tables in
132 the database, including tables created in the future.
133 </para>
134 </listitem>
135 </varlistentry>
136
137 <varlistentry id="sql-createpublication-params-for-tables-in-schema">
138 <term><literal>FOR TABLES IN SCHEMA</literal></term>
139 <listitem>
140 <para>
141 Marks the publication as one that replicates changes for all tables in
142 the specified list of schemas, including tables created in the future.
143 </para>
144
145 <para>
146 Specifying a schema when the publication also publishes a table with a
147 column list is not supported.
148 </para>
149
150 <para>
151 Only persistent base tables and partitioned tables present in the schema
152 will be included as part of the publication. Temporary tables, unlogged
153 tables, foreign tables, materialized views, and regular views from the
154 schema will not be part of the publication.
155 </para>
156
157 <para>
158 When a partitioned table is published via a schema-level publication, all
159 of its existing and future partitions are implicitly considered to be part of the
160 publication, regardless of whether they are from the publication schema or not.
161 So, even operations that are performed directly on a
162 partition are also published via publications that its ancestors are
163 part of.
164 </para>
165 </listitem>
166 </varlistentry>
167
168 <varlistentry id="sql-createpublication-params-with">
169 <term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
170 <listitem>
171 <para>
172 This clause specifies optional parameters for a publication. The
173 following parameters are supported:
174
175 <variablelist>
176 <varlistentry id="sql-createpublication-params-with-publish">
177 <term><literal>publish</literal> (<type>string</type>)</term>
178 <listitem>
179 <para>
180 This parameter determines which DML operations will be published by
181 the new publication to the subscribers. The value is a
182 comma-separated list of operations. The allowed operations are
183 <literal>insert</literal>, <literal>update</literal>,
184 <literal>delete</literal>, and <literal>truncate</literal>.
185 The default is to publish all actions,
186 and so the default value for this option is
187 <literal>'insert, update, delete, truncate'</literal>.
188 </para>
189 <para>
190 This parameter only affects DML operations. In particular, the initial
191 data synchronization (see <xref linkend="logical-replication-snapshot"/>)
192 for logical replication does not take this parameter into account when
193 copying existing table data.
194 </para>
195 </listitem>
196 </varlistentry>
197
198 <varlistentry id="sql-createpublication-params-with-publish-generated-columns">
199 <term><literal>publish_generated_columns</literal> (<type>enum</type>)</term>
200 <listitem>
201 <para>
202 Specifies whether the generated columns present in the tables
203 associated with the publication should be replicated. Possible values
204 are <literal>none</literal> and <literal>stored</literal>.
205 </para>
206
207 <para>
208 The default is <literal>none</literal> meaning the generated
209 columns present in the tables associated with publication will not be
210 replicated.
211 </para>
212
213 <para>
214 If set to <literal>stored</literal>, the stored generated columns
215 present in the tables associated with publication will be replicated.
216 </para>
217
218 <note>
219 <para>
220 If the subscriber is from a release prior to 18, then initial table
221 synchronization won't copy generated columns even if the parameter
222 <literal>publish_generated_columns</literal> is <literal>stored</literal>
223 in the publisher.
224 </para>
225 </note>
226
227 <para>
228 See <xref linkend="logical-replication-gencols"/> for more details about
229 logical replication of generated columns.
230 </para>
231 </listitem>
232 </varlistentry>
233
234 <varlistentry id="sql-createpublication-params-with-publish-via-partition-root">
235 <term><literal>publish_via_partition_root</literal> (<type>boolean</type>)</term>
236 <listitem>
237 <para>
238 This parameter controls how changes to a partitioned table (or any of
239 its partitions) are published. When set to <literal>true</literal>,
240 changes are published using the identity and schema of the
241 root partitioned table. When set to <literal>false</literal> (the
242 default), changes are published using the identity and schema of the
243 individual partitions where the changes actually occurred. Enabling
244 this option allows the changes to be replicated into a
245 non-partitioned table or into a partitioned table whose partition
246 structure differs from that of the publisher.
247 </para>
248
249 <para>
250 There can be a case where a subscription combines multiple
251 publications. If a partitioned table is published by any
252 subscribed publications which set
253 <literal>publish_via_partition_root = true</literal>, changes on this
254 partitioned table (or on its partitions) will be published using
255 the identity and schema of this partitioned table rather than
256 that of the individual partitions.
257 </para>
258
259 <para>
260 This parameter also affects how row filters and column lists are
261 chosen for partitions; see below for details.
262 </para>
263
264 <para>
265 If this is enabled, <literal>TRUNCATE</literal> operations performed
266 directly on partitions are not replicated.
267 </para>
268 </listitem>
269 </varlistentry>
270 </variablelist></para>
271 </listitem>
272 </varlistentry>
273
274 </variablelist>
275
276 <para>
277 When specifying a parameter of type <type>boolean</type>, the
278 <literal>=</literal> <replaceable class="parameter">value</replaceable>
279 part can be omitted, which is equivalent to
280 specifying <literal>TRUE</literal>.
281 </para>
282 </refsect1>
283
284 <refsect1>
285 <title>Notes</title>
286
287 <para>
288 If <literal>FOR TABLE</literal>, <literal>FOR ALL TABLES</literal> or
289 <literal>FOR TABLES IN SCHEMA</literal> are not specified, then the
290 publication starts out with an empty set of tables. That is useful if
291 tables or schemas are to be added later.
292 </para>
293
294 <para>
295 The creation of a publication does not start replication. It only defines
296 a grouping and filtering logic for future subscribers.
297 </para>
298
299 <para>
300 To create a publication, the invoking user must have the
301 <literal>CREATE</literal> privilege for the current database.
302 (Of course, superusers bypass this check.)
303 </para>
304
305 <para>
306 To add a table to a publication, the invoking user must have ownership
307 rights on the table. The <command>FOR ALL TABLES</command> and
308 <command>FOR TABLES IN SCHEMA</command> clauses require the invoking
309 user to be a superuser.
310 </para>
311
312 <para>
313 The tables added to a publication that publishes <command>UPDATE</command>
314 and/or <command>DELETE</command> operations must have
315 <literal>REPLICA IDENTITY</literal> defined. Otherwise those operations will be
316 disallowed on those tables.
317 </para>
318
319 <para>
320 Any column list must include the <literal>REPLICA IDENTITY</literal> columns
321 in order for <command>UPDATE</command> or <command>DELETE</command>
322 operations to be published. There are no column list restrictions if the
323 publication publishes only <command>INSERT</command> operations.
324 </para>
325
326 <para>
327 A row filter expression (i.e., the <literal>WHERE</literal> clause) must contain only
328 columns that are covered by the <literal>REPLICA IDENTITY</literal>, in
329 order for <command>UPDATE</command> and <command>DELETE</command> operations
330 to be published. For publication of <command>INSERT</command> operations,
331 any column may be used in the <literal>WHERE</literal> expression. The
332 row filter allows simple expressions that don't have
333 user-defined functions, user-defined operators, user-defined types,
334 user-defined collations, non-immutable built-in functions, or references to
335 system columns.
336 </para>
337
338 <para>
339 The generated columns that are part of <literal>REPLICA IDENTITY</literal>
340 must be published explicitly either by listing them in the column list or
341 by enabling the <literal>publish_generated_columns</literal> option, in
342 order for <command>UPDATE</command> and <command>DELETE</command> operations
343 to be published.
344 </para>
345
346 <para>
347 The row filter on a table becomes redundant if
348 <literal>FOR TABLES IN SCHEMA</literal> is specified and the table
349 belongs to the referred schema.
350 </para>
351
352 <para>
353 For published partitioned tables, the row filter for each
354 partition is taken from the published partitioned table if the
355 publication parameter <literal>publish_via_partition_root</literal> is true,
356 or from the partition itself if it is false (the default).
357 See <xref linkend="logical-replication-row-filter"/> for details about row
358 filters.
359 Similarly, for published partitioned tables, the column list for each
360 partition is taken from the published partitioned table if the
361 publication parameter <literal>publish_via_partition_root</literal> is true,
362 or from the partition itself if it is false.
363 </para>
364
365 <para>
366 For an <command>INSERT ... ON CONFLICT</command> command, the publication will
367 publish the operation that results from the command. Depending
368 on the outcome, it may be published as either <command>INSERT</command> or
369 <command>UPDATE</command>, or it may not be published at all.
370 </para>
371
372 <para>
373 For a <command>MERGE</command> command, the publication will publish an
374 <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command>
375 for each row inserted, updated, or deleted.
376 </para>
377
378 <para>
379 <command>ATTACH</command>ing a table into a partition tree whose root is
380 published using a publication with <literal>publish_via_partition_root</literal>
381 set to <literal>true</literal> does not result in the table's existing contents
382 being replicated.
383 </para>
384
385 <para>
386 <command>COPY ... FROM</command> commands are published
387 as <command>INSERT</command> operations.
388 </para>
389
390 <para>
391 <acronym>DDL</acronym> operations are not published.
392 </para>
393
394 <para>
395 The <literal>WHERE</literal> clause expression is executed with the role used
396 for the replication connection.
397 </para>
398 </refsect1>
399
400 <refsect1>
401 <title>Examples</title>
402
403 <para>
404 Create a publication that publishes all changes in two tables:
405 <programlisting>
406 CREATE PUBLICATION mypublication FOR TABLE users, departments;
407 </programlisting>
408 </para>
409
410 <para>
411 Create a publication that publishes all changes from active departments:
412 <programlisting>
413 CREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE);
414 </programlisting>
415 </para>
416
417 <para>
418 Create a publication that publishes all changes in all tables:
419 <programlisting>
420 CREATE PUBLICATION alltables FOR ALL TABLES;
421 </programlisting>
422 </para>
423
424 <para>
425 Create a publication that only publishes <command>INSERT</command>
426 operations in one table:
427 <programlisting>
428 CREATE PUBLICATION insert_only FOR TABLE mydata
429 WITH (publish = 'insert');
430 </programlisting>
431 </para>
432
433 <para>
434 Create a publication that publishes all changes for tables
435 <structname>users</structname>, <structname>departments</structname> and
436 all changes for all the tables present in the schema
437 <structname>production</structname>:
438 <programlisting>
439 CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES IN SCHEMA production;
440 </programlisting>
441 </para>
442
443 <para>
444 Create a publication that publishes all changes for all the tables present in
445 the schemas <structname>marketing</structname> and
446 <structname>sales</structname>:
447 <programlisting>
448 CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;
449 </programlisting></para>
450
451 <para>
452 Create a publication that publishes all changes for table <structname>users</structname>,
453 but replicates only columns <structname>user_id</structname> and
454 <structname>firstname</structname>:
455 <programlisting>
456 CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);
457 </programlisting></para>
458 </refsect1>
459
460 <refsect1>
461 <title>Compatibility</title>
462
463 <para>
464 <command>CREATE PUBLICATION</command> is a <productname>PostgreSQL</productname>
465 extension.
466 </para>
467 </refsect1>
468
469 <refsect1>
470 <title>See Also</title>
471
472 <simplelist type="inline">
473 <member><xref linkend="sql-alterpublication"/></member>
474 <member><xref linkend="sql-droppublication"/></member>
475 <member><xref linkend="sql-createsubscription"/></member>
476 <member><xref linkend="sql-altersubscription"/></member>
477 </simplelist>
478 </refsect1>
479 </refentry>