1.查看coupon到prime 的逻辑复制表
# A 和 B 切换前进行
A 查看前置信息192.21.13.51
coupon=# select * from pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
---------------+----------+--------------+-----------+-----------+-----------+-------------
coupon_pub_01 | 10 | f | t | t | t | t
coupon_pub_06 | 10 | f | t | t | t | t
coupon_pub_07 | 10 | f | t | t | t | t
(3 rows)
coupon=# select * from pg_publication_tables;
pubname | schemaname | tablename
---------------+------------+--------------------------
coupon_pub_01 | public | t_coupon_profile_info
coupon_pub_01 | public | t_coupon_activity_info
coupon_pub_07 | public | t_coupon_account_destroy
coupon_pub_06 | public | t_coupon_account_revoke
(4 rows)
B 查看前置信息192.21.13.101
coupon=# select * from pg_subscription;
subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications
---------+--------------+----------+------------+-----------------------------------------------------------------------------------+----------------+---------------+-----------------
16408 | prime_sub_06 | 10 | t | host=192.21.13.51 port=1921 dbname=coupon user=postgres password=@baishengDB1122 | coupon_slot_06 | off | {coupon_pub_06}
16408 | prime_sub_05 | 10 | t | host=192.21.13.51 port=1921 dbname=coupon user=prime_logical password=Yumc123 | coupon_slot_01 | off | {coupon_pub_01}
16408 | prime_sub_07 | 10 | t | host=192.21.13.51 port=1921 dbname=coupon user=prime_logical password=Yumc123 | coupon_slot_07 | off | {coupon_pub_07}
(3 rows)
2.2025/1/21切换前清除逻辑复制
# A 和 B 切换前进行
B 192.21.13.101删除订阅
drop subscription prime_sub_05 ;
drop subscription prime_sub_06 ;
drop subscription prime_sub_07;
A 192.21.13.51删除发布
drop publication coupon_pub_01；
drop publication coupon_pub_06；
drop publication coupon_pub_07；
3.查看逻辑复制状态
# A 和 B 切换前进行
B 192.21.13.101
coupon=# select * from pg_stat_subscription ;
A 192.21.13.51
coupon=# select * from pg_replication_slots;
4.重建逻辑复制
# A 和 B 切换后进行
B 备份表 192.25.113.61
create table t_coupon_profile_info_bak20250120 as select * from t_coupon_profile_info;
create table t_coupon_activity_info_bak20250120 as select * from t_coupon_activity_info;
create table t_coupon_account_destroy_bak20250120 as select * from t_coupon_account_destroy ;
create table t_coupon_account_revoke_bak20250120 as select * from t_coupon_account_revoke ;
B 清空表192.25.113.61
truncate table t_coupon_profile_info;
truncate table t_coupon_activity_info;
truncate table t_coupon_account_destroy;
truncate table t_coupon_account_revoke;
A 创建发布（192.25.113.25）
CREATE PUBLICATION coupon_pub_01 FOR TABLE t_coupon_profile_info,t_coupon_activity_info,t_coupon_account_revoke,t_coupon_account_destroy;
select * from pg_publication_tables;
B 创建订阅（192.25.113.61）
CREATE SUBSCRIPTION prime_sub_01 CONNECTION 'host=192.25.113.25 port=1921 dbname=coupon user=prime_logical password=Yumc123' PUBLICATION coupon_pub_01 WITH (create_slot = true ,slot_name = 'coupon_slot_01' ,copy_data = true );
select * from pg_stat_subscription;
验证逻辑同步
B:
select * from pg_stat_subscription;
A：
select * from pg_stat_replication;
select * from pg_publication;
select * from pg_publication_tables;
5.2025/1/22主备会切换前删除逻辑复制
# A 和 B 切换前进行
192.25.113.61删除订阅
drop subscription prime_sub_01 ；
192.25.113.25 删除发布
drop publication coupon_pub_01；
6.重建逻辑复制
# A 和 B 切换后进行
192.21.13.101
create table t_coupon_profile_info_bak20250121 as select * from t_coupon_profile_info;
create table t_coupon_activity_info_bak20250121 as select * from t_coupon_activity_info;
create table t_coupon_account_destroy_bak20250121 as select * from t_coupon_account_destroy ;
create table t_coupon_account_revoke_bak20250121 as select * from t_coupon_account_revoke ;
192.21.13.101
truncate table t_coupon_profile_info;
truncate table t_coupon_activity_info;
truncate table t_coupon_account_destroy;
truncate table t_coupon_account_revoke;
A 创建发布（192.21.13.51）
CREATE PUBLICATION coupon_pub_01 FOR TABLE t_coupon_profile_info,t_coupon_activity_info,t_coupon_account_revoke,t_coupon_account_destroy;
select * from pg_publication_tables;
B 创建订阅（192.21.13.101）
CREATE SUBSCRIPTION prime_sub_01 CONNECTION 'host=192.21.13.51 port=1921 dbname=coupon user=prime_logical password=Yumc123' PUBLICATION coupon_pub_01 WITH (create_slot = true ,slot_name = 'coupon_slot_01' ,copy_data = true );
select * from pg_stat_subscription;
验证逻辑同步
B :
select * from pg_stat_subscription;
A ：
select * from pg_stat_replication;
select * from pg_publication;
select * from pg_publication_tables;
select count(*) from t_coupon_profile_info;
select count(*) from t_coupon_activity_info;
select count(*) from t_coupon_account_destroy;
select count(*) from t_coupon_account_revoke;
