pglogical

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

shared_preload_libraries = 'pglogical';

CREATE EXTENSION pglogical;

wal_level = 'logical'
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pglogical'

CREATE EXTENSION pglogical;

-- 在提供者上创建节点
SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=providerhost port=5432 dbname=mydb'
);

-- 将 public 模式中所有表添加到默认复制集
SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

-- 添加 public 模式中所有序列
SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public']);

-- 在订阅者上创建节点
SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=subscriberhost port=5432 dbname=mydb'
);

-- 创建到提供者的订阅
SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=providerhost port=5432 dbname=mydb'
);

-- 创建自定义复制集
SELECT pglogical.create_replication_set('my_set');

-- 添加特定表
SELECT pglogical.replication_set_add_table('my_set', 'my_table', true);

-- 移除表
SELECT pglogical.replication_set_remove_table('my_set', 'my_table');

-- 行过滤：仅复制匹配条件的行
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'my_table',
    row_filter := 'id > 1000'
);

-- 列过滤：仅复制特定列
SELECT pglogical.replication_set_add_table(
    set_name := 'default',
    relation := 'my_table',
    columns := '{id, name, updated_at}'
);

-- 检查订阅状态
SELECT * FROM pglogical.show_subscription_status();

-- 删除订阅
SELECT pglogical.drop_subscription('subscription1');