磐维数据库逻辑复制发布

前提条件

数据库参数

• 逻辑日志目前从主机节点中抽取，默认关闭 SSL 连接，如果进行逻辑复制，需要先配置 GUC 参数 ssl=on。
• 设置 GUC 参数 wal_level=logical。
• 设置 GUC 参数 max_replication_slots 每个节点所需的复制槽数(物理流复制槽数+逻辑复制槽数）。
• 设置 GUC 参数 max_wal_senders，大于max_replication_slots
• 这四个参数修改，都需要重启数据库实例才能生效，生产数据库谨慎操作。

postgres=# select name,setting,context from pg_settings where name in
postgres-# ('ssl',
postgres(# 'wal_level',
postgres(# 'max_wal_senders',
postgres(# 'max_replication_slots');
         name          |   setting   |  context   
-----------------------+-------------+------------
 max_replication_slots | 8           | postmaster
 max_wal_senders       | 16          | postmaster
 ssl                   | on          | postmaster
 wal_level             | logical     | postmaster
(4 rows)

复制用户

• 具有sysadmin 或 replcation,OPRADMIN 权限的用户，这里举例为repl
• 创建复制用户

postgres=# create user repl with password '*******' replication;
CREATE ROLE
postgres=# \du repl 
            List of roles
 Role name | Attributes  | Member of 
-----------+-------------+-----------
 repl      | Replication | {}

白名单配置

• 添加订阅端的白名单

gs_guc reload -h 'host db1 repl 192.168.X.0/24 sha256'
gs_guc reload -h 'host replication repl 192.168.X.0/24 sha256'

赋予权限

• 将要发布的表的权限赋予给复制用户repl

db1=# GRANT CONNECT ON DATABASE db1 TO repl;
GRANT
db1=# GRANT USAGE ON SCHEMA sch1 TO repl;
GRANT
db1=# GRANT SELECT ON sch1.test TO repl;
GRANT

创建发布

• 创建逻辑复制发布

db1=# CREATE PUBLICATION pub_test FOR TABLE sch1.test;
CREATE PUBLICATION

• 查看逻辑发布的相关信息

db1=# select * from pg_publication;
 pubname  | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubddl | ignorerefreshmatview 
----------+----------+--------------+-----------+-----------+-----------+-------------+--------+----------------------
 pub_test |       10 | f            | t         | t         | t         | t           |      0 | t
(1 row)

db1=# select * from pg_publication_tables;
 pubname  | schemaname | tablename 
----------+------------+-----------
 pub_test | sch1       | test
(1 row)