postgreSQL逻辑订阅相关语句
发布端:
配置:
posgresql.conf
查看发布端订阅:
select * from pg_publication;
创建订阅:
create publication testpub1 for table t1;
删除订阅:
drop publication testpub1;
查看复制槽:
SELECT * FROM pg_replication_slots;
在订阅端创建订阅后,如果数据表数据量很大导致第一时间没有看到同步数据,可在发布端使用查看复制槽语句,查看是否有类似“_73021_sync_73004”的复制槽,表明正在同步。
订阅端
创建订阅
create subscription xxxx connection 'host=127.0.0.1 port=51111 dbname=XXXX user=postgres password=xxxx' publication xxxx;
查看订阅端订阅:
select * from pg_subscription;
或者
select * from pg_stat_subscription
删除订阅:
drop subscription testsub1 ;
删除订阅备用:
ALTER SUBSCRIPTION XXXX DISABLE;
ALTER SUBSCRIPTION XXXX SET (slot_name = NONE);
SELECT * FROM pg_drop_replication_slot('testsub1 ');