diff options
Diffstat (limited to 'doc/src/sgml/ref/create_publication.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_publication.sgml | 409 |
1 files changed, 409 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml new file mode 100644 index 0000000..7ab7e77 --- /dev/null +++ b/doc/src/sgml/ref/create_publication.sgml @@ -0,0 +1,409 @@ +<!-- +doc/src/sgml/ref/create_publication.sgml +PostgreSQL documentation +--> + +<refentry id="sql-createpublication"> + <indexterm zone="sql-createpublication"> + <primary>CREATE PUBLICATION</primary> + </indexterm> + + <refmeta> + <refentrytitle>CREATE PUBLICATION</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>CREATE PUBLICATION</refname> + <refpurpose>define a new publication</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +CREATE PUBLICATION <replaceable class="parameter">name</replaceable> + [ FOR ALL TABLES + | FOR <replaceable class="parameter">publication_object</replaceable> [, ... ] ] + [ WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ] + +<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase> + + TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] [ WHERE ( <replaceable class="parameter">expression</replaceable> ) ] [, ... ] + TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ] +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>CREATE PUBLICATION</command> 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. + </para> + + <para> + A publication is essentially a group of tables whose data changes are + intended to be replicated through logical replication. See + <xref linkend="logical-replication-publication"/> for details about how + publications fit into the logical replication setup. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="parameter">name</replaceable></term> + <listitem> + <para> + The name of the new publication. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>FOR TABLE</literal></term> + <listitem> + <para> + Specifies a list of tables to add to the publication. If + <literal>ONLY</literal> is specified before the table name, only + that table is added to the publication. If <literal>ONLY</literal> is not + specified, the table and all its descendant tables (if any) are added. + Optionally, <literal>*</literal> 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. + </para> + + <para> + If the optional <literal>WHERE</literal> clause is specified, it defines a + <firstterm>row filter</firstterm> expression. Rows for + which the <replaceable class="parameter">expression</replaceable> + evaluates to false or null will not be published. Note that parentheses + are required around the expression. It has no effect on + <literal>TRUNCATE</literal> commands. + </para> + + <para> + 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 <literal>TRUNCATE</literal> commands. See + <xref linkend="logical-replication-col-lists"/> for details about column + lists. + </para> + + <para> + 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. + </para> + + <para> + Specifying a column list when the publication also publishes + <literal>FOR TABLES IN SCHEMA</literal> is not supported. + </para> + + <para> + 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. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>FOR ALL TABLES</literal></term> + <listitem> + <para> + Marks the publication as one that replicates changes for all tables in + the database, including tables created in the future. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>FOR TABLES IN SCHEMA</literal></term> + <listitem> + <para> + Marks the publication as one that replicates changes for all tables in + the specified list of schemas, including tables created in the future. + </para> + + <para> + Specifying a schema when the publication also publishes a table with a + column list is not supported. + </para> + + <para> + 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. + </para> + + <para> + 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. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term> + <listitem> + <para> + This clause specifies optional parameters for a publication. The + following parameters are supported: + + <variablelist> + <varlistentry> + <term><literal>publish</literal> (<type>string</type>)</term> + <listitem> + <para> + 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 + <literal>insert</literal>, <literal>update</literal>, + <literal>delete</literal>, and <literal>truncate</literal>. + The default is to publish all actions, + and so the default value for this option is + <literal>'insert, update, delete, truncate'</literal>. + </para> + <para> + This parameter only affects DML operations. In particular, the initial + data synchronization (see <xref linkend="logical-replication-snapshot"/>) + for logical replication does not take this parameter into account when + copying existing table data. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>publish_via_partition_root</literal> (<type>boolean</type>)</term> + <listitem> + <para> + 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. + </para> + + <para> + This parameter also affects how row filters and column lists are + chosen for partitions; see below for details. + </para> + + <para> + If this is enabled, <literal>TRUNCATE</literal> operations performed + directly on partitions are not replicated. + </para> + </listitem> + </varlistentry> + </variablelist></para> + </listitem> + </varlistentry> + + </variablelist> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + If <literal>FOR TABLE</literal>, <literal>FOR ALL TABLES</literal> or + <literal>FOR TABLES IN SCHEMA</literal> 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. + </para> + + <para> + The creation of a publication does not start replication. It only defines + a grouping and filtering logic for future subscribers. + </para> + + <para> + To create a publication, the invoking user must have the + <literal>CREATE</literal> privilege for the current database. + (Of course, superusers bypass this check.) + </para> + + <para> + To add a table to a publication, the invoking user must have ownership + rights on the table. The <command>FOR ALL TABLES</command> and + <command>FOR TABLES IN SCHEMA</command> clauses require the invoking + user to be a superuser. + </para> + + <para> + The tables added to a publication that publishes <command>UPDATE</command> + and/or <command>DELETE</command> operations must have + <literal>REPLICA IDENTITY</literal> defined. Otherwise those operations will be + disallowed on those tables. + </para> + + <para> + Any column list must include the <literal>REPLICA IDENTITY</literal> columns + in order for <command>UPDATE</command> or <command>DELETE</command> + operations to be published. There are no column list restrictions if the + publication publishes only <command>INSERT</command> operations. + </para> + + <para> + A row filter expression (i.e., the <literal>WHERE</literal> clause) must contain only + columns that are covered by the <literal>REPLICA IDENTITY</literal>, in + order for <command>UPDATE</command> and <command>DELETE</command> operations + to be published. For publication of <command>INSERT</command> operations, + any column may be used in the <literal>WHERE</literal> 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. + </para> + + <para> + The row filter on a table becomes redundant if + <literal>FOR TABLES IN SCHEMA</literal> is specified and the table + belongs to the referred schema. + </para> + + <para> + For published partitioned tables, the row filter for each + partition is taken from the published partitioned table if the + publication parameter <literal>publish_via_partition_root</literal> is true, + or from the partition itself if it is false (the default). + See <xref linkend="logical-replication-row-filter"/> 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 <literal>publish_via_partition_root</literal> is true, + or from the partition itself if it is false. + </para> + + <para> + For an <command>INSERT ... ON CONFLICT</command> command, the publication will + publish the operation that results from the command. Depending + on the outcome, it may be published as either <command>INSERT</command> or + <command>UPDATE</command>, or it may not be published at all. + </para> + + <para> + For a <command>MERGE</command> command, the publication will publish an + <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command> + for each row inserted, updated, or deleted. + </para> + + <para> + <command>ATTACH</command>ing a table into a partition tree whose root is + published using a publication with <literal>publish_via_partition_root</literal> + set to <literal>true</literal> does not result in the table's existing contents + being replicated. + </para> + + <para> + <command>COPY ... FROM</command> commands are published + as <command>INSERT</command> operations. + </para> + + <para> + <acronym>DDL</acronym> operations are not published. + </para> + + <para> + The <literal>WHERE</literal> clause expression is executed with the role used + for the replication connection. + </para> + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + Create a publication that publishes all changes in two tables: +<programlisting> +CREATE PUBLICATION mypublication FOR TABLE users, departments; +</programlisting> + </para> + + <para> + Create a publication that publishes all changes from active departments: +<programlisting> +CREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE); +</programlisting> + </para> + + <para> + Create a publication that publishes all changes in all tables: +<programlisting> +CREATE PUBLICATION alltables FOR ALL TABLES; +</programlisting> + </para> + + <para> + Create a publication that only publishes <command>INSERT</command> + operations in one table: +<programlisting> +CREATE PUBLICATION insert_only FOR TABLE mydata + WITH (publish = 'insert'); +</programlisting> + </para> + + <para> + Create a publication that publishes all changes for tables + <structname>users</structname>, <structname>departments</structname> and + all changes for all the tables present in the schema + <structname>production</structname>: +<programlisting> +CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES IN SCHEMA production; +</programlisting> + </para> + + <para> + Create a publication that publishes all changes for all the tables present in + the schemas <structname>marketing</structname> and + <structname>sales</structname>: +<programlisting> +CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales; +</programlisting></para> + + <para> + Create a publication that publishes all changes for table <structname>users</structname>, + but replicates only columns <structname>user_id</structname> and + <structname>firstname</structname>: +<programlisting> +CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname); +</programlisting></para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>CREATE PUBLICATION</command> is a <productname>PostgreSQL</productname> + extension. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-alterpublication"/></member> + <member><xref linkend="sql-droppublication"/></member> + <member><xref linkend="sql-createsubscription"/></member> + <member><xref linkend="sql-altersubscription"/></member> + </simplelist> + </refsect1> +</refentry> |