Postgresql 中文操作指南

CREATE PUBLICATION

创建出版物 - 定义一个新出版物

CREATE PUBLICATION — define a new publication

Synopsis

CREATE PUBLICATION name
    [ FOR ALL TABLES
      | FOR publication_object [, ... ] ]
    [ WITH ( publication_parameter [= value] [, ... ] ) ]

where publication_object is one of:

    TABLE [ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ] [, ... ]
    TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]

Description

CREATE PUBLICATION 向当前数据库中添加一个新出版物。出版物名称必须与当前数据库中任何现有出版物的名称不同。

CREATE PUBLICATION 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.

本质上，出版物就是一组表，其数据更改旨在通过逻辑复制进行复制。有关出版物如何融入逻辑复制设置中的详细信息，请参见 Section 31.1 。

A publication is essentially a group of tables whose data changes are intended to be replicated through logical replication. See Section 31.1 for details about how publications fit into the logical replication setup.

Parameters

  • name #

    • The name of the new publication.

  • FOR TABLE #

    • Specifies a list of tables to add to the publication. If ONLY is specified before the table name, only that table is added to the publication. If ONLY is not specified, the table and all its descendant tables (if any) are added. Optionally, * 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.

    • If the optional WHERE clause is specified, it defines a row filter expression. Rows for which the expression evaluates to false or null will not be published. Note that parentheses are required around the expression. It has no effect on TRUNCATE commands.

    • 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 TRUNCATE commands. See Section 31.4 for details about column lists.

    • 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.

    • Specifying a column list when the publication also publishes FOR TABLES IN SCHEMA is not supported.

    • 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.

  • FOR ALL TABLES #

    • Marks the publication as one that replicates changes for all tables in the database, including tables created in the future.

  • FOR TABLES IN SCHEMA #

    • Marks the publication as one that replicates changes for all tables in the specified list of schemas, including tables created in the future.

    • Specifying a schema when the publication also publishes a table with a column list is not supported.

    • 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.

    • 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.

  • WITH ( _publication_parameter [= value] [, …​ ] )_ #

    • This clause specifies optional parameters for a publication. The following parameters are supported:

  • publish (string) #

    • 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 insert, update, delete, and truncate. The default is to publish all actions, and so the default value for this option is 'insert, update, delete, truncate'.

    • This parameter only affects DML operations. In particular, the initial data synchronization (see Section 31.7.1) for logical replication does not take this parameter into account when copying existing table data.

  • publish_via_partition_root (boolean) #

    • 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.

    • There can be a case where a subscription combines multiple publications. If a partitioned table is published by any subscribed publications which set publish_via_partition_root = true, 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.

    • This parameter also affects how row filters and column lists are chosen for partitions; see below for details.

    • If this is enabled, TRUNCATE operations performed directly on partitions are not replicated.

在指定类型为 boolean 的参数时，可以省略 = value 部分，这等同于指定 TRUE 。

When specifying a parameter of type boolean, the = value part can be omitted, which is equivalent to specifying TRUE.

Notes

如果未指定 FOR TABLE 、 FOR ALL TABLES 或 FOR TABLES IN SCHEMA ，则发布将以一组空表开始。如果之后要添加表或架构，则此项很有用。

If FOR TABLE, FOR ALL TABLES or FOR TABLES IN SCHEMA 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.

创建发布并不开始复制。它只为将来的订阅者定义分组和过滤逻辑。

The creation of a publication does not start replication. It only defines a grouping and filtering logic for future subscribers.

要创建发布，调用用户必须具有当前数据库的 CREATE 权限。（当然，超级用户绕过此项检查。）

To create a publication, the invoking user must have the CREATE privilege for the current database. (Of course, superusers bypass this check.)

要添加表到发布中，调用用户必须具有对该表的拥有权。 FOR ALL TABLES 和 FOR TABLES IN SCHEMA 语句要求调用用户是超级用户。

To add a table to a publication, the invoking user must have ownership rights on the table. The FOR ALL TABLES and FOR TABLES IN SCHEMA clauses require the invoking user to be a superuser.

添加到发布中的表（发布 UPDATE 和/或 DELETE 操作）必须已经定义了 REPLICA IDENTITY 。否则，这些操作将不允许在这些表中执行。

The tables added to a publication that publishes UPDATE and/or DELETE operations must have REPLICA IDENTITY defined. Otherwise those operations will be disallowed on those tables.

为了允许发布 UPDATE 或 DELETE 操作，任何列列表必须包括 REPLICA IDENTITY 列。如果发布只发布 INSERT 操作，则没有列列表限制。

Any column list must include the REPLICA IDENTITY columns in order for UPDATE or DELETE operations to be published. There are no column list restrictions if the publication publishes only INSERT operations.

行过滤器表达式（即 WHERE 语句）为了发布 UPDATE 和 DELETE 操作，只能包含 REPLICA IDENTITY 涵盖的列。为了发布 INSERT 操作，任何列都可以用于 WHERE 表达式中。行过滤器允许使用的简单表达式没有用户自定义函数、用户自定义运算符、用户自定义类型、用户自定义排序规则、不可更改的内置函数或对系统列的引用。

A row filter expression (i.e., the WHERE clause) must contain only columns that are covered by the REPLICA IDENTITY, in order for UPDATE and DELETE operations to be published. For publication of INSERT operations, any column may be used in the WHERE 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.

如果指定 FOR TABLES IN SCHEMA 并且该表属于所引用的架构，则该表的行过滤器将变得多余。

The row filter on a table becomes redundant if FOR TABLES IN SCHEMA is specified and the table belongs to the referred schema.

对于已发布的分区表，如果发布参数 publish_via_partition_root 为真，则每个分区的行过滤器将取自已发布的分区表；如果为假（默认值），则取自分区本身。有关行过滤器的详细信息，请参见 Section 31.3 。同样，对于已发布的分区表，如果发布参数 publish_via_partition_root 为真，则每个分区的列列表将取自已发布的分区表；如果为假，则取自分区本身。

For published partitioned tables, the row filter for each partition is taken from the published partitioned table if the publication parameter publish_via_partition_root is true, or from the partition itself if it is false (the default). See Section 31.3 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 publish_via_partition_root is true, or from the partition itself if it is false.

对于 INSERT …​ ON CONFLICT 命令，发布将发布该命令产生的操作。根据结果，它可能发布为 INSERT 或 UPDATE ，也可能根本不发布。

For an INSERT …​ ON CONFLICT command, the publication will publish the operation that results from the command. Depending on the outcome, it may be published as either INSERT or UPDATE, or it may not be published at all.

对于 MERGE 命令，发布将针对每行插入、更新或删除发布 INSERT 、 UPDATE 或 DELETE 。

For a MERGE command, the publication will publish an INSERT, UPDATE, or DELETE for each row inserted, updated, or deleted.

将 ATTACH_ing a table into a partition tree whose root is published using a publication with _publish_via_partition_root 设置为 true 不会导致复制该表的现有内容。

ATTACH_ing a table into a partition tree whose root is published using a publication with _publish_via_partition_root set to true does not result in the table’s existing contents being replicated.

COPY …​ FROM 命令作为 INSERT 操作发布。

COPY …​ FROM commands are published as INSERT operations.

不发布 DDL 操作。

DDL operations are not published.

使用复制连接角色执行 WHERE 子句表达式。

The WHERE clause expression is executed with the role used for the replication connection.

Examples

创建一个发布所有两个表中更改的发布：

Create a publication that publishes all changes in two tables:

CREATE PUBLICATION mypublication FOR TABLE users, departments;

创建一个发布所有处于活动部门的更改的发布：

Create a publication that publishes all changes from active departments:

CREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE);

创建一个发布所有表中所有更改的发布：

Create a publication that publishes all changes in all tables:

CREATE PUBLICATION alltables FOR ALL TABLES;

创建一个只发布一个表中 INSERT 操作的发布：

Create a publication that only publishes INSERT operations in one table:

CREATE PUBLICATION insert_only FOR TABLE mydata
    WITH (publish = 'insert');

创建一个发布表 users 、 departments 的所有更改以及模式中所有表的所有更改的发布 production ：

Create a publication that publishes all changes for tables users, departments and all changes for all the tables present in the schema production:

CREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES IN SCHEMA production;

创建一个发布模式中所有表所有更改的发布 marketing 和 sales ：

Create a publication that publishes all changes for all the tables present in the schemas marketing and sales:

CREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;

创建一个发布表 users 所有更改的发布，但只复制列 user_id 和 firstname ：

Create a publication that publishes all changes for table users, but replicates only columns user_id and firstname:

CREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);

Compatibility

CREATE PUBLICATION 是 PostgreSQL 扩展。

CREATE PUBLICATION is a PostgreSQL extension.

See Also

ALTER PUBLICATION 、 DROP PUBLICATION 、 CREATE SUBSCRIPTION 、 ALTER SUBSCRIPTION

ALTER PUBLICATION, DROP PUBLICATION, CREATE SUBSCRIPTION, ALTER SUBSCRIPTION