MogDB=# select version();  
                                                                       version                                                    
                   
----------------------------------------------------------------------------------------------------------------------------------
--------------------
 (MogDB 5.0.1 build ae6d2ada) compiled at 2023-08-16 09:07:43 commit 0 last mr 1804  on x86_64-unknown-linux-gnu, compiled by g++ 
(GCC) 7.3.0, 64-bit
(1 row)
openGauss=# select version();  
                                                                       version                                           
                             
-------------------------------------------------------------------------------------------------------------------------
-----------------------------
 (openGauss 5.0.3 build 89d144c2) compiled at 2024-07-31 20:59:31 commit 0 last mr   on x86_64-unknown-linux-gnu, compile
d by g++ (GCC) 7.3.0, 64-bit
(1 row)

一、使用订阅发布的前提

正常运行的mogdb数据库实例

参数wal_level = logical，max_replication_slots > max_wal_senders

具有sysadmin或者replication,OPRADMIN权限的用户

发布者与订阅者的schema名要保持一致

create user repluser REPLICATION OPRADMIN encrypted password 'repluser@123';

vi pg_hba.conf
host  replication  repluser 0.0.0.0/0 sha256

auxdb=# select name,setting from pg_settings where name in ('wal_level','max_replication_slots','max_wal_senders');
         name          | setting 
-----------------------+---------
 max_replication_slots | 32
 max_wal_senders       | 16
 wal_level             | logical
(3 rows)

--测试表（所有节点）

create table logicaltest(id serial, col1 text, ctime timestamp);
alter table logicaltest REPLICA IDENTITY full;

--初始数据（源） Mogdb 5.0.1

insert into logicaltest select generate_series(1,5),md5(random()),now();
auxdb=# select * from logicaltest;
 id |               col1               |           ctime            
----+----------------------------------+----------------------------
  1 | c95fd7c2ec4bf63ba7e859f69b8b0978 | 2024-09-06 10:48:23.354204
  2 | baa878dd11b96343ae50baf99745ca6b | 2024-09-06 10:48:23.354204
  3 | a968b3cc7345cb1bef4b2a1713044eca | 2024-09-06 10:48:23.354204
  4 | b4d544960a7be03a42cc468a27dfb329 | 2024-09-06 10:48:23.354204
  5 | cc88a4bc832a539f6fddc31502bc54cf | 2024-09-06 10:48:23.354204
(5 rows)

--发布者mypub501

create publication mypub501 for table logicaltest;
auxdb=# select * from pg_publication;
 pubname  | pubowner | puballtables | pubinsert | pubupdate | pubdelete 
----------+----------+--------------+-----------+-----------+-----------
 mypub501 |       10 | f            | t         | t         | t
(1 row)

--订阅者mysub501

gs_guc generate  -D $GAUSSHOME/bin -o subscription
repluser@123
create subscription mysub501 connection 'dbname=auxdb host=192.168.100.81 port=26001 user=repluser password=repluser@123' publication mypub501; --自动建复制槽


--源端
auxdb=# select * from logicaltest;
 id |               col1               |           ctime           
----+----------------------------------+---------------------------
  1 | 945a52dc5be0c643016066921d801adf | 2024-09-09 16:36:52.05934
  2 | 06fe5f009c4c3a76c845779963db3bd6 | 2024-09-09 16:36:52.05934
  3 | 71a498213e4db7d8e635cac3bd5bf9e5 | 2024-09-09 16:36:52.05934
  4 | 4e16ee4bfffdc277d8c2b4d4439bb04c | 2024-09-09 16:36:52.05934
  5 | 808ee0cb193a63209382640aea84568e | 2024-09-09 16:36:52.05934
(5 rows)

auxdb=# delete from logicaltest where col1 = '71a498213e4db7d8e635cac3bd5bf9e5';
DELETE 1
                               ^
auxdb=# update logicaltest set col1='mwl' where id = 1;
UPDATE 1

--目标端
auxdb=# select * from logicaltest;
 id |               col1               |           ctime           
----+----------------------------------+---------------------------
  2 | 06fe5f009c4c3a76c845779963db3bd6 | 2024-09-09 16:36:52.05934
  4 | 4e16ee4bfffdc277d8c2b4d4439bb04c | 2024-09-09 16:36:52.05934
  5 | 808ee0cb193a63209382640aea84568e | 2024-09-09 16:36:52.05934
  1 | mwl                              | 2024-09-09 16:36:52.05934
(4 rows)

-- truncate table 不同步

-- 两边表字段都要添加
ALTER TABLE logicaltest ADD COLUMN age INT;

insert into logicaltest select generate_series(1,5),md5(random()),now(),1;

ALTER TABLE logicaltest ADD COLUMN name varchar(10);

insert into logicaltest select generate_series(1,5),md5(random()),now(),1,'wjs';


create subscription mysub501 connection 'dbname=auxdb host=192.168.100.81 port=26001 user=repluser password=repluser@123' publication mypub501 with(enabled=false);
auxdb=# select * from pg_subscription;
-[ RECORD 1 ]---+-----------------------------------------------------------------------------------------------------------------------------------------------
subdbid         | 16792
subname         | mysub501
subowner        | 10
subenabled      | f
subconninfo     | dbname=auxdb host=192.168.100.81 port=26001 user=repluser password=encryptOptP+EMnh2bBpjRIqnof3w3h2ByMOWY//cwZAE/uxKhNE1jxttzFEmbCkJpcNbzEWFX 
subslotname     | 
subsynccommit   | off
subpublications | {mypub501}
subbinary       | f
subskiplsn      | 0/0
update pg_subscription set subenabled = 't' where subname = 'mysub501';
--添加表
alter publication mypub501 add table xxx;
--刷新表
alter subscription mysub501 refresh publication;
--查看复制槽
select * from pg_replication_slots;
--添加逻辑复制槽
auxdb=# select pg_create_logical_replication_slot('mysub503','pgoutput');
 pg_create_logical_replication_slot 
------------------------------------
 (mysub503,0/56B79C0)
(1 row)

auxdb=# alter subscription mysub503 set(slot_name=mysub503);

--删除复制槽
SELECT pg_drop_replication_slot('mysub503');