在 AlloyDB 和 AlloyDB Omni 之间复制数据

本页介绍了使用 pglogical 扩展程序在 Google Cloud AlloyDB 和 AlloyDB Omni 之间复制数据的步骤。

如需简要了解 AlloyDB Omni 中的 pglogical、其优势和限制，请参阅 pglogical 扩展程序简介。

pglogical 的关键组件

pglogical 扩展程序的关键组件如下：

  • 节点：为 PostgreSQL 集群中的数据库提供的引用。 pglogical 扩展程序会安装到集群中的任意数量的数据库中，并针对这些数据库运行，每个数据库都充当一个不同的 pglogical 节点。每个节点可以同时是提供方（也称为复制源）或订阅方（也称为复制目标），也可以同时是这两者。每个数据库只能有一个节点。
  • 复制集：在提供程序数据库中定义为要迁移的表和序列以及需要复制的 SQL 语句（例如 INSERT, UPDATE, DELETE, TRUNCATE）的逻辑分组。您可以将表分配给多个复制集。默认情况下，系统会提供三个预配置的复制集，例如 default、default_insert_only 和 ddl_sql，您可以根据需要添加任意数量的其他复制集。
  • 订阅：提供订阅者数据库中从提供方数据库复制的更改以及从提供方数据库复制的更改的详细信息。订阅通过连接字符串指定提供方数据库，并可选择性指定应从该提供方复制哪些复制集。此外，您还可以在创建订阅时指定是否使用 apply delay。

在此部署中， Google Cloud AlloyDB 服务是提供方，而本地 AlloyDB Omni 是订阅方。请注意，也可以采用相反的配置。

支持的身份验证方法

在 AlloyDB Omni 上实现 pglogical 扩展之前，您必须考虑复制节点之间的网络和安全性。与 pglogical 扩展程序搭配使用的两个主要身份验证方法是密码和信任身份验证方法。

建议的身份验证方法是信任身份验证，因为在密码身份验证方法中，密码会以纯文本格式存储在 pglogical 拥有的数据库表中。具有查询这些表的数据库权限的任何人都可以在非二进制备份和 PostgreSQL 日志文件中以纯文本形式查看这些密码。

如果您使用的是信任身份验证方法，则必须在基于主机的身份验证文件 pg_hba.conf 中进行特定条目，以实现最高安全性。您可以通过指定目标数据库来限制访问权限，仅允许使用复制选项或特定数据库、复制用户，并且仅允许来自订阅者特定 IP 地址的用户访问。

准备工作

您可以将 pglogical 作为扩展程序安装在给定数据库中。

在 AlloyDB Omni 上实现 pglogical 扩展之前，请确保您满足以下系统要求：

  • Google Cloud AlloyDB 集群，以及对主实例的读写权限（以 Cloud AlloyDB 管理员身份）。如需了解如何预配 Google Cloud AlloyDB 集群，请参阅创建数据库并连接到该数据库。
  • 已安装并配置的 AlloyDB Omni 服务器。如需了解如何安装 AlloyDB Omni，请参阅安装 AlloyDB Omni。
  • Google Cloud AlloyDB 的主实例和 AlloyDB Omni 主机服务器的 IP 地址。
  • Google Cloud AlloyDB 与 AlloyDB Omni 主机服务器之间已建立且安全的网络。必须在标准 PostgreSQL 端口 5432 上建立 TCP 连接。

调整 Google Cloud AlloyDB 提供方的参数

pglogical 扩展程序只需对Google Cloud AlloyDB 提供方集群进行一组最少的参数调整。您必须将 wal_level 参数设置为 logical，并将 pglogical 附加到 postgresql.conf 文件中的 shared_preload_libraries 参数。

   cp postgresql.conf postgresql.bak
   sed -r -i "s|(\#)?wal_level\s*=.*|wal_level=logical|" postgresql.conf
   sed -r -i "s|(\#)?(shared_preload_libraries\s*=\s*)'(.*)'.*$|\2'\3,pglogical'|" postgresql.conf
   sed -r -i "s|',|'|" postgresql.conf

在 Google Cloud AlloyDB 服务中，您可以通过设置适当的集群标志来调整参数。

您必须调整以下 Google Cloud AlloyDB 标志的参数：

  • alloydb.enable_pglogical = on
  • alloydb.logical_decoding = on

如需了解如何在 Google CloudAlloyDB 中设置数据库标志，请参阅配置实例的数据库标志。

对于其他必需的提供程序节点数据库参数，您必须设置Google Cloud AlloyDB 默认值，如下所示：

  • max_worker_processes：每个提供方数据库一个，每个订阅方节点至少一个。此参数的标准值至少为 10。
  • max_replication_slots：提供方节点上的每个节点一个。
  • max_wal_senders：提供方节点上的每个节点一个。
  • track_commit_timestamp：如果需要最后或第一个更新胜出冲突解决方案，请将其设置为 on。
  • listen_addresses：必须包含 AlloyDB Omni IP 地址，或通过覆盖 CIDR 块提及。

您可以使用任何查询工具（例如 psql）检查这些参数。

调整 AlloyDB Omni 订阅方集群上的参数

pglogical 扩展程序还需要在 AlloyDB Omni 订阅方进行一组最少的参数调整。您必须将 pglogical 附加到 DATA_DIR/postgresql.conf 文件中的 shared_preload_libraries 参数。如果集群中的任何数据库都充当提供方数据库，请对提供方数据库进行所需的参数更改。

将 DATA_DIR 替换为数据目录的文件系统路径，例如 /home/$USER/alloydb-data。

  1. 调整参数：

    sudo sed -r -i "s|(shared_preload_libraries\s*=\s*)'(.*)'.*$|\1'\2,pglogical'|" DATA_DIR/postgresql.conf

  2. 验证参数是否设置正确：

    grep -iE 'shared_preload_libraries' DATA_DIR/postgresql.conf

  3. 重启 AlloyDB Omni 以使参数更改生效：

    Docker 

     docker container restart CONTAINER_NAME

    将 CONTAINER_NAME 替换为您在安装 AlloyDB Omni 容器时为其分配的名称。

    Podman 

     podman container restart CONTAINER_NAME

    将 CONTAINER_NAME 替换为您在安装 AlloyDB Omni 容器时为其分配的名称。

  4. 为其他提供程序数据库参数设置 AlloyDB Omni 默认值：

    • max_worker_processes：每个提供方数据库一个，每个订阅方节点一个。
    • track_commit_timestamp：如果需要最后或第一个更新胜出冲突解决方案，请将其设置为 on。

  5. 确认所有参数值均设置正确：

    Docker 

     docker exec CONTAINER_NAME psql -h localhost -U postgres -c "
 SELECT name, setting
   FROM pg_catalog.pg_settings
  WHERE name IN ('listen_addresses',
                 'wal_level',
                 'shared_preload_libraries',
                 'max_worker_processes',
                 'max_replication_slots',
                 'max_wal_senders',
                 'track_commit_timestamp')
      ORDER BY name;"

    Podman 

     podman exec CONTAINER_NAME psql -h localhost -U postgres -c "
 SELECT name, setting
   FROM pg_catalog.pg_settings
  WHERE name IN ('listen_addresses',
                 'wal_level',
                 'shared_preload_libraries',
                 'max_worker_processes',
                 'max_replication_slots',
                 'max_wal_senders',
                 'track_commit_timestamp')
      ORDER BY name;"

对 AlloyDB Omni 订阅方集群进行基于主机的身份验证调整

pglogical 会与 AlloyDB Omni 订阅者数据库建立本地 TCP 连接。因此，您必须将订阅者的主机服务器的 IP 地址添加到 AlloyDB Omni DATA_DIR/pg_hba.conf 文件中。

  1. 将针对新 pglogical_replication 用户的本地服务器信任身份验证条目添加到 DATA_DIR/pg_hba.conf 文件中：

    echo -e "# pglogical entries:
host all pglogical_replication samehost trust
" | column -t | sudo tee -a DATA_DIR/pg_hba.conf

  2. 验证条目是否正确：

    tail -2 DATA_DIR/pg_hba.conf

  3. 重启 AlloyDB Omni 以使身份验证更改生效：

    Docker 

    docker container restart CONTAINER_NAME

    Podman 

    podman container restart CONTAINER_NAME

在提供方和订阅方集群中创建 pglogical 用户

您必须在提供方集群和订阅方集群中都创建新用户。pglogical 要求用户同时拥有 superuser 和 replication 权限。

  1. 在 Google Cloud AlloyDB 提供程序集群中，创建用户并授予 alloydbsuperuser 角色：

    CREATE USER pglogical_replication LOGIN PASSWORD 'secret';
ALTER USER pglogical_replication WITH replication;
GRANT alloydbsuperuser TO pglogical_replication;

  2. 在 AlloyDB Omni 订阅方集群中，创建用户并授予 replication 和 superuser 属性：

    CREATE USER pglogical_replication LOGIN PASSWORD 'secret';
ALTER USER pglogical_replication WITH replication;
ALTER USER pglogical_replication WITH superuser;

向 Google Cloud AlloyDB 提供程序数据库添加 pglogical 和节点

  1. 授予所需权限。

    您必须在每个数据库中安装 pglogical 扩展程序，并向 pglogical 数据库用户授予 usage 权限。在 Google Cloud AlloyDB 中，您必须授予对 pglogical 架构的权限。

    例如，如果您的数据库是 my_test_db，请针对 Google Cloud AlloyDB 提供程序数据库运行以下命令：

       \c my_test_db;
 CREATE EXTENSION IF NOT EXISTS pglogical;
 GRANT usage ON SCHEMA pglogical TO pglogical_replication;
-- For Google Cloud AlloyDB we also need to manually grant privileges:
 GRANT ALL PRIVILEGES ON ALL tables IN SCHEMA pglogical TO pglogical_replication;

  2. 为提供方数据库创建 pglogical 节点。 node_name 是任意的，dsn 字符串必须是指向同一数据库的有效 TCP 连接。对于 Google Cloud AlloyDB，dsn 的主机部分是为主实例提供的 IP 地址。

    对于 Google Cloud AlloyDB，不允许使用信任身份验证，并且 dsn 中必须包含密码参数。参数。

    例如，对于 my_test_db 数据库，请运行以下命令：

    SELECT pglogical.create_node(node_name := 'provider', dsn := 'host=SERVER_IP_ADDRESS
port=5432 dbname=my_test_db user=pglogical_replication password=secret');

创建表并将其添加到默认复制集

创建一个表，并将其添加到 Google CloudAlloyDB 提供程序数据库上的默认复制集。

  1. 在提供程序数据库中创建一个名为 test_table_1 的测试表：

    CREATE TABLE test_table_1 (col1 INT PRIMARY KEY);
INSERT INTO test_table_1 VALUES (1),(2),(3);

  2. 为各个表授予 SELECT 权限，或运行 GRANT SELECT ON ALL TABLES 命令。要加入复制集的任何表都必须向复制用户 pglogical_replication 授予查询权限。

    GRANT SELECT ON ALL TABLES IN SCHEMA public TO pglogical_replication;

  3. 将测试表手动添加到默认复制集中。您可以创建自定义 pglogical 复制集，也可以使用默认的复制集。在您创建扩展程序时，系统会创建多个默认复制集，例如 default、default_insert_only 和 ddl_sql。您可以单独向复制集添加表和序列，也可以为指定架构一次性添加所有表和序列。

    -- Add the specified table to the default replication set:
SELECT pglogical.replication_set_add_table(set_name := 'default', relation := 'test_table_1', synchronize_data := TRUE);

-- Check which tables have been added to all replication sets:
SELECT * FROM pglogical.replication_set_table;

  4. （可选）添加指定架构（例如 public）中的所有表：

    -- Add all "public" schema tables to the default replication set:
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

-- Check which tables have been added to all replication sets:
SELECT * FROM pglogical.replication_set_table;

-- Add all "public" schema sequences to the default replication:
SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public']);

 -- Check which sequences have been added to all replication sets:
SELECT * FROM pglogical.replication_set_seq;

  5. 从 default 复制集中移除该表。如果架构中存在没有主键的表，您可以将其设置为仅进行 INSERT 复制，也可以使用与 ALTER TABLE 命令搭配使用的 REPLICA IDENTITY 功能设置用于唯一标识行的列。如果您使用 replication_set_add_all_tables 函数自动将这些表添加到 default 复制集，则必须手动将它们从该复制集中移除，并将它们添加到 default_insert_only 集。

    -- Remove the table from the **default** replication set:
SELECT pglogical.replication_set_remove_table(set_name := 'default', relation := 'test_table_2');

    -- Manually add to the **default_insert_only** replication set:
SELECT pglogical.replication_set_add_table(set_name := 'default_insert_only', relation := 'test_table_2');

    您可以根据需要，如果想自动将新创建的表添加到复制集，请按照 pglogical 源中的建议添加 pglogical_assign_repset 触发器。

将数据库复制到 AlloyDB Omni 订阅方集群

  1. 使用 pg_dump 实用程序创建源数据库的仅架构备份。

  2. 使用 Google Cloud AlloyDB 主实例的 IP 地址，从 AlloyDB Omni 订阅方服务器运行 pg_dump 命令。

    pg_dump -h SERVER_IP_ADDRESS -U postgres --create --schema-only my_test_db > my_test_db.schema-only.sql

  3. 将备份导入订阅者 AlloyDB Omni 服务器上的订阅者数据库：

    Docker 

    docker exec -i CONTAINER_NAME psql -h localhost -U postgres < my_test_db.schema-only.sql

    Podman 

    podman exec -i CONTAINER_NAME psql -h localhost -U postgres < my_test_db.schema-only.sql

忽略 alloydbsuperuser not existing 等错误。此角色仅适用于Google Cloud AlloyDB。

这会创建数据库和架构，但不包含任何行数据。pglogical 扩展程序会复制行数据。手动复制或重新创建所需的任何其他用户或角色。

在 AlloyDB Omni 订阅者数据库中创建节点和订阅

  1. 在 AlloyDB Omni 订阅者数据库上创建节点：

    Docker 

    docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=localhost port=5432 dbname=my_test_db user=pglogical_replication');"

    Podman 

    podman exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=localhost port=5432 dbname=my_test_db user=pglogical_replication');"

  2. 在订阅者数据库中创建一个订阅，指向Google Cloud AlloyDB 提供程序数据库的主实例。

    Docker 

    docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT pglogical.create_subscription(subscription_name := 'test_sub_1', provider_dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');"

    Podman 

    podman exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT pglogical.create_subscription(subscription_name := 'test_sub_1', provider_dsn := 'host=SERVER_IP_ADDRESS port=5432 dbname=my_test_db user=pglogical_replication password=secret');"

  3. 复制时间可能从几秒到几分钟不等，具体取决于表大小和要复制的数据，之后初始数据应会从提供方复制到订阅方：

    Docker 

    docker exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT * FROM test_table_1 ORDER BY 1;"

    Podman 

    podman exec CONTAINER_NAME psql -h localhost -U postgres -d my_test_db -c "
SELECT * FROM test_table_1 ORDER BY 1;"

    添加到提供方数据库中的其他行也会在几秒钟内复制。

其他 pglogical 部署注意事项

pglogical 扩展程序具有许多本文档未介绍的高级功能。其中许多功能都适用于您的实现。您可以考虑使用以下高级功能：

  • 冲突解决
  • 多主和双向复制
  • 包含序列
  • 切换和故障切换过程

后续步骤