在 PostgreSQL 中，逻辑复制是一种将数据更改从发布商数据库复制到一个或多个订阅方（可以是数据库或其他应用）的方法。您可以使用 AlloyDB Omni Kubernetes Operator 创建集群，并在这些集群上启用和配置逻辑复制。
本文档提供了示例，介绍了如何创建和配置发布方集群和订阅方集群。在阅读本文档之前，您应先熟悉 AlloyDB Omni 概览。您还应了解 PostgreSQL 逻辑复制的限制。
本页中的代码段是可用作模型的示例，您可以将值替换为 AlloyDB Omni 资源的值。
创建集群
创建发布商集群。
$ cat << EOF | kubectl apply -f - apiVersion: v1 kind: Secret metadata: name: db-pw-publisher type: Opaque data: publisher: "b2RzcGFzc3dvcmQ=" # Password is odspassword --- apiVersion: alloydbomni.dbadmin.goog/v1 kind: DBCluster metadata: name: publisher spec: primarySpec: adminUser: passwordRef: name: db-pw-publisher databaseVersion: "15.7.0" resources: memory: 10Gi cpu: 1 disks: - name: DataDisk size: 40Gi EOF创建订阅方集群。
$ cat << EOF | kubectl apply -f - apiVersion: v1 kind: Secret metadata: name: db-pw-subscriber type: Opaque data: subscriber: "b2RzcGFzc3dvcmQ=" # Password is odspassword --- apiVersion: alloydbomni.dbadmin.goog/v1 kind: DBCluster metadata: name: subscriber spec: primarySpec: adminUser: passwordRef: name: db-pw-subscriber databaseVersion: "15.7.0" resources: memory: 10Gi cpu: 1 disks: - name: DataDisk size: 40Gi EOF
配置发布商集群
配置发布商集群并创建表。（可选）您可以将数据作为测试发布，以确保其复制到订阅方。
将参数
wal_level更新为logical。$ kubectl patch dbclusters.al publisher -p '{"spec":{"primarySpec":{"parameters":{"wal_level":"logical"}}}}' --type=merge找到所需的 pod。
$ kubectl get pod -l "alloydbomni.internal.dbadmin.goog/dbcluster=publisher, alloydbomni.internal.dbadmin.goog/task-type=database, dbs.internal.dbadmin.goog/ha-role=Primary"登录发布商集群的数据库 pod。
NAME READY STATUS RESTARTS AGE al-2bce-publisher-0 3/3 Running 0 36m $ kubectl exec -ti al-2bce-publisher-0 -- /bin/bash创建一个名为
customer的数据库。CREATE DATABASE customer;可选：出于测试目的，请向数据库中添加一个表并插入一些数据。您可以使用这些数据观察从发布端到订阅端的数据复制情况。
$ psql -h localhost -U postgres customer customer=# CREATE TABLE COMPANY( customer(# ID INT PRIMARY KEY NOT NULL, customer(# NAME TEXT NOT NULL, customer(# AGE INT NOT NULL, customer(# SALARY REAL customer(# ); CREATE TABLE customer=# INSERT INTO COMPANY (ID,NAME,AGE,SALARY) VALUES customer-# (1, 'Quinn', 25, 65000.00), customer-# (2, 'Kim', 22, 72250.00), customer-# (3, 'Bola', 31, 53000.00), customer-# (4, 'Sasha', 33, 105000.00), customer-# (5, 'Yuri', 27, 85000.00); INSERT 0 5 customer=# \dt List of relations Schema | Name | Type | Owner --------+---------+-------+---------- public | company | table | postgres (1 row) customer=# select * from company; id | name | age | salary ----+-------+-----+-------- 1 | Quinn | 25 | 65000 2 | Kim | 22 | 72250 3 | Bola | 31 | 53000 4 | Sasha | 33 | 105000 5 | Yuri | 27 | 85000 (5 rows)创建用户
logicalreplica以用于复制并授予权限。CREATE USER logicalreplica WITH REPLICATION LOGIN PASSWORD '123';授予权限。此示例使用公共架构。
GRANT SELECT ON ALL TABLES IN SCHEMA public TO logicalreplica; GRANT USAGE ON SCHEMA public TO logicalreplica; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO logicalreplica;在
customer数据库中创建发布。CREATE PUBLICATION pub_customer; ALTER PUBLICATION pub_customer ADD TABLE company;
配置订阅方集群
让订阅方集群能够接收来自发布方集群的数据更新。
在订阅者数据库中将参数
wal_level设置为logical。$ kubectl patch dbclusters.al subscriber -p '{"spec":{"primarySpec":{"parameters":{"wal_level":"logical"}}}}' --type=merge找到所需的 pod。
$ kubectl get pod -l "alloydbomni.internal.dbadmin.goog/dbcluster=subscriber, alloydbomni.internal.dbadmin.goog/task-type=database, dbs.internal.dbadmin.goog/ha-role=Primary"登录订阅者集群数据库 pod。
$ kubectl get pod NAME READY STATUS RESTARTS AGE al-2bce-publisher-0 3/3 Running 0 20h $ kubectl exec -ti al-3513-subscriber-0 -- /bin/bash Defaulted container "database" out of: database, logrotate-agent, memoryagent, dbinit (init) postgres@al-3513-subscriber-0:/$找到发布商 pod 的 IP 地址，例如
10.116.14.190$ kubectl get service NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE al-publisher-rw-ilb ClusterIP 10.116.14.190 <none> 5432/TCP 21h从发布商处获取架构备份，作为发布商数据库中已发布数据的初始副本。逻辑复制不支持 DDL 复制。在逻辑复制开始之前，您计划复制的架构或表必须存在于目标（订阅方集群）中。
postgres@al-3513-subscriber-0:/$ pg_dump -h 10.116.14.190 -U postgres --create --schema-only customer > /tmp/customer.schema-only.sql在订阅者数据库中应用备份。
postgres@al-3513-subscriber-0:/$ psql -h localhost -U postgres < /tmp/customer.schema-only.sql可选：验证表中是否没有数据。
# There is no data in table company customer=# select * from company; id | name | age | salary ----+------+-----+-------- (0 rows)为数据库
customer创建订阅。postgres@al-3513-subscriber-0:/$ psql -h localhost -U postgres customer customer=# CREATE SUBSCRIPTION sub_customer CONNECTION 'host=10.116.14.190 port=5432 user=logicalreplica dbname=customer password=123' PUBLICATION pub_customer;可选：验证订阅方集群上的复制情况。
postgres@al-3513-subscriber-0:/$ psql -h localhost -U postgres customer customer=# select * from public.company; id | name | age | salary ----+-------+-----+-------- 1 | Quinn | 25 | 65000 2 | Kim | 22 | 72250 3 | Bola | 31 | 53000 4 | Sasha | 33 | 105000 5 | Yuri | 27 | 85000 (5 rows)在发布商集群中，向表格添加一行。
# On the publisher database $ kubectl exec -ti al-2bce-publisher-0 -- /bin/bash Defaulted container "database" out of: database, logrotate-agent, memoryagent, dbinit (init) postgres@al-2bce-publisher-0:/$ psql -h localhost -U postgres customer customer=# insert into company(id, name, age, salary) values (6, 'Alex', 39, 100000);在订阅方集群中，验证添加到发布方集群中的表的行是否已复制到订阅方集群中的表。
# On the subscriber database, data is synced. postgres@al-3513-subscriber-0:/$ psql -h localhost -U postgres customer customer=# select * from company; id | name | age | salary ----+-------+-----+-------- 1 | Quinn | 25 | 65000 2 | Kim | 22 | 72250 3 | Bola | 31 | 53000 4 | Sasha | 33 | 105000 5 | Yuri | 27 | 85000 6 | Alex | 39 | 100000 (6 rows)
手动创建其他表
与 pglogical 中的 replicate_ddl_command 不同，逻辑复制不会自动同步 DDL 更改。虽然开源工具 pgl_ddl_deploy 提供了解决方案，但您也可以在订阅方手动执行 DDL 命令。
为说明这一点，请在发布商集群的
customer数据库中创建一个名为finance的新表。# On the publisher database $ kubectl exec -ti al-2bce-publisher-0 -- /bin/bash Defaulted container "database" out of: database, logrotate-agent, memoryagent, dbinit (init) postgres@al-2bce-publisher-0:/$ psql -h localhost -U postgres customer customer=# create table finance (row text); CREATE TABLE customer=# insert into finance values ('critical data'); INSERT 0 1 customer=# ALTER PUBLICATION pub_customer ADD TABLE finance; ALTER PUBLICATION将新表添加到发布方集群后，您需要在订阅方中手动应用 DDL（表创建），然后在订阅方集群上运行以下命令来验证复制。
postgres@al-3513-subscriber-0:/$ psql -h localhost -U postgres customer customer=# create table finance (row text); CREATE TABLE customer=# ALTER SUBSCRIPTION sub_customer REFRESH PUBLICATION; ALTER SUBSCRIPTION customer=# select * from finance; row --------------- critical data (1 row)