Adding upstream version 16.2.upstream/16.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-13 13:44:03 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-13 13:44:03 +0000
commit: 293913568e6a7a86fd1479e1cff8e2ecb58d6568 (patch)
tree: fc3b469a3ec5ab71b36ea97cc7aaddb838423a0c /doc/src/sgml/html/sql-createpublication.html
parent: Initial commit. (diff)
download: postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.tar.xz
postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.zip
1 files changed, 245 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createpublication.html b/doc/src/sgml/html/sql-createpublication.html
new file mode 100644
index 0000000..2228835
--- /dev/null
+++ b/doc/src/sgml/html/sql-createpublication.html
@@ -0,0 +1,245 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!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.2 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">
+CREATE PUBLICATION <em class="replaceable"><code>name</code></em>
+    [ FOR ALL TABLES
+      | FOR <em class="replaceable"><code>publication_object</code></em> [, ... ] ]
+    [ WITH ( <em class="replaceable"><code>publication_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) ]
+
+<span class="phrase">where <em class="replaceable"><code>publication_object</code></em> is one of:</span>
+
+    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> ) ] [, ... ]
+    TABLES IN SCHEMA { <em class="replaceable"><code>schema_name</code></em> | CURRENT_SCHEMA } [, ... ]
+</pre></div><div class="refsect1" id="id-1.9.3.77.5"><h2>Description</h2><p>
+   <code class="command">CREATE PUBLICATION</code> adds a new publication
+   into the current database.  The publication name must be distinct from
+   the name of any existing publication in the current database.
+  </p><p>
+   A publication is essentially a group of tables whose data changes are
+   intended to be replicated through logical replication.  See
+   <a class="xref" href="logical-replication-publication.html" title="31.1. Publication">Section 31.1</a> for details about how
+   publications fit into the logical replication setup.
+   </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>
+      The name of the new publication.
+     </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>
+      Specifies a list of tables to add to the publication.  If
+      <code class="literal">ONLY</code> is specified before the table name, only
+      that table is added to the publication.  If <code class="literal">ONLY</code> is not
+      specified, the table and all its descendant tables (if any) are added.
+      Optionally, <code class="literal">*</code> can be specified after the table name to
+      explicitly indicate that descendant tables are included.
+      This does not apply to a partitioned table, however.  The partitions of
+      a partitioned table are always implicitly considered part of the
+      publication, so they are never explicitly added to the publication.
+     </p><p>
+      If the optional <code class="literal">WHERE</code> clause is specified, it defines a
+      <em class="firstterm">row filter</em> expression. Rows for
+      which the <em class="replaceable"><code>expression</code></em>
+      evaluates to false or null will not be published. Note that parentheses
+      are required around the expression. It has no effect on
+      <code class="literal">TRUNCATE</code> commands.
+     </p><p>
+      When a column list is specified, only the named columns are replicated.
+      If no column list is specified, all columns of the table are replicated
+      through this publication, including any columns added later. It has no
+      effect on <code class="literal">TRUNCATE</code> commands. See
+      <a class="xref" href="logical-replication-col-lists.html" title="31.4. Column Lists">Section 31.4</a> for details about column
+      lists.
+     </p><p>
+      Only persistent base tables and partitioned tables can be part of a
+      publication.  Temporary tables, unlogged tables, foreign tables,
+      materialized views, and regular views cannot be part of a publication.
+     </p><p>
+      Specifying a column list when the publication also publishes
+      <code class="literal">FOR TABLES IN SCHEMA</code> is not supported.
+     </p><p>
+      When a partitioned table is added to a publication, all of its existing
+      and future partitions are implicitly considered to be part of the
+      publication.  So, even operations that are performed directly on a
+      partition are also published via publications that its ancestors are
+      part of.
+     </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>
+      Marks the publication as one that replicates changes for all tables in
+      the database, including tables created in the future.
+     </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>
+      Marks the publication as one that replicates changes for all tables in
+      the specified list of schemas, including tables created in the future.
+     </p><p>
+      Specifying a schema when the publication also publishes a table with a
+      column list is not supported.
+     </p><p>
+      Only persistent base tables and partitioned tables present in the schema
+      will be included as part of the publication.  Temporary tables, unlogged
+      tables, foreign tables, materialized views, and regular views from the
+      schema will not be part of the publication.
+     </p><p>
+      When a partitioned table is published via schema level publication, all
+      of its existing and future partitions are implicitly considered to be part of the
+      publication, regardless of whether they are from the publication schema or not.
+      So, even operations that are performed directly on a
+      partition are also published via publications that its ancestors are
+      part of.
+     </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>
+      This clause specifies optional parameters for a publication.  The
+      following parameters are supported:
+
+      </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>
+          This parameter determines which DML operations will be published by
+          the new publication to the subscribers.  The value is
+          comma-separated list of operations.  The allowed operations are
+          <code class="literal">insert</code>, <code class="literal">update</code>,
+          <code class="literal">delete</code>, and <code class="literal">truncate</code>.
+          The default is to publish all actions,
+          and so the default value for this option is
+          <code class="literal">'insert, update, delete, truncate'</code>.
+         </p><p>
+          This parameter only affects DML operations. In particular, the initial
+          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>)
+          for logical replication does not take this parameter into account when
+          copying existing table data.
+         </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>
+          This parameter determines whether changes in a partitioned table (or
+          on its partitions) contained in the publication will be published
+          using the identity and schema of the partitioned table rather than
+          that of the individual partitions that are actually changed; the
+          latter is the default.  Enabling this allows the changes to be
+          replicated into a non-partitioned table or a partitioned table
+          consisting of a different set of partitions.
+         </p><p>
+          There can be a case where a subscription combines multiple
+          publications. If a partitioned table is published by any
+          subscribed publications which set
+          <code class="literal">publish_via_partition_root = true</code>, changes on this
+          partitioned table (or on its partitions) will be published using
+          the identity and schema of this partitioned table rather than
+          that of the individual partitions.
+         </p><p>
+          This parameter also affects how row filters and column lists are
+          chosen for partitions; see below for details.
+         </p><p>
+          If this is enabled, <code class="literal">TRUNCATE</code> operations performed
+          directly on partitions are not replicated.
+         </p></dd></dl></div></dd></dl></div><p>
+   When specifying a parameter of type <code class="type">boolean</code>, the
+   <code class="literal">=</code> <em class="replaceable"><code>value</code></em>
+   part can be omitted, which is equivalent to
+   specifying <code class="literal">TRUE</code>.
+  </p></div><div class="refsect1" id="id-1.9.3.77.7"><h2>Notes</h2><p>
+   If <code class="literal">FOR TABLE</code>, <code class="literal">FOR ALL TABLES</code> or
+   <code class="literal">FOR TABLES IN SCHEMA</code> are not specified, then the
+   publication starts out with an empty set of tables.  That is useful if
+   tables or schemas are to be added later.
+  </p><p>
+   The creation of a publication does not start replication.  It only defines
+   a grouping and filtering logic for future subscribers.
+  </p><p>
+   To create a publication, the invoking user must have the
+   <code class="literal">CREATE</code> privilege for the current database.
+   (Of course, superusers bypass this check.)
+  </p><p>
+   To add a table to a publication, the invoking user must have ownership
+   rights on the table.  The <code class="command">FOR ALL TABLES</code> and
+   <code class="command">FOR TABLES IN SCHEMA</code> clauses require the invoking
+   user to be a superuser.
+  </p><p>
+   The tables added to a publication that publishes <code class="command">UPDATE</code>
+   and/or <code class="command">DELETE</code> operations must have
+   <code class="literal">REPLICA IDENTITY</code> defined.  Otherwise those operations will be
+   disallowed on those tables.
+  </p><p>
+   Any column list must include the <code class="literal">REPLICA IDENTITY</code> columns
+   in order for <code class="command">UPDATE</code> or <code class="command">DELETE</code>
+   operations to be published. There are no column list restrictions if the
+   publication publishes only <code class="command">INSERT</code> operations.
+  </p><p>
+   A row filter expression (i.e., the <code class="literal">WHERE</code> clause) must contain only
+   columns that are covered by the <code class="literal">REPLICA IDENTITY</code>, in
+   order for <code class="command">UPDATE</code> and <code class="command">DELETE</code> operations
+   to be published. For publication of <code class="command">INSERT</code> operations,
+   any column may be used in the <code class="literal">WHERE</code> expression. The
+   row filter allows simple expressions that don't have
+   user-defined functions, user-defined operators, user-defined types,
+   user-defined collations, non-immutable built-in functions, or references to
+   system columns.
+  </p><p>
+   The row filter on a table becomes redundant if
+   <code class="literal">FOR TABLES IN SCHEMA</code> is specified and the table
+   belongs to the referred schema.
+  </p><p>
+   For published partitioned tables, the row filter for each
+   partition is taken from the published partitioned table if the
+   publication parameter <code class="literal">publish_via_partition_root</code> is true,
+   or from the partition itself if it is false (the default).
+   See <a class="xref" href="logical-replication-row-filter.html" title="31.3. Row Filters">Section 31.3</a> for details about row
+   filters.
+   Similarly, for published partitioned tables, the column list for each
+   partition is taken from the published partitioned table if the
+   publication parameter <code class="literal">publish_via_partition_root</code> is true,
+   or from the partition itself if it is false.
+  </p><p>
+   For an <code class="command">INSERT ... ON CONFLICT</code> command, the publication will
+   publish the operation that results from the command.  Depending
+   on the outcome, it may be published as either <code class="command">INSERT</code> or
+   <code class="command">UPDATE</code>, or it may not be published at all.
+  </p><p>
+   For a <code class="command">MERGE</code> command, the publication will publish an
+   <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or <code class="command">DELETE</code>
+   for each row inserted, updated, or deleted.
+  </p><p>
+   <code class="command">ATTACH</code>ing a table into a partition tree whose root is
+   published using a publication with <code class="literal">publish_via_partition_root</code>
+   set to <code class="literal">true</code> does not result in the table's existing contents
+   being replicated.
+  </p><p>
+   <code class="command">COPY ... FROM</code> commands are published
+   as <code class="command">INSERT</code> operations.
+  </p><p>
+   <acronym class="acronym">DDL</acronym> operations are not published.
+  </p><p>
+   The <code class="literal">WHERE</code> clause expression is executed with the role used
+   for the replication connection.
+  </p></div><div class="refsect1" id="id-1.9.3.77.8"><h2>Examples</h2><p>
+   Create a publication that publishes all changes in two tables:
+</p><pre class="programlisting">
+CREATE PUBLICATION mypublication FOR TABLE users, departments;
+</pre><p>
+  </p><p>
+   Create a publication that publishes all changes from active departments:
+</p><pre class="programlisting">
+CREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE);
+</pre><p>
+  </p><p>
+   Create a publication that publishes all changes in all tables:
+</p><pre class="programlisting">
+CREATE PUBLICATION alltables FOR ALL TABLES;
+</pre><p>
+  </p><p>
+   Create a publication that only publishes <code class="command">INSERT</code>
+   operations in one table:
+</p><pre class="programlisting">
+CREATE PUBLICATION insert_only FOR TABLE mydata
+    WITH (publish = 'insert');
+</pre><p>
+  </p><p>
+   Create a publication that publishes all changes for tables
+   <code class="structname">users</code>, <code class="structname">departments</code> and
+   all changes for all the tables present in the schema
+   <code class="structname">production</code>:
+</p><pre class="programlisting">
+CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES IN SCHEMA production;
+</pre><p>
+  </p><p>
+   Create a publication that publishes all changes for all the tables present in
+   the schemas <code class="structname">marketing</code> and
+   <code class="structname">sales</code>:
+</p><pre class="programlisting">
+CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;
+</pre><p>
+   Create a publication that publishes all changes for table <code class="structname">users</code>,
+   but replicates only columns <code class="structname">user_id</code> and
+   <code class="structname">firstname</code>:
+</p><pre class="programlisting">
+CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);
+</pre></div><div class="refsect1" id="id-1.9.3.77.9"><h2>Compatibility</h2><p>
+   <code class="command">CREATE PUBLICATION</code> is a <span class="productname">PostgreSQL</span>
+   extension.
+  </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.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE ROLE</td></tr></table></div></body></html>
+\ No newline at end of file
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-13 13:44:03 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-13 13:44:03 +0000
commit	293913568e6a7a86fd1479e1cff8e2ecb58d6568 (patch)
tree	fc3b469a3ec5ab71b36ea97cc7aaddb838423a0c /doc/src/sgml/html/sql-createpublication.html
parent	Initial commit. (diff)
download	postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.tar.xz postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.zip