openGauss-逻辑复制搭建
配置环境
发布端x.x.x.69
订阅段x.x.x.19
发布订阅端均为不同集群的主库。
下文以69,19为代替
发布端69操作:
建表,建同步用户,改参数
openGauss=# create table t_1107 (id int primary key,name varchar(10));
openGauss=# create user rep replication opradmin IDENTIFIED BY 'Abcd@1234';
openGauss=# grant SELECT ON t_1107 TO rep;
[omm@omm1 ~]$ gs_guc reload -N all -I all -c "wal_level=logical"
[omm@omm1 ~]$ gs_guc reload -N all -I all -h "host replication rep 0.0.0.0/0 sha256"
[omm@omm1 ~]$ gs_om -t restart
订阅端19操作:
建表,改参数
[omm@omm1 ~]$ gs_guc reload -N all -I all -c "wal_level=logical"
[omm@omm1 ~]$ gs_om -t restart
openGauss=# create table t_1107 (id int primary key,name varchar(10));
发布端69发布:
(-S 后接复杂密码)
openGauss=# create publication pub for table t_1107 ;
CREATE PUBLICATION
[omm@omm1 bin]$ gs_guc -S Abcdqwe@1234 -D /openGauss/install/app/bin/ -o subscription generate
订阅端19订阅:
openGauss=# CREATE SUBSCRIPTION sub CONNECTION 'host=x.x.x.69 port=5433 dbname=postgres user=rep password=Abcd@1234' PUBLICATION pub;
NOTICE: created replication slot "sub" on publisher
CREATE SUBSCRIPTION
openGauss=# select * from pg_subscription;
subdbid | subname | subowner | subenabled | subconninfo
| subslotname | subsynccommit | subpublications | subbinary
---------+---------+----------+------------+-----------------------------------------------------------------------------------------------------------
------------------------------+-------------+---------------+-----------------+-----------
14729 | sub | 10 | t | host=x.x.x.69 port=5433 dbname=postgres user=rep password=encryptOptlAc7Lc80iT6wZbVWB3l0sWuN9cjrWs7lgPm5
8nbwFVr0QVV1x0zLsSUUWtXongLJ | sub | off | {pub} | f
(1 row)
发布端69检查发布状态:
openGauss=# select * from pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete
---------+----------+--------------+-----------+-----------+-----------
pub | 10 | f | t | t | t
(1 row)
openGauss=# select * from pg_replication_slots where slot_name='sub';
slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn | dummy_standby | confirmed_flush
-----------+----------+-----------+--------+----------+--------+------+--------------+-------------+---------------+-----------------
sub | pgoutput | logical | 15730 | postgres | t | | 45228 | 0/36A15018 | f | 0/36A15068
(1 row)
发布端69写入数据,在订阅端能同步到数据。
本文来自博客园,作者:{dyy},转载请注明原文链接:{https://www.cnblogs.com/ddlearning/}