在 AlloyDB for PostgreSQL 和 AlloyDB Omni 之间复制数据

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

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

pglogical 的关键组件

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

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

在此部署中，AlloyDB for PostgreSQL 服务是提供方，而本地 AlloyDB Omni 是订阅方。请注意，也可以进行相反的配置。

支持的身份验证方法

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

推荐的身份验证方法是信任身份验证，因为在密码身份验证方法中，密码以明文格式存储在由 pglogical 拥有的数据库表中。任何具有查询这些表的数据库权限的用户都可以在非二进制备份和 PostgreSQL 日志文件中看到以明文形式显示的这些密码。

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

准备工作

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

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

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

调整 AlloyDB for PostgreSQL 提供方的参数

pglogical 扩展程序需要对 AlloyDB for PostgreSQL 提供方集群进行一组最基本的参数调整。您必须将 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

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

您必须调整以下 AlloyDB for PostgreSQL 标志的参数：

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

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

对于其他必需的提供方节点数据库参数，您必须按如下所示设置 AlloyDB for PostgreSQL 默认值：

• 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. 在 DATA_DIR/pg_hba.conf 文件中为本地服务器添加特定于新 pglogical_replication 用户的信任身份验证条目：

   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. 在 AlloyDB for PostgreSQL 提供方集群中，创建用户并授予 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;
   

向 AlloyDB for PostgreSQL 提供方数据库添加 pglogical 和节点

1. 授予所需权限。

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

   例如，如果您的数据库是 my_test_db，请对 AlloyDB for PostgreSQL 提供方数据库运行以下命令：

      \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 连接。对于 AlloyDB for PostgreSQL，dsn 的主机部分是为主实例提供的 IP 地址。

   对于 AlloyDB for PostgreSQL，不允许使用信任身份验证，并且密码参数必须包含在 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');
   

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

创建一个表，并将其添加到 AlloyDB for PostgreSQL 提供方数据库上的默认复制集。

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. 使用 AlloyDB for PostgreSQL 主实例的 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 等错误。此角色特定于 AlloyDB for PostgreSQL。

这会创建数据库和架构，但不包含任何行数据。行数据由 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. 在订阅方数据库中创建订阅，指向 AlloyDB for PostgreSQL 提供方数据库的主实例。

   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 扩展程序具有许多本文档未介绍的高级功能。其中许多功能适用于您的实现。您可以考虑使用以下高级功能：

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

后续步骤

• 在 AlloyDB Omni 和其他数据库之间复制数据
• 使用 pglogical 复制进行切换和故障切换