深入探索 PostgreSQL 13.8 的订阅与发布功能

-- 创建发布
CREATE PUBLICATION pub_with_insert FOR ALL TABLES;

-- 创建订阅，连接到远程数据库
CREATE SUBSCRIPTION sub_with_conninfo
    CONNECTION 'host=remote-host user=replica password=secret port=5432 dbname=source'
    PUBLICATION pub_with_insert;

-- 使用slot进行同步，避免同一时间点的数据冲突
CREATE SUBSCRIPTION sub_with_slot
    CONNECTION 'host=remote-host user=replica password=secret port=5432 dbname=source'
    PUBLICATION pub_with_insert
    WITH (create_slot = true, slot_name = 'sub_with_slot_replication_slot');

-- 查看现有的订阅状态
SELECT * FROM pg_subscription;

-- 查看订阅的复制槽信息
SELECT * FROM pg_replication_slots WHERE slot_name = 'sub_with_slot_replication_slot';

-- 更新订阅，使用新的连接参数
ALTER SUBSCRIPTION sub_with_conninfo
    SET CONNECTION 'host=new-remote-host user=replica password=new-secret port=5432 dbname=source';

-- 删除订阅
DROP SUBSCRIPTION sub_with_conninfo;