1<!--
2doc/src/sgml/ref/create_publication.sgml
3PostgreSQL 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>
24CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
25    [ FOR TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
26      | FOR ALL TABLES ]
27    [ WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
28
29</synopsis>
30 </refsynopsisdiv>
31
32 <refsect1>
33  <title>Description</title>
34
35  <para>
36   <command>CREATE PUBLICATION</command> adds a new publication
37   into the current database.  The publication name must be distinct from
38   the name of any existing publication in the current database.
39  </para>
40
41  <para>
42   A publication is essentially a group of tables whose data changes are
43   intended to be replicated through logical replication.  See
44   <xref linkend="logical-replication-publication"> for details about how
45   publications fit into the logical replication setup.
46   </para>
47 </refsect1>
48
49 <refsect1>
50  <title>Parameters</title>
51
52  <variablelist>
53   <varlistentry>
54    <term><replaceable class="parameter">name</replaceable></term>
55    <listitem>
56     <para>
57      The name of the new publication.
58     </para>
59    </listitem>
60   </varlistentry>
61
62   <varlistentry>
63    <term><literal>FOR TABLE</literal></term>
64    <listitem>
65     <para>
66      Specifies a list of tables to add to the publication.  If
67      <literal>ONLY</> is specified before the table name, only
68      that table is added to the publication.  If <literal>ONLY</> is not
69      specified, the table and all its descendant tables (if any) are added.
70      Optionally, <literal>*</> can be specified after the table name to
71      explicitly indicate that descendant tables are included.
72     </para>
73
74     <para>
75      Only persistent base tables can be part of a publication.  Temporary
76      tables, unlogged tables, foreign tables, materialized views, regular
77      views, and partitioned tables cannot be part of a publication.  To
78      replicate a partitioned table, add the individual partitions to the
79      publication.
80     </para>
81    </listitem>
82   </varlistentry>
83
84   <varlistentry>
85    <term><literal>FOR ALL TABLES</literal></term>
86    <listitem>
87     <para>
88      Marks the publication as one that replicates changes for all tables in
89      the database, including tables created in the future.
90     </para>
91    </listitem>
92   </varlistentry>
93
94   <varlistentry>
95    <term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
96    <listitem>
97     <para>
98      This clause specifies optional parameters for a publication.  The
99      following parameters are supported:
100
101      <variablelist>
102       <varlistentry>
103        <term><literal>publish</literal> (<type>string</type>)</term>
104        <listitem>
105         <para>
106          This parameter determines which DML operations will be published by
107          the new publication to the subscribers.  The value is
108          comma-separated list of operations.  The allowed operations are
109          <literal>insert</literal>, <literal>update</literal>, and
110          <literal>delete</literal>.  The default is to publish all actions,
111          and so the default value for this option is
112          <literal>'insert, update, delete'</literal>.
113         </para>
114        </listitem>
115       </varlistentry>
116      </variablelist>
117
118     </para>
119    </listitem>
120   </varlistentry>
121
122  </variablelist>
123 </refsect1>
124
125 <refsect1>
126  <title>Notes</title>
127
128  <para>
129   If neither <literal>FOR TABLE</literal> nor <literal>FOR ALL
130   TABLES</literal> is specified, then the publication starts out with an
131   empty set of tables.  That is useful if tables are to be added later.
132  </para>
133
134  <para>
135   The creation of a publication does not start replication.  It only defines
136   a grouping and filtering logic for future subscribers.
137  </para>
138
139  <para>
140   To create a publication, the invoking user must have the
141   <literal>CREATE</> privilege for the current database.
142   (Of course, superusers bypass this check.)
143  </para>
144
145  <para>
146   To add a table to a publication, the invoking user must have ownership
147   rights on the table.  The <command>FOR ALL TABLES</command> clause requires
148   the invoking user to be a superuser.
149  </para>
150
151  <para>
152   The tables added to a publication that publishes <command>UPDATE</command>
153   and/or <command>DELETE</command> operations must have
154   <literal>REPLICA IDENTITY</> defined.  Otherwise those operations will be
155   disallowed on those tables.
156  </para>
157
158  <para>
159   For an <command>INSERT ... ON CONFLICT</> command, the publication will
160   publish the operation that actually results from the command.  So depending
161   of the outcome, it may be published as either <command>INSERT</command> or
162   <command>UPDATE</command>, or it may not be published at all.
163  </para>
164
165  <para>
166   <command>COPY ... FROM</command> commands are published
167   as <command>INSERT</command> operations.
168  </para>
169
170  <para>
171   <command>TRUNCATE</command> and <acronym>DDL</acronym> operations
172   are not published.
173  </para>
174 </refsect1>
175
176 <refsect1>
177  <title>Examples</title>
178
179  <para>
180   Create a publication that publishes all changes in two tables:
181<programlisting>
182CREATE PUBLICATION mypublication FOR TABLE users, departments;
183</programlisting>
184  </para>
185
186  <para>
187   Create a publication that publishes all changes in all tables:
188<programlisting>
189CREATE PUBLICATION alltables FOR ALL TABLES;
190</programlisting>
191  </para>
192
193  <para>
194   Create a publication that only publishes <command>INSERT</command>
195   operations in one table:
196<programlisting>
197CREATE PUBLICATION insert_only FOR TABLE mydata
198    WITH (publish = 'insert');
199</programlisting></para>
200 </refsect1>
201
202 <refsect1>
203  <title>Compatibility</title>
204
205  <para>
206   <command>CREATE PUBLICATION</command> is a <productname>PostgreSQL</>
207   extension.
208  </para>
209 </refsect1>
210
211 <refsect1>
212  <title>See Also</title>
213
214  <simplelist type="inline">
215   <member><xref linkend="sql-alterpublication"></member>
216   <member><xref linkend="sql-droppublication"></member>
217  </simplelist>
218 </refsect1>
219</refentry>
220