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