# 一、变更内容
二、变更文档
前置工作
# 中间库地址
172.25.189.18
# 库信息
duola-trans
# 槽信息
able_ods_duola_t_coupon_used_log
table_ods_duola_mul_t_order_coupon_detail
task_ods_duola_t_b2c_dyl_order_refund
table_dim_duola_t_meta_channel_info
table_dim_duola_t_meta_channel_info_log
task_ods_duola_mul_order
table_ods_duola_t_b2c_mt_order_coupon_detail
task_ods_duola_t_b2c_mt_order_refund
table_ods_duola_t_order_gift_card_detail
task_ods_duola_t_b2c_dyl_order
ods_duola_mul_order_refund
table_ods_duola_t_order_prime_detail
table_ods_duola_t_b2c_dyl_order_coupon_detail
task_ods_duola_t_b2c_mt_order
# 确认 复制槽的 active 状态
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_ods_duola_t_coupon_used_log';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_ods_duola_mul_t_order_coupon_detail';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_dyl_order_refund';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_dim_duola_t_meta_channel_info';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_dim_duola_t_meta_channel_info_log';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'task_ods_duola_mul_order';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_ods_duola_t_b2c_mt_order_coupon_detail';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_mt_order_refund';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_ods_duola_t_order_gift_card_detail';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_dyl_order';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'ods_duola_mul_order_refund';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_ods_duola_t_order_prime_detail';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_ods_duola_t_b2c_dyl_order_coupon_detail';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_mt_order';
操作WBS
-
记录槽信息
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_order_gift_card_detail';
-[ RECORD 1 ]-------+-----------------------------------------
slot_name | table_ods_duola_t_order_gift_card_detail
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656518007
restart_lsn | E95/3622F990
confirmed_flush_lsn | E95/36421A80
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
一共14个复制槽
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_coupon_used_log';
-[ RECORD 1 ]-------+----------------------------------
slot_name | table_ods_duola_t_coupon_used_log
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656510544
restart_lsn | E95/34E1D2F8
confirmed_flush_lsn | E95/350D29C0
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_mul_t_order_coupon_detail';
-[ RECORD 1 ]-------+------------------------------------------
slot_name | table_ods_duola_mul_t_order_coupon_detail
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656529951
restart_lsn | E95/383ED5F8
confirmed_flush_lsn | E95/386A4508
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_dyl_order_refund';
-[ RECORD 1 ]-------+--------------------------------------
slot_name | task_ods_duola_t_b2c_dyl_order_refund
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656513564
restart_lsn | E95/35637A10
confirmed_flush_lsn | E95/35734830
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'table_dim_duola_t_meta_channel_info';
-[ RECORD 1 ]-------+------------------------------------
slot_name | table_dim_duola_t_meta_channel_info
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656515054
restart_lsn | E95/35A57EE0
confirmed_flush_lsn | E95/35DD1EB0
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'table_dim_duola_t_meta_channel_info_log';
-[ RECORD 1 ]-------+----------------------------------------
slot_name | table_dim_duola_t_meta_channel_info_log
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656515054
restart_lsn | E95/35A57EE0
confirmed_flush_lsn | E95/35C70A00
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_mul_order';
-[ RECORD 1 ]-------+-------------------------
slot_name | task_ods_duola_mul_order
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656543925
restart_lsn | E95/3A9AE3B0
confirmed_flush_lsn | E95/3A9E5B50
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_b2c_mt_order_coupon_detail';
-[ RECORD 1 ]-------+---------------------------------------------
slot_name | table_ods_duola_t_b2c_mt_order_coupon_detail
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656528326
restart_lsn | E95/37F7E100
confirmed_flush_lsn | E95/383CAE88
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_mt_order_refund';
-[ RECORD 1 ]-------+-------------------------------------
slot_name | task_ods_duola_t_b2c_mt_order_refund
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656519457
restart_lsn | E95/3665FB08
confirmed_flush_lsn | E95/3692D5A8
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_order_gift_card_detail';
-[ RECORD 1 ]-------+-----------------------------------------
slot_name | table_ods_duola_t_order_gift_card_detail
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656518007
restart_lsn | E95/3622F990
confirmed_flush_lsn | E95/36421A80
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_dyl_order';
-[ RECORD 1 ]-------+-------------------------------
slot_name | task_ods_duola_t_b2c_dyl_order
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656526781
restart_lsn | E95/37B50608
confirmed_flush_lsn | E95/37E92098
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'ods_duola_mul_order_refund';
-[ RECORD 1 ]-------+---------------------------
slot_name | ods_duola_mul_order_refund
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656501587
restart_lsn | E95/333EB4E8
confirmed_flush_lsn | E95/33562658
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_order_prime_detail';
-[ RECORD 1 ]-------+-------------------------------------
slot_name | table_ods_duola_t_order_prime_detail
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656518007
restart_lsn | E95/3622F990
confirmed_flush_lsn | E95/36400A48
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_b2c_dyl_order_coupon_detail';
-[ RECORD 1 ]-------+----------------------------------------------
slot_name | table_ods_duola_t_b2c_dyl_order_coupon_detail
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656543925
restart_lsn | E95/3A9AE3B0
confirmed_flush_lsn | E95/5770DA10
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
duola-trans=# SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_mt_order';
-[ RECORD 1 ]-------+------------------------------
slot_name | task_ods_duola_t_b2c_mt_order
plugin | wal2json
slot_type | logical
datoid | 16422
database | duola-trans
temporary | f
active | f
active_pid |
xmin |
catalog_xmin | 2656543925
restart_lsn | E95/3A9AE3B0
confirmed_flush_lsn | E95/4554EBE0
wal_status | extended
safe_wal_size |
two_phase | f
conflicting | f
2. ### 和开发约操作窗口
# 通知开发，并且等待开发关闭job
3. ### 停用槽
# 当 复制槽的状态变成 false后操作删除
# 查询逻辑复制槽
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_coupon_used_log';
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_mul_t_order_coupon_detail';
SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_dyl_order_refund';
SELECT * FROM pg_replication_slots where slot_name = 'table_dim_duola_t_meta_channel_info';
SELECT * FROM pg_replication_slots where slot_name = 'table_dim_duola_t_meta_channel_info_log';
SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_mul_order';
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_b2c_mt_order_coupon_detail';
SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_mt_order_refund';
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_order_gift_card_detail';
SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_dyl_order';
SELECT * FROM pg_replication_slots where slot_name = 'ods_duola_mul_order_refund';
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_order_prime_detail';
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_b2c_dyl_order_coupon_detail';
SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_mt_order';
4. ### 删除槽
#
# 删除逻辑复制槽， 活跃状态的槽位无法删除，需要停止消费job。
select pg_drop_replication_slot('table_ods_duola_t_coupon_used_log');
select pg_drop_replication_slot('table_ods_duola_mul_t_order_coupon_detail');
select pg_drop_replication_slot('task_ods_duola_t_b2c_dyl_order_refund');
select pg_drop_replication_slot('table_dim_duola_t_meta_channel_info');
select pg_drop_replication_slot('table_dim_duola_t_meta_channel_info_log');
select pg_drop_replication_slot('task_ods_duola_mul_order');
select pg_drop_replication_slot('table_ods_duola_t_b2c_mt_order_coupon_detail');
select pg_drop_replication_slot('task_ods_duola_t_b2c_mt_order_refund');
select pg_drop_replication_slot('table_ods_duola_t_order_gift_card_detail');
select pg_drop_replication_slot('task_ods_duola_t_b2c_dyl_order');
select pg_drop_replication_slot('ods_duola_mul_order_refund');
select pg_drop_replication_slot('table_ods_duola_t_order_prime_detail');
select pg_drop_replication_slot('table_ods_duola_t_b2c_dyl_order_coupon_detail');
select pg_drop_replication_slot('task_ods_duola_t_b2c_mt_order');
5. ### 重建槽
#
SELECT pg_create_logical_replication_slot('table_ods_duola_t_coupon_used_log','wal2json');
SELECT pg_create_logical_replication_slot('table_ods_duola_mul_t_order_coupon_detail','wal2json');
SELECT pg_create_logical_replication_slot('task_ods_duola_t_b2c_dyl_order_refund','wal2json');
SELECT pg_create_logical_replication_slot('table_dim_duola_t_meta_channel_info','wal2json');
SELECT pg_create_logical_replication_slot('table_dim_duola_t_meta_channel_info_log','wal2json');
SELECT pg_create_logical_replication_slot('task_ods_duola_mul_order','wal2json');
SELECT pg_create_logical_replication_slot('table_ods_duola_t_b2c_mt_order_coupon_detail','wal2json');
SELECT pg_create_logical_replication_slot('task_ods_duola_t_b2c_mt_order_refund','wal2json');
SELECT pg_create_logical_replication_slot('table_ods_duola_t_order_gift_card_detail','wal2json');
SELECT pg_create_logical_replication_slot('task_ods_duola_t_b2c_dyl_order','wal2json');
SELECT pg_create_logical_replication_slot('ods_duola_mul_order_refund','wal2json');
SELECT pg_create_logical_replication_slot('table_ods_duola_t_order_prime_detail','wal2json');
SELECT pg_create_logical_replication_slot('table_ods_duola_t_b2c_dyl_order_coupon_detail','wal2json');
SELECT pg_create_logical_replication_slot('task_ods_duola_t_b2c_mt_order','wal2json');
-
通知开发重启消费job
通知开发重启job，进行全量同步
-
观察槽状态
# 查询逻辑复制槽
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_coupon_used_log';
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_mul_t_order_coupon_detail';
SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_dyl_order_refund';
SELECT * FROM pg_replication_slots where slot_name = 'table_dim_duola_t_meta_channel_info';
SELECT * FROM pg_replication_slots where slot_name = 'table_dim_duola_t_meta_channel_info_log';
SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_mul_order';
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_b2c_mt_order_coupon_detail';
SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_mt_order_refund';
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_order_gift_card_detail';
SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_dyl_order';
SELECT * FROM pg_replication_slots where slot_name = 'ods_duola_mul_order_refund';
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_order_prime_detail';
SELECT * FROM pg_replication_slots where slot_name = 'table_ods_duola_t_b2c_dyl_order_coupon_detail';
SELECT * FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_mt_order';
#
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_ods_duola_t_coupon_used_log';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_ods_duola_mul_t_order_coupon_detail';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_dyl_order_refund';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_dim_duola_t_meta_channel_info';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_dim_duola_t_meta_channel_info_log';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'task_ods_duola_mul_order';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_ods_duola_t_b2c_mt_order_coupon_detail';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_mt_order_refund';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_ods_duola_t_order_gift_card_detail';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_dyl_order';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'ods_duola_mul_order_refund';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_ods_duola_t_order_prime_detail';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'table_ods_duola_t_b2c_dyl_order_coupon_detail';
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name = 'task_ods_duola_t_b2c_mt_order';
# 合并查询 重建后的复制槽 状态
SELECT slot_name,plugin,slot_type,active,restart_lsn,confirmed_flush_lsn FROM pg_replication_slots where slot_name in ('table_ods_duola_t_coupon_used_log','table_ods_duola_mul_t_order_coupon_detail','task_ods_duola_t_b2c_dyl_order_refund','table_dim_duola_t_meta_channel_info','table_dim_duola_t_meta_channel_info_log','task_ods_duola_mul_order','table_ods_duola_t_b2c_mt_order_coupon_detail','task_ods_duola_t_b2c_mt_order_refund','table_ods_duola_t_order_gift_card_detail','task_ods_duola_t_b2c_dyl_order','ods_duola_mul_order_refund','table_ods_duola_t_order_prime_detail','table_ods_duola_t_b2c_dyl_order_coupon_detail','task_ods_duola_t_b2c_mt_order');
8. ### 完成
