doc/src/sgml/ref/create_publication.sgml

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