需要注意的是,重建了subscription后,如果订阅端的表数据没有删除,可能会出现该报错信息,一定要把订阅端的需要复制的表清理掉,即可恢复正常。
#发布端查看槽信息,不可用
select * from pg_replication_slots ;
pgl_melotall_providerad95e5b_subscripbcef54f | pglogical_output | logical | 26716 | melotlog | f | f | | | | |
#订阅端查看日志如下:
2021-05-28 09:34:47.995 CST,,"melotall",23629,,60b048b7.5c4d,1,"",2021-05-28 09:34:47 CST,18/0,0,LOG,00000,"starting apply for subscription subscription_melotlog",,,,,,,,,"pglogical apply 16401:3169801459"
2021-05-28 09:34:47.995 CST,,"melotall",23629,,60b048b7.5c4d,2,"",2021-05-28 09:34:47 CST,18/0,0,ERROR,XX000,"subscriber subscription_melotlog initialization failed during nonrecoverable step (d), please try the setup again",,,,,,,,,"pglogical apply 16401:3169801459"
2021-05-28 09:34:47.995 CST,,"melotall",23629,,60b048b7.5c4d,3,"",2021-05-28 09:34:47 CST,18/0,0,LOG,00000,"apply worker [23629] at slot 8 generation 9 exiting with error",,,,,,,,,"pglogical apply 16401:3169801459"
2021-05-28 09:34:47.995 CST,,,22889,,60b04647.5969,12,,2021-05-28 09:24:23 CST,,0,LOG,00000,"background worker ""pglogical apply 16401:3169801459"" (PID 23629) exited with exit code 1",,,,,,,,,""
#发布端查出复制的表
melotlog=# select relname from pglogical.tables where set_name is not null;
tbl_test
#清理订阅端复制表的数据
truncate tbl_test;
#发布端再次查看槽信息,已恢复正常,订阅端也重新同步了表的数据
pgl_melotall_providerad95e5b_subscripbcef54f | pglogical_output | logical | 26716 | melotlog | f | t | 3313 | | 50877264 | 1
B/4417F948 | 1B/4417F980
参考:
https://www.postgresql.org/message-id/1505262042242-0.post%40n3.nabble.com
https://www.postgresql.org/message-id/1505269792958-0.post%40n3.nabble.com