codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE PUBLICATION</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createprocedure.html" title="CREATE PROCEDURE" /><link rel="next" href="sql-createrole.html" title="CREATE ROLE" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE PUBLICATION</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createprocedure.html" title="CREATE PROCEDURE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createrole.html" title="CREATE ROLE">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATEPUBLICATION"><div class="titlepage"></div><a id="id-1.9.3.77.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE PUBLICATION</span></h2><p>CREATE PUBLICATION — define a new publication</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3CREATE PUBLICATION <em class="replaceable"><code>name</code></em>4 [ FOR ALL TABLES5 | FOR <em class="replaceable"><code>publication_object</code></em> [, ... ] ]6 [ WITH ( <em class="replaceable"><code>publication_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) ]7 8<span class="phrase">where <em class="replaceable"><code>publication_object</code></em> is one of:</span>9 10 TABLE [ ONLY ] <em class="replaceable"><code>table_name</code></em> [ * ] [ ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) ] [ WHERE ( <em class="replaceable"><code>expression</code></em> ) ] [, ... ]11 TABLES IN SCHEMA { <em class="replaceable"><code>schema_name</code></em> | CURRENT_SCHEMA } [, ... ]12</pre></div><div class="refsect1" id="id-1.9.3.77.5"><h2>Description</h2><p>13 <code class="command">CREATE PUBLICATION</code> adds a new publication14 into the current database. The publication name must be distinct from15 the name of any existing publication in the current database.16 </p><p>17 A publication is essentially a group of tables whose data changes are18 intended to be replicated through logical replication. See19 <a class="xref" href="logical-replication-publication.html" title="31.1. Publication">Section 31.1</a> for details about how20 publications fit into the logical replication setup.21 </p></div><div class="refsect1" id="id-1.9.3.77.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt id="SQL-CREATEPUBLICATION-NAME"><span class="term"><em class="replaceable"><code>name</code></em></span> <a href="#SQL-CREATEPUBLICATION-NAME" class="id_link">#</a></dt><dd><p>22 The name of the new publication.23 </p></dd><dt id="SQL-CREATEPUBLICATION-FOR-TABLE"><span class="term"><code class="literal">FOR TABLE</code></span> <a href="#SQL-CREATEPUBLICATION-FOR-TABLE" class="id_link">#</a></dt><dd><p>24 Specifies a list of tables to add to the publication. If25 <code class="literal">ONLY</code> is specified before the table name, only26 that table is added to the publication. If <code class="literal">ONLY</code> is not27 specified, the table and all its descendant tables (if any) are added.28 Optionally, <code class="literal">*</code> can be specified after the table name to29 explicitly indicate that descendant tables are included.30 This does not apply to a partitioned table, however. The partitions of31 a partitioned table are always implicitly considered part of the32 publication, so they are never explicitly added to the publication.33 </p><p>34 If the optional <code class="literal">WHERE</code> clause is specified, it defines a35 <em class="firstterm">row filter</em> expression. Rows for36 which the <em class="replaceable"><code>expression</code></em>37 evaluates to false or null will not be published. Note that parentheses38 are required around the expression. It has no effect on39 <code class="literal">TRUNCATE</code> commands.40 </p><p>41 When a column list is specified, only the named columns are replicated.42 If no column list is specified, all columns of the table are replicated43 through this publication, including any columns added later. It has no44 effect on <code class="literal">TRUNCATE</code> commands. See45 <a class="xref" href="logical-replication-col-lists.html" title="31.4. Column Lists">Section 31.4</a> for details about column46 lists.47 </p><p>48 Only persistent base tables and partitioned tables can be part of a49 publication. Temporary tables, unlogged tables, foreign tables,50 materialized views, and regular views cannot be part of a publication.51 </p><p>52 Specifying a column list when the publication also publishes53 <code class="literal">FOR TABLES IN SCHEMA</code> is not supported.54 </p><p>55 When a partitioned table is added to a publication, all of its existing56 and future partitions are implicitly considered to be part of the57 publication. So, even operations that are performed directly on a58 partition are also published via publications that its ancestors are59 part of.60 </p></dd><dt id="SQL-CREATEPUBLICATION-FOR-ALL-TABLES"><span class="term"><code class="literal">FOR ALL TABLES</code></span> <a href="#SQL-CREATEPUBLICATION-FOR-ALL-TABLES" class="id_link">#</a></dt><dd><p>61 Marks the publication as one that replicates changes for all tables in62 the database, including tables created in the future.63 </p></dd><dt id="SQL-CREATEPUBLICATION-FOR-TABLES-IN-SCHEMA"><span class="term"><code class="literal">FOR TABLES IN SCHEMA</code></span> <a href="#SQL-CREATEPUBLICATION-FOR-TABLES-IN-SCHEMA" class="id_link">#</a></dt><dd><p>64 Marks the publication as one that replicates changes for all tables in65 the specified list of schemas, including tables created in the future.66 </p><p>67 Specifying a schema when the publication also publishes a table with a68 column list is not supported.69 </p><p>70 Only persistent base tables and partitioned tables present in the schema71 will be included as part of the publication. Temporary tables, unlogged72 tables, foreign tables, materialized views, and regular views from the73 schema will not be part of the publication.74 </p><p>75 When a partitioned table is published via schema level publication, all76 of its existing and future partitions are implicitly considered to be part of the77 publication, regardless of whether they are from the publication schema or not.78 So, even operations that are performed directly on a79 partition are also published via publications that its ancestors are80 part of.81 </p></dd><dt id="SQL-CREATEPUBLICATION-WITH"><span class="term"><code class="literal">WITH ( <em class="replaceable"><code>publication_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] )</code></span> <a href="#SQL-CREATEPUBLICATION-WITH" class="id_link">#</a></dt><dd><p>82 This clause specifies optional parameters for a publication. The83 following parameters are supported:84 85 </p><div class="variablelist"><dl class="variablelist"><dt id="SQL-CREATEPUBLICATION-WITH-PUBLISH"><span class="term"><code class="literal">publish</code> (<code class="type">string</code>)</span> <a href="#SQL-CREATEPUBLICATION-WITH-PUBLISH" class="id_link">#</a></dt><dd><p>86 This parameter determines which DML operations will be published by87 the new publication to the subscribers. The value is88 comma-separated list of operations. The allowed operations are89 <code class="literal">insert</code>, <code class="literal">update</code>,90 <code class="literal">delete</code>, and <code class="literal">truncate</code>.91 The default is to publish all actions,92 and so the default value for this option is93 <code class="literal">'insert, update, delete, truncate'</code>.94 </p><p>95 This parameter only affects DML operations. In particular, the initial96 data synchronization (see <a class="xref" href="logical-replication-architecture.html#LOGICAL-REPLICATION-SNAPSHOT" title="31.7.1. Initial Snapshot">Section 31.7.1</a>)97 for logical replication does not take this parameter into account when98 copying existing table data.99 </p></dd><dt id="SQL-CREATEPUBLICATION-WITH-PUBLISH-VIA-PARTITION-ROOT"><span class="term"><code class="literal">publish_via_partition_root</code> (<code class="type">boolean</code>)</span> <a href="#SQL-CREATEPUBLICATION-WITH-PUBLISH-VIA-PARTITION-ROOT" class="id_link">#</a></dt><dd><p>100 This parameter determines whether changes in a partitioned table (or101 on its partitions) contained in the publication will be published102 using the identity and schema of the partitioned table rather than103 that of the individual partitions that are actually changed; the104 latter is the default. Enabling this allows the changes to be105 replicated into a non-partitioned table or a partitioned table106 consisting of a different set of partitions.107 </p><p>108 There can be a case where a subscription combines multiple109 publications. If a partitioned table is published by any110 subscribed publications which set111 <code class="literal">publish_via_partition_root = true</code>, changes on this112 partitioned table (or on its partitions) will be published using113 the identity and schema of this partitioned table rather than114 that of the individual partitions.115 </p><p>116 This parameter also affects how row filters and column lists are117 chosen for partitions; see below for details.118 </p><p>119 If this is enabled, <code class="literal">TRUNCATE</code> operations performed120 directly on partitions are not replicated.121 </p></dd></dl></div></dd></dl></div><p>122 When specifying a parameter of type <code class="type">boolean</code>, the123 <code class="literal">=</code> <em class="replaceable"><code>value</code></em>124 part can be omitted, which is equivalent to125 specifying <code class="literal">TRUE</code>.126 </p></div><div class="refsect1" id="id-1.9.3.77.7"><h2>Notes</h2><p>127 If <code class="literal">FOR TABLE</code>, <code class="literal">FOR ALL TABLES</code> or128 <code class="literal">FOR TABLES IN SCHEMA</code> are not specified, then the129 publication starts out with an empty set of tables. That is useful if130 tables or schemas are to be added later.131 </p><p>132 The creation of a publication does not start replication. It only defines133 a grouping and filtering logic for future subscribers.134 </p><p>135 To create a publication, the invoking user must have the136 <code class="literal">CREATE</code> privilege for the current database.137 (Of course, superusers bypass this check.)138 </p><p>139 To add a table to a publication, the invoking user must have ownership140 rights on the table. The <code class="command">FOR ALL TABLES</code> and141 <code class="command">FOR TABLES IN SCHEMA</code> clauses require the invoking142 user to be a superuser.143 </p><p>144 The tables added to a publication that publishes <code class="command">UPDATE</code>145 and/or <code class="command">DELETE</code> operations must have146 <code class="literal">REPLICA IDENTITY</code> defined. Otherwise those operations will be147 disallowed on those tables.148 </p><p>149 Any column list must include the <code class="literal">REPLICA IDENTITY</code> columns150 in order for <code class="command">UPDATE</code> or <code class="command">DELETE</code>151 operations to be published. There are no column list restrictions if the152 publication publishes only <code class="command">INSERT</code> operations.153 </p><p>154 A row filter expression (i.e., the <code class="literal">WHERE</code> clause) must contain only155 columns that are covered by the <code class="literal">REPLICA IDENTITY</code>, in156 order for <code class="command">UPDATE</code> and <code class="command">DELETE</code> operations157 to be published. For publication of <code class="command">INSERT</code> operations,158 any column may be used in the <code class="literal">WHERE</code> expression. The159 row filter allows simple expressions that don't have160 user-defined functions, user-defined operators, user-defined types,161 user-defined collations, non-immutable built-in functions, or references to162 system columns.163 </p><p>164 The row filter on a table becomes redundant if165 <code class="literal">FOR TABLES IN SCHEMA</code> is specified and the table166 belongs to the referred schema.167 </p><p>168 For published partitioned tables, the row filter for each169 partition is taken from the published partitioned table if the170 publication parameter <code class="literal">publish_via_partition_root</code> is true,171 or from the partition itself if it is false (the default).172 See <a class="xref" href="logical-replication-row-filter.html" title="31.3. Row Filters">Section 31.3</a> for details about row173 filters.174 Similarly, for published partitioned tables, the column list for each175 partition is taken from the published partitioned table if the176 publication parameter <code class="literal">publish_via_partition_root</code> is true,177 or from the partition itself if it is false.178 </p><p>179 For an <code class="command">INSERT ... ON CONFLICT</code> command, the publication will180 publish the operation that results from the command. Depending181 on the outcome, it may be published as either <code class="command">INSERT</code> or182 <code class="command">UPDATE</code>, or it may not be published at all.183 </p><p>184 For a <code class="command">MERGE</code> command, the publication will publish an185 <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or <code class="command">DELETE</code>186 for each row inserted, updated, or deleted.187 </p><p>188 <code class="command">ATTACH</code>ing a table into a partition tree whose root is189 published using a publication with <code class="literal">publish_via_partition_root</code>190 set to <code class="literal">true</code> does not result in the table's existing contents191 being replicated.192 </p><p>193 <code class="command">COPY ... FROM</code> commands are published194 as <code class="command">INSERT</code> operations.195 </p><p>196 <acronym class="acronym">DDL</acronym> operations are not published.197 </p><p>198 The <code class="literal">WHERE</code> clause expression is executed with the role used199 for the replication connection.200 </p></div><div class="refsect1" id="id-1.9.3.77.8"><h2>Examples</h2><p>201 Create a publication that publishes all changes in two tables:202</p><pre class="programlisting">203CREATE PUBLICATION mypublication FOR TABLE users, departments;204</pre><p>205 </p><p>206 Create a publication that publishes all changes from active departments:207</p><pre class="programlisting">208CREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE);209</pre><p>210 </p><p>211 Create a publication that publishes all changes in all tables:212</p><pre class="programlisting">213CREATE PUBLICATION alltables FOR ALL TABLES;214</pre><p>215 </p><p>216 Create a publication that only publishes <code class="command">INSERT</code>217 operations in one table:218</p><pre class="programlisting">219CREATE PUBLICATION insert_only FOR TABLE mydata220 WITH (publish = 'insert');221</pre><p>222 </p><p>223 Create a publication that publishes all changes for tables224 <code class="structname">users</code>, <code class="structname">departments</code> and225 all changes for all the tables present in the schema226 <code class="structname">production</code>:227</p><pre class="programlisting">228CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES IN SCHEMA production;229</pre><p>230 </p><p>231 Create a publication that publishes all changes for all the tables present in232 the schemas <code class="structname">marketing</code> and233 <code class="structname">sales</code>:234</p><pre class="programlisting">235CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;236</pre><p>237 Create a publication that publishes all changes for table <code class="structname">users</code>,238 but replicates only columns <code class="structname">user_id</code> and239 <code class="structname">firstname</code>:240</p><pre class="programlisting">241CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);242</pre></div><div class="refsect1" id="id-1.9.3.77.9"><h2>Compatibility</h2><p>243 <code class="command">CREATE PUBLICATION</code> is a <span class="productname">PostgreSQL</span>244 extension.245 </p></div><div class="refsect1" id="id-1.9.3.77.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alterpublication.html" title="ALTER PUBLICATION"><span class="refentrytitle">ALTER PUBLICATION</span></a>, <a class="xref" href="sql-droppublication.html" title="DROP PUBLICATION"><span class="refentrytitle">DROP PUBLICATION</span></a>, <a class="xref" href="sql-createsubscription.html" title="CREATE SUBSCRIPTION"><span class="refentrytitle">CREATE SUBSCRIPTION</span></a>, <a class="xref" href="sql-altersubscription.html" title="ALTER SUBSCRIPTION"><span class="refentrytitle">ALTER SUBSCRIPTION</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createprocedure.html" title="CREATE PROCEDURE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createrole.html" title="CREATE ROLE">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE PROCEDURE </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE ROLE</td></tr></table></div></body></html>