Postgresql 中文操作指南

31.2. Subscription #

subscription 是逻辑复制的下游部分。定义订阅的节点称为 subscriber。订阅定义了与另一个数据库的连接以及它想要订阅的一组出版物（一个或多个）。

A subscription is the downstream side of logical replication. The node where a subscription is defined is referred to as the subscriber. A subscription defines the connection to another database and set of publications (one or more) to which it wants to subscribe.

订阅数据库的行为与任何其他 PostgreSQL 实例相同，并且可以通过自己定义出版物，用作其他数据库的发布者。

The subscriber database behaves in the same way as any other PostgreSQL instance and can be used as a publisher for other databases by defining its own publications.

如果需要，订阅节点可以有多个订阅。可以在单个发布者-订阅者对之间定义多个订阅，在这种情况下，必须注意确保订阅的出版物对象不会重叠。

A subscriber node may have multiple subscriptions if desired. It is possible to define multiple subscriptions between a single publisher-subscriber pair, in which case care must be taken to ensure that the subscribed publication objects don’t overlap.

每个订阅都将通过一个复制槽接收更改（请参阅 Section 27.2.6）。对于现有表数据的初始数据同步，可能需要额外的复制槽，并且这些槽将在数据同步结束时被删除。

Each subscription will receive changes via one replication slot (see Section 27.2.6). Additional replication slots may be required for the initial data synchronization of pre-existing table data and those will be dropped at the end of data synchronization.

逻辑复制订阅可以成为同步复制的备用复制（请参阅 Section 27.2.8）。默认情况下，备用名称是订阅名称。可以将备用名称指定为 application_name 订阅连接信息中。

A logical replication subscription can be a standby for synchronous replication (see Section 27.2.8). The standby name is by default the subscription name. An alternative name can be specified as application_name in the connection information of the subscription.

如果当前用户是超级用户，pg_dump 会转储订阅。否则，会编写一条警告并跳过订阅，因为非超级用户无法从 pg_subscription 目录中读取所有订阅信息。

Subscriptions are dumped by pg_dump if the current user is a superuser. Otherwise a warning is written and subscriptions are skipped, because non-superusers cannot read all subscription information from the pg_subscription catalog.

使用 CREATE SUBSCRIPTION 添加订阅，可以使用 ALTER SUBSCRIPTION 命令随时停止/恢复订阅，并可以使用 DROP SUBSCRIPTION 命令删除订阅。

The subscription is added using CREATE SUBSCRIPTION and can be stopped/resumed at any time using the ALTER SUBSCRIPTION command and removed using DROP SUBSCRIPTION.

当一个订阅被删除并重新创建，同步信息将丢失。这意味着数据需要在之后重新同步。

When a subscription is dropped and recreated, the synchronization information is lost. This means that the data has to be resynchronized afterwards.

模式定义不会被复制，并且已发布的表必须位于订户中。只有常规表可能是复制的目标。例如，您无法复制到视图。

The schema definitions are not replicated, and the published tables must exist on the subscriber. Only regular tables may be the target of replication. For example, you can’t replicate to a view.

表之间在发布者和订户之间通过完全限定的表名进行匹配。不支持针对订户中名称不同的表复制。

The tables are matched between the publisher and the subscriber using the fully qualified table name. Replication to differently-named tables on the subscriber is not supported.

表中的列也按名称匹配。订阅者表中的列顺序不必与发布者表中列的顺序相匹配。只要数据文本表示形式可转换为目标类型，那么列的数据类型不必匹配。例如，可以从 integer 类型列复制到 bigint 类型列。目标表还可以包含已发布表未提供的其他列。任何此类列都将填充目标表定义中指定的默认值。但是，二进制格式的逻辑复制更加严格。有关详细信息，请参阅 CREATE SUBSCRIPTION 的 binary 选项。

Columns of a table are also matched by name. The order of columns in the subscriber table does not need to match that of the publisher. The data types of the columns do not need to match, as long as the text representation of the data can be converted to the target type. For example, you can replicate from a column of type integer to a column of type bigint. The target table can also have additional columns not provided by the published table. Any such columns will be filled with the default value as specified in the definition of the target table. However, logical replication in binary format is more restrictive. See the binary option of CREATE SUBSCRIPTION for details.

31.2.1. Replication Slot Management #

如前所述，每个（活动）订阅从远程（发布）端的复制槽中接收更改。

As mentioned earlier, each (active) subscription receives changes from a replication slot on the remote (publishing) side.

附加表同步槽通常是临时的，在内部创建以执行初始表同步，并在不再需要它们时自动删除。这些表同步槽生成了名称：“pg%u_sync_%u_%llu_”（参数：订阅 oid，表 relid，系统标识符 sysid）

Additional table synchronization slots are normally transient, created internally to perform initial table synchronization and dropped automatically when they are no longer needed. These table synchronization slots have generated names: “pg%u_sync_%u_%llu_” (parameters: Subscription oid, Table relid, system identifier sysid)

通常，在使用 CREATE SUBSCRIPTION 创建订阅时，远程复制槽会自动创建，并且在使用 DROP SUBSCRIPTION 删除订阅时，它会自动删除。然而，在某些情况下，单独操作订阅和底层复制槽会很有用或有必要。下面是一些场景：

Normally, the remote replication slot is created automatically when the subscription is created using CREATE SUBSCRIPTION and it is dropped automatically when the subscription is dropped using DROP SUBSCRIPTION. In some situations, however, it can be useful or necessary to manipulate the subscription and the underlying replication slot separately. Here are some scenarios:

31.2.2. Examples: Set Up Logical Replication #

在发布者上创建一些测试表。

Create some test tables on the publisher.

test_pub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
CREATE TABLE
test_pub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
CREATE TABLE
test_pub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
CREATE TABLE

在订户上创建相同的表。

Create the same tables on the subscriber.

test_sub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
CREATE TABLE
test_sub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
CREATE TABLE
test_sub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
CREATE TABLE

将数据插入到发布者端的表中。

Insert data to the tables at the publisher side.

test_pub=# INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
INSERT 0 3
test_pub=# INSERT INTO t2 VALUES (1, 'A'), (2, 'B'), (3, 'C');
INSERT 0 3
test_pub=# INSERT INTO t3 VALUES (1, 'i'), (2, 'ii'), (3, 'iii');
INSERT 0 3

为表创建发布。 pub2 和 pub3a 发布不允许一些 publish 操作。 pub3b 发布具有行筛选器（请参阅 Section 31.3 ）。

Create publications for the tables. The publications pub2 and pub3a disallow some publish operations. The publication pub3b has a row filter (see Section 31.3).

test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1;
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION pub2 FOR TABLE t2 WITH (publish = 'truncate');
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION pub3a FOR TABLE t3 WITH (publish = 'truncate');
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION pub3b FOR TABLE t3 WHERE (e > 5);
CREATE PUBLICATION

为发布创建订阅。订阅 sub3 订阅 pub3a 和 pub3b。默认情况下，所有订阅都会复制初始数据。

Create subscriptions for the publications. The subscription sub3 subscribes to both pub3a and pub3b. All subscriptions will copy initial data by default.

test_sub=# CREATE SUBSCRIPTION sub1
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub1'
test_sub-# PUBLICATION pub1;
CREATE SUBSCRIPTION
test_sub=# CREATE SUBSCRIPTION sub2
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub2'
test_sub-# PUBLICATION pub2;
CREATE SUBSCRIPTION
test_sub=# CREATE SUBSCRIPTION sub3
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub3'
test_sub-# PUBLICATION pub3a, pub3b;
CREATE SUBSCRIPTION

请注意，初始表数据会被复制，无论发布的 publish 操作是什么。

Observe that initial table data is copied, regardless of the publish operation of the publication.

test_sub=# SELECT * FROM t1;
 a |   b
---+-------
 1 | one
 2 | two
 3 | three
(3 rows)

test_sub=# SELECT * FROM t2;
 c | d
---+---
 1 | A
 2 | B
 3 | C
(3 rows)

此外，由于初始数据复制忽略 publish 操作，并且由于发布 pub3a 没有行过滤器，这意味着复制的表 t3 包含所有行，即使它们与发布 pub3b 的行过滤器不匹配。

Furthermore, because the initial data copy ignores the publish operation, and because publication pub3a has no row filter, it means the copied table t3 contains all rows even when they do not match the row filter of publication pub3b.

test_sub=# SELECT * FROM t3;
 e |  f
---+-----
 1 | i
 2 | ii
 3 | iii
(3 rows)

将更多数据插入到发布者端的表中。

Insert more data to the tables at the publisher side.

test_pub=# INSERT INTO t1 VALUES (4, 'four'), (5, 'five'), (6, 'six');
INSERT 0 3
test_pub=# INSERT INTO t2 VALUES (4, 'D'), (5, 'E'), (6, 'F');
INSERT 0 3
test_pub=# INSERT INTO t3 VALUES (4, 'iv'), (5, 'v'), (6, 'vi');
INSERT 0 3

现在，发布者端数据看起来像这样：

Now the publisher side data looks like:

test_pub=# SELECT * FROM t1;
 a |   b
---+-------
 1 | one
 2 | two
 3 | three
 4 | four
 5 | five
 6 | six
(6 rows)

test_pub=# SELECT * FROM t2;
 c | d
---+---
 1 | A
 2 | B
 3 | C
 4 | D
 5 | E
 6 | F
(6 rows)

test_pub=# SELECT * FROM t3;
 e |  f
---+-----
 1 | i
 2 | ii
 3 | iii
 4 | iv
 5 | v
 6 | vi
(6 rows)

请注意，在正常复制期间，使用了适当的 publish 操作。这意味着发布 pub2 和 pub3a 不会复制 INSERT。此外，发布 pub3b 仅复制与 pub3b 的行过滤器匹配的数据。现在，订户端数据看起来像这样：

Observe that during normal replication the appropriate publish operations are used. This means publications pub2 and pub3a will not replicate the INSERT. Also, publication pub3b will only replicate data that matches the row filter of pub3b. Now the subscriber side data looks like:

test_sub=# SELECT * FROM t1;
 a |   b
---+-------
 1 | one
 2 | two
 3 | three
 4 | four
 5 | five
 6 | six
(6 rows)

test_sub=# SELECT * FROM t2;
 c | d
---+---
 1 | A
 2 | B
 3 | C
(3 rows)

test_sub=# SELECT * FROM t3;
 e |  f
---+-----
 1 | i
 2 | ii
 3 | iii
 6 | vi
(4 rows)

31.2.3. Examples: Deferred Replication Slot Creation #

在某些情况下（例如 Section 31.2.1），如果未自动创建远程复制槽，则用户必须在激活订阅之前手动创建它。创建槽和激活订阅的步骤显示在以下示例中。这些示例指定了标准逻辑解码输出插件（pgoutput），这是内置逻辑复制所使用的插件。

There are some cases (e.g. Section 31.2.1) where, if the remote replication slot was not created automatically, the user must create it manually before the subscription can be activated. The steps to create the slot and activate the subscription are shown in the following examples. These examples specify the standard logical decoding output plugin (pgoutput), which is what the built-in logical replication uses.

首先，为要使用的示例创建一个发布。

First, create a publication for the examples to use.

test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES;
CREATE PUBLICATION

示例 1：其中订阅显示 connect = false

Example 1: Where the subscription says connect = false

示例 2：其中订阅表示 connect = false ，但也指定了 slot_name 选项。

Example 2: Where the subscription says connect = false, but also specifies the slot_name option.

示例 3：其中订阅指定了 slot_name = NONE

Example 3: Where the subscription specifies slot_name = NONE