设置逻辑复制和解码

MySQL | PostgreSQL | SQL Server

您可以在 Cloud SQL for PostgreSQL 中使用逻辑复制和解码功能。这些功能支持逻辑复制工作流和更改数据捕获 (CDC) 工作流。

如需了解有关复制的一般信息，请参阅 Cloud SQL 中的复制简介。

简介

当 PostgreSQL 执行逻辑复制时，流式传输到副本的更改会使用逻辑解码从 WAL 日志中提取。解码后的更改与底层物理存储格式无关。这些更改仅体现 SQL 级别的数据在 INSERT、UPDATE 和 DELETE 方面的更改。这种独立于存储层的机制提供了极大的灵活性，可让变更数据流的使用者实现各种功能。

逻辑复制是基于逻辑解码构建的标志性功能。

与 PostgreSQL 的物理复制功能不同，后者要求源数据库和目标数据库为同一版本，而逻辑复制支持在 PostgreSQL 主要版本之间进行复制。Cloud SQL 中的逻辑复制受所有 PostgreSQL 版本中提供的 pglogical 扩展程序和 PostgreSQL 10 中添加的 PostgreSQL 原生逻辑复制的支持。

不同的插件可以采用流式传输更改的格式。这样可以实现灵活的变更数据捕获 (CDC) 架构。例如，wal2json 扩展程序允许将数据库中的所有更改流式传输到使用方（格式为 JSON）。Cloud SQL 支持内置的 pgoutput 解码器、test_decoding contrib 模块和 wal2json。Cloud SQL 目前支持 JSON 输出的两个 wal2json 变体：将整个事务编码为单个 JSON 对象的 format-version 1，以及为每个命令输出一个 JSON 对象的 format-version 2。这些插件能够复制到非 PostgreSQL 数据库。

配置 PostgreSQL 实例

PostgreSQL 通过将更多信息写入其预写式日志 (WAL) 来支持逻辑解码。

在 Cloud SQL 中，通过将 cloudsql.logical_decoding 标志设置为 on 来启用此功能。此设置与标准 PostgreSQL 中使用的设置不同。如果您更改外部 PostgreSQL 实例，则可以通过将 wal_level 配置参数设置为 logical 来启用此功能。

如果您计划使用 pglogical 扩展程序，则必须将 pglogical 添加到 shared_preload_libraries。由于 Cloud SQL 不允许直接修改此标志，因此通过将 cloudsql.enable_pglogical 设置为 on 来启用 pglogical。（在虚拟机上，运行 sudo apt-get install postgresql-13-pglogical ）并重启数据库。

如果您要使用 pglogical 在两个 PostgreSQL 实例之间复制，则只需要在主实例上启用逻辑解码，无需在副本实例上启用（除非该实例本身是其他副本的主实例）。但是，必须在这两个实例上启用 pglogical 扩展程序。如需查看有关如何使用“主实例”和“副本实例”这两个术语及其含义的示例，请参阅 Cloud SQL 中的复制简介。

启用网络连接

确保主实例接受来自副本实例的连接。

主要	副本	配置
Cloud SQL（公共 IP）	Cloud SQL（公共 IP）	将副本的传出 IP 地址添加到主实例的已获授权的网络。
Cloud SQL（专用 IP）	Cloud SQL（专用 IP）	如果两个实例位于同一 Google Cloud 项目中，则将副本 VPC 网络已分配的 IP 范围添加到托管实例的已获授权的网络中。如需在 Google Cloud 控制台中查找分配的 IP 范围，请执行以下操作：导航到 VPC 网络页面。选择您要使用的 VPC 网络。选择专用服务连接标签页。选择分配的 IP 范围标签页。注意：如果 VPC 网络的子网在 10.0.0.0/8、172.16.0.0/12 或 192.168.0.0/16 范围内，则您无需向任一实例的授权网络添加任何内容，因为这些默认情况下会为每个实例分配这些子网块。
外部	Cloud SQL	您可以使用 Database Migration Service。
Cloud SQL	外部	如需了解详情，请参阅配置外部副本。

获取副本实例的传出 IP 地址

如果副本实例是 Cloud SQL 实例且具有公共 IP 地址，请执行以下步骤以获取其传出 IP 地址。

控制台

打开“Cloud SQL 实例”页面。
在 Cloud SQL 副本的公共 IP 地址旁边，将鼠标悬停在更多信息工具提示上，然后检索传出 IP 地址。请注意，传出 IP 地址不是 Cloud 控制台的副本主列表中显示的 IP 地址。

如果副本实例不是 Cloud SQL 实例，请参阅相关文档。

如需详细了解如何获取实例的公共 IP 地址，请参阅获取 Cloud SQL 副本的传出 IP 地址。

gcloud

您可以使用以下 gcloud 命令：

gcloud sql instances describe [REPLICA_NAME] --format="default(ipAddresses)"

允许连接

如果主实例是 Cloud SQL 实例，则可以将副本的传出 IP 地址添加为已获授权的网络，从而允许从该 IP 地址进行访问。

为 PostgreSQL 9.6 及更早版本启用复制连接

如果您的主实例未在 Cloud SQL 中运行，并且正在运行 PostgreSQL 9.6 或更早版本，则必须确保该实例的 pg_hba.conf 文件设置为接受复制连接。将以下行添加到该文件中，并且仅使用 all all 进行初始测试。为了提高安全性，请将用户和 IP 地址限制为仅所需的那些用户和 IP 地址，如以下示例所示：

host replication all all md5

如需了解详情，请参阅 pg_hba.conf 文件。

创建复制用户

如需使用逻辑解码功能，请创建具有 REPLICATION 特性的 PostgreSQL 用户。

示例

CREATE USER replication_user WITH REPLICATION
IN ROLE cloudsqlsuperuser LOGIN PASSWORD 'secret';

或者，您可以为现有用户设置此特性：

ALTER USER existing_user WITH REPLICATION;

PostgreSQL 资源

使用逻辑解码时，主 PostgreSQL 实例上的后台进程使用所选的解码插件将 WAL 更改转换为逻辑更改，并将其传递给使用方（甚至可能是非 PostgreSQL 实例）。此后台进程称为 WAL 发送者。PostgreSQL 实例中处于活跃状态的并发 WAL 发送者数量受 max_wal_senders 标志的限制。此标志默认为 10，其限制会随 Cloud SQL 实例的内存呈现线性提高，每 GB 内存允许 8 个 WAL 发送者。

为确保 WAL 分段在发送到所有使用方之前不会被舍弃，PostgreSQL 使用逻辑复制槽来跟踪哪些数据已发送给哪个使用方（以及读取副本的物理复制槽）。您可以为 PostgreSQL 实例创建的复制槽数受 max_replication_slots 标志的限制。此标志默认为 10，其限制会随 Cloud SQL 实例的内存而增加，每 GB 内存允许 2 到 8 个复制槽。

下表显示了 Cloud SQL 实例的最大内存与实例的最大复制槽之间的关系。

最大内存 (GB)	复制槽数上限
4	10
16	32
32	128
64	256
128	512
256	1024
512	2048
512+	4096

通常每个使用方都有一个复制槽和一个 WAL 发送器，因此，应该将这些标志设置为大致相等的值。但是，PostgreSQL 建议为 max_wal_senders 提供小型缓冲区，以便处理连接意外终止以及建立新连接的情形。Cloud SQL 只读副本使用的物理复制也使用复制槽和 WAL 发送者，因此在计算所需的每个资源数量时要统计这些副本数。

PostgreSQL 原生逻辑复制和 pglogical 需要额外的后台进程在主实例和副本实例上运行。可以运行的后台进程数量受 max_worker_processes 标志的限制。默认值为 8，它的限制与 Cloud SQL 实例的内存呈线性增加关系，允许每 GB 内存再增加两个进程。有关这些方法使用的工作器进程的确切数目，请参阅对应的部分。

如果此标志设置得太低，并且复制失败，在日志中显示错误消息 worker registration failed，您可能需要增加 max_worker_processes 设置。

请注意，WAL 发送者不会计为工作器进程。为执行并行查询而生成的工作器会计入计数，因此如果 max_worker_processes 的值设置得过低，可能会导致性能欠佳，因为 PostgreSQL 无法利用并行查询执行。

您可以使用 pg_ls_waldir () 函数来确定 WAL 磁盘使用率。此函数仅限于 cloudsqlsuperuser 用户，例如默认管理员用户 postgres。此函数仅适用于 PostgreSQL 版本 10 及更高版本。

如需计算 WAL 磁盘总用量，请执行以下操作：

postgres=> select * from pg_ls_waldir();

name	size	修改
00000001000000000000000A	16777216	2021-08-11 15:16:49+00
000000010000000000000009	16777216	2021-08-12 06:23:24+00

（2 行）

postgres=> select pg_size_pretty(sum(size)) as "Total WAL disk usage" from pg_ls_waldir();

WAL 磁盘总用量
32 MB

（1 行）

使用外部副本设置逻辑复制

如需查看使用逻辑和逻辑解码的完整示例，请参阅配置外部副本。

使用 pglogical 设置逻辑复制

如需使用 pglogical 设置逻辑复制，必须在主实例上启用逻辑解码。在 Cloud SQL 实例上设置 cloudsql.logical_decoding=on，或在外部实例上设置 wal_level=logical。此外，必须在主实例和副本实例上启用 pglogical：对 Cloud SQL 实例设置 cloudsql.enable_pglogical=on，或将 pglogical 添加到外部实例的 shared_preload_libraries。请注意，更改这些标志需要重启主实例和副本实例。

如果您在执行这些步骤时遇到问题，请参阅排查 pglogical 的问题。

创建具有复制权限的用户

在使用 pglogical 时，您需要一个对主实例和副本实例具有复制权限和 cloudsqlsuperuser 角色的用户。下面所述的所有命令都应由该用户执行。

安装 pglogical 扩展程序

您需要在主实例和副本实例上安装 pglogical 扩展程序。在主实例上，复制用户（即连接到数据库的用户）必须安装它。

CREATE EXTENSION pglogical;

在每个实例上创建一个 pglogical 节点

pglogical 节点表示物理 PostgreSQL 实例，用于存储该实例的连接详细信息。主实例和副本实例都必须将自身注册为节点：

source-instance$ SELECT pglogical.create_node(
    node_name := 'primary',
    dsn := 'host=<primary-ip> port=5432 dbname=postgres user=replication_user password=secret'
);

dest-instance$ SELECT pglogical.create_node(
    node_name := 'replica',
    dsn := 'host=<replica-ip> port=5432 dbname=postgres user=replication_user password=secret'
);

创建一个包含要复制的数据的表

pglogical 扩展程序仅允许将一部分表复制到目的地。例如，我们将在主实例上创建一个虚拟表，并在其中填充要测试的数据：

CREATE TABLE replica_test (id SERIAL PRIMARY KEY, data text);
INSERT INTO replica_test (data) VALUES ('apple'), ('banana'), ('cherry');

还必须在副本实例上创建该表。

将表添加到复制集

为了支持将不同的数据集复制到不同的目标位置，pglogical 提供了复制集的概念。我们可以将测试表添加到默认复制集中。

SELECT pglogical.replication_set_add_table('default', 'replica_test', true);

创建 pglogical 订阅

通过向主实例提供连接详细信息，在目标实例上创建 pglogical 订阅。

SELECT pglogical.create_subscription(
    subscription_name := 'test_sub',
    provider_dsn := 'host=<primary-ip> port=5432 dbname=postgres user=replication_user password=replicapassword'
);

SELECT * FROM pglogical.show_subscription_status('test_sub');

如果状态显示为“正在复制”，则表示设置成功。查询 replica_test 表，确保已复制数据。在主实例上插入和修改记录，并验证记录随后是否显示在副本实例上。

在主实例上，查询 pg_replication_slots 表以查看订阅创建的复制槽。

清理

测试成功完成后，使用 pglogical.drop_subscription('test_sub') 删除副本上的订阅。验证复制槽是否也在主实例上被丢弃。否则，WAL 段会继续在副本实例上累积。

如需详细了解复制集、部分数据复制、DDL 复制、其他高级配置和限制，请参阅 pglogical 文档。

资源使用情况

pglogical 扩展程序运行多个后台进程，这些后台进程会计入 max_worker_processes 限制。

在稳定状态下，pglogical 扩展程序会在启用时运行一个“监控器”进程、每个安装了该扩展程序的 PostgreSQL 数据库的一个“管理器”进程（例如，可能是其中的 D），以及副本实例上每个 pglogical 订阅的一个“应用”进程（例如，可能是其中的 S）。但是，该扩展程序可能会在执行初始同步时生成其他工作器进程，并且实际上会为实例中的每个数据库生成“管理器”进程，但如果数据库未安装该扩展程序，则会立即退出。

因此，分配的工作器进程应稍多于处于稳定状态所需的工作器进程。工作器进程供 PostgreSQL 用于其他用途，例如用于并行查询处理。如果 max_worker_processes 设置得过低，复制可能会毫无征兆地失败，或者 PostgreSQL 可能无法执行并行查询处理。

总而言之，推荐使用以下设置：

max_worker_processes
  >= 1 + D + 8 (on the source instance)
  >= 1 + D + S + 8 (on the destination instance)
max_wal_senders >= S + 2 (on the source instance)
max_replication_slots >= S (on the source instance)

排查 pglogical 问题

无法创建 pglogical 扩展程序

在尝试安装 pglogical 扩展程序时，您可能会看到以下错误：

ERROR:  pglogical is not in shared_preload_libraries

在 Cloud SQL 实例上安装 pglogical 时，请确保您已设置 cloudsql.enable_pglogical=on。如果使用外部实例，请直接将其添加到 shared_preload_libraries 标志，例如 shared_preload_libraries=pg_stat_statements,pglogical。这些修改需要重启主实例。

无法创建 pglogical 订阅

创建订阅时，pglogical 首先会检查是否可以使用连接详细信息连接到实例。首先尝试创建常规连接，如果失败，则会出现错误：ERROR: could not connect to the postgresql server。

如果发生此错误，请确保将主实例配置为允许来自副本实例的连接，并确保您提供的连接详情正确无误。下面详细介绍了 PostgreSQL 无法建立连接的原因。

创建常规连接后，pglogical 会尝试建立特殊的复制连接。在 PostgreSQL 9.6 及更低版本中，这种类型的连接可以具有不同的身份验证配置。如果您看到以下错误，则需要更新源实例上的 pg_hba.conf 文件：ERROR: could not connect to the postgresql server in replication mode。

Cloud SQL 使用的 pg_hba.conf 文件包含必要的更改；只有在连接到非 Cloud SQL 管理的外部实例时，才会发生此错误。

此外，如果源实例不允许足够多的 WAL 发送者，则复制模式连接可能会失败。如果您看到 FATAL: number of requested standby connections exceeds max_wal_senders，则在主实例上增加 max_wal_senders。

pglogical 订阅关闭

pglogical 订阅可能无法复制。如需解决此问题，请首先确保后台进程正在副本实例上运行。查询 pg_stat_activity 以验证 pglogical apply 进程是否正在运行。如果没有，请检查目标节点上的日志。如果您看到消息 worker registration failed,，则可以增加 max_worker_processes 设置。

然后，确保在主实例上创建了复制槽。在副本实例上，pglogical.subscription 中的行包含订阅尝试创建的槽的名称，并在主实例上查询 pg_replication_slots 以验证该槽已成功创建。

如果未创建复制槽，请检查主实例上的日志。

ERROR: logical decoding requires wal_level >= logical 错误表示 wal_level 标志未设置为 logical。如需解决此问题，请在主实例上设置为 Cloud SQL 实例 cloudsql.logical_decoding=on。

或者，如果该实例是外部实例，请设置 wal_level=logical。

否则，您可能会看到 ERROR: all replication slots are in use 以及有用的 HINT: Free one or increase max_replication_slots。

设置原生 PostgreSQL 逻辑复制

从 PostgreSQL 10 开始，PostgreSQL 支持原生内置逻辑复制。要设置原生逻辑复制，必须通过在 Cloud SQL 实例上设置 cloudsql.logical_decoding=on 或在外部实例上设置 wal_level=logical 来在主实例上启用逻辑解码。请注意，修改这些标志需要重启主实例。

查看配置 PostgreSQL 实例部分，确保您的实例已正确配置（以用于网络连接等）。本页面提供概念验证的步骤。如果您在按照这些部分中的步骤操作时遇到任何问题，请参阅排查 pglogical 问题。如需了解详情，请参阅 PostgreSQL 文档中的逻辑复制。

创建一个包含要复制的数据的表

原生 PostgreSQL 逻辑复制支持整个数据库或仅支持单个表。例如，我们将在主实例上创建一个虚拟表，并在其中填充要测试的数据。

CREATE TABLE native_test (id SERIAL PRIMARY KEY, data text);
INSERT INTO native_test (data) VALUES ('apple'), ('banana'), ('cherry');

还必须在副本实例上创建该表。

在主实例上创建发布

原生 PostgreSQL 逻辑复制适用于发布者和订阅者。在 native_test 中创建数据的发布：

CREATE PUBLICATION pub FOR TABLE native_test;

在副本实例上创建订阅

以下示例展示如何在副本实例上创建订阅：

CREATE SUBSCRIPTION sub
    CONNECTION 'host=<primary-ip> port=5432 dbname=postgres user=replication_user password=replicapassword'
    PUBLICATION pub;

在副本实例上创建订阅需要 cloudsqlsuperuser 角色。创建订阅后，查询 native_test 表以验证数据是否已出现在副本实例中。

在主实例上，您可以查询 pg_replication_slots 表以查看订阅创建的复制槽。

清理

测试成功完成后，使用 DROP SUBSCRIPTION sub; 删除副本上的订阅。验证复制槽是否也在主实例上被丢弃。否则，WAL 分段会继续在主实例上累积。

原生 PostgreSQL 逻辑复制的限制

无法访问 pg_subscription 系统表的 subconninfo 列。

运行 pg_dump 无法转储有关订阅的信息，因为它会检查发起连接的用户是否具有超级用户权限。

接收变更数据捕获 (CDC) 已解码的 WAL 更改

作为 CDC 的替代用例，逻辑解码可以从 PostgreSQL 实例流式传输更改。为此使用的标准工具是 pg_recvlogical。

您可以使用 pg_recvlogical 工具创建复制槽并流式传输该槽跟踪的更改。更改的格式由您选择的解码插件决定。您可以使用：

wal2json，用于流式传输采用 JSON 格式的更改，或
test_decoding，用于流式传输采用准系统文本格式的更改

创建复制槽

如需创建复制槽，请运行以下命令：

pg_recvlogical
  -h <instance_ip> \
  -U <replication_user> \
  -p 5432 \
  -d postgres \
  --slot test_slot \
  --create-slot \
  -P <decoder_plugin>

流式传输更改

在一个 Cloud Shell 终端中，运行以下命令：

pg_recvlogical
  -h <instance_ip> \
  -U <replication_user> \
  -p 5432 \
  -d postgres \
  --slot test_slot \
  --start \
  -f -

在另一个 Cloud Shell 终端中，连接到您的数据库并运行以下命令：

CREATE TABLE cdc_test (id SERIAL PRIMARY KEY, data text);
INSERT INTO cdc_test (data) VALUES ('apple', 'banana');
UPDATE cdc_test SET data = 'cherry' WHERE id = 2;
DELETE FROM cdc_test WHERE id = 1;
DROP TABLE cdc_test;

如果您使用的是 wal2json 解码器插件，则第一个 Cloud Shell 终端会显示如下所示的输出：

{"change":[]}
{"change":[{"kind":"insert","schema":"public","table":"cdc_test","columnnames":["id","data"],"columntypes":["integer","text"],"columnvalues":[1,"apple"]},{"kind":"insert","schema":"public","table":"cdc_test","columnnames":["id","data"],"columntypes":["integer","text"],"columnvalues":[2,"banana"]}]}
{"change":[{"kind":"update","schema":"public","table":"cdc_test","columnnames":["id","data"],"columntypes":["integer","text"],"columnvalues":[2,"cherry"],"oldkeys":{"keynames":["id"],"keytypes":["integer"],"keyvalues":[2]}}]}
{"change":[{"kind":"delete","schema":"public","table":"cdc_test","oldkeys":{"keynames":["id"],"keytypes":["integer"],"keyvalues":[1]}}]}
{"change":[]}

如果您使用的是 test_decoding 解码器插件，则第一个 Cloud Shell 终端会显示如下所示的输出：

BEGIN 19460
COMMIT 19460
BEGIN 19461
table public.cdc_test: INSERT: id[integer]:1 data[text]:'apple'
table public.cdc_test: INSERT: id[integer]:2 data[text]:'banana'
COMMIT 19461
BEGIN 19462
table public.cdc_test: UPDATE: id[integer]:2 data[text]:'cherry'
COMMIT 19462
BEGIN 19463
table public.cdc_test: DELETE: id[integer]:1
COMMIT 19463
BEGIN 19464
COMMIT 19464

（您的事务 ID 可能会有所不同。）

清理

完成测试后，通过运行以下命令丢弃您创建的复制槽：

pg_recvlogical
  -h <instance_ip> \
  -U <replication_user> \
  -p 5432 \
  -d postgres \
  --slot test_slot \
  --drop-slot

注释和限制

本部分中的注释和限制适用于 Cloud SQL for PostgreSQL 的逻辑复制和解码功能。

恢复启用了 cloudsql.logical_decoding 或 cloudsql.enable_pglogical 并且当前充当逻辑复制的发布者的实例时，必须先停用复制到所有目标实例。否则，恢复到实例会失败并报错，但当前错误详细信息不可见。
恢复（在备份时）启用了 cloudsql.logical_decoding 或 cloudsql.enable_pglogical 的实例的备份并且您要将它恢复到新实例时，复制状态不会已恢复到新实例。您必须在恢复之后手动重新配置复制。
在具有一个或多个 Cloud SQL 读取副本（使用物理复制功能）的 Cloud SQL 实例上，如果您启用 cloudsql.logical_decoding 或 cloudsql.enable_pglogical，则这些标志也会在读取副本上启用。
- Cloud SQL 读取副本实例不能充当逻辑复制的发布者，因为 PostgreSQL 不支持读取副本中的逻辑解码。但是，在读取副本实例上会启用这些标志，以确保该副本在提升时可以用作主实例的替换内容。
- 在主实例上启用 cloudsql.logical_decoding 或 cloudsql.enable_pglogical 会导致在所有读取副本上启用这些标志，并且会导致主实例和读取副本连续重启。为避免这种情况并控制何时重启每个实例，您可以 (1) 依次在每个读取副本上设置标志，然后 (2) 在主实例上设置标志。
- 在主实例上停用 cloudsql.logical_decoding 或 cloudsql.enable_pglogical 并不会在所有读取副本上停用这些标志。如需在各实例上停用这些标志，您必须执行上述相反的步骤：(1) 停用主实例上的标志，然后 (2) 分别在每个读取副本上停用这些标志。