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');