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

--- title: PostgreSQL pglogical 插件与跨数据库逻辑复制实战 keywords: pglogical, create_subscription, wal_level, replication_set, logical replication, PostgreSQL description: 使用 pglogical 在不同数据库间建立逻辑复制，配置前置参数与复制集，保证数据无停机同步。 tags: - PostgreSQL - create_subscription - logical replication - pglogical - replication_set - wal_level - 数据库 - 逻辑复制 categories: - 文章资讯 - 技术教程 --- 前置配置（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'); ```