设置逻辑复制和解码

您可以在 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_decode 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 中的复制简介。

启用网络连接

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

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

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

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 实例的最大内存与实例的最大复制槽之间的关系。

通常每个使用方都有一个复制槽和一个 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();

（2 行）

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

（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_decode，用于流式传输采用准系统文本格式的更改

创建复制槽

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

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 的逻辑复制和解码功能。

• pglogical 扩展程序在启用了连接器强制执行的实例中不起作用。此限制不适用于已配置专用服务访问通道的实例。

• 恢复启用了 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) 分别在每个读取副本上停用这些标志。