PostgreSQL pglogical 插件与跨数据库逻辑复制实战

前置配置（postgresql.conf）：wal_level = logical

max_worker_processes = 10

max_replication_slots = 10

max_wal_senders = 10

shared_preload_libraries = 'pglogical'

发布端（Provider）配置与初始化：CREATE EXTENSION pglogical;

SELECT pglogical.create_node(node_name := 'pub_node', dsn := 'host=pub-db port=5432 dbname=app user=replicator password=secret');

SELECT pglogical.create_replication_set('default', include_insert := true, include_update := true, include_delete := true, include_truncate := true);

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public'], include_sequences := true);

订阅端（Subscriber）创建订阅并同步：CREATE EXTENSION pglogical;

SELECT pglogical.create_node(node_name := 'sub_node', dsn := 'host=sub-db port=5432 dbname=app user=replicator password=secret');

SELECT pglogical.create_subscription(

subscription_name := 'sub_default',

provider_dsn := 'host=pub-db port=5432 dbname=app user=replicator password=secret',

replication_sets := ARRAY['default'],

synchronize_data := true,

forward_origins := '{}'

);

状态与同步完成检测：SELECT * FROM pglogical.show_subscription_status();

SELECT pglogical.wait_for_subscription_sync_complete('sub_default');

DDL 复制（受控执行）：SELECT pglogical.replicate_ddl_command('ALTER TABLE public.users ADD COLUMN last_seen timestamptz');