pglogical使用

开源喵 2021-06-30

1756

pglogical是一个专门为PostgreSQL扩展实现的逻辑复制插件。，它不需要触发器或外部程序。

1.1 必要条件

wal_level = 'logical'
max_worker_processes = 10   # one per database needed on provider node
                            # one per node needed on subscriber node
max_replication_slots = 10  # one per node needed on provider node
max_wal_senders = 10        # one per node needed on provider node
shared_preload_libraries = 'pglogical'

track_commit_timestamp = on # needed for last/first update wins conflict resolution
                            # property available in PostgreSQL 9.5+

1.2 publish发布端

SELECT pglogical.create_node(
    node_name := 'pub',
    dsn := 'host=sdw2 port=5432 dbname=postgres'
);                            

postgres=# select pglogical.replication_set_add_table('default','t1');
 replication_set_add_table 
---------------------------
 t

何为default?

postgres=# select * from pglogical.replication_set ;
   set_id   | set_nodeid |      set_name       | replicate_insert | replicate_update | replicate_delete | replicate_truncate 
------------+------------+---------------------+------------------+------------------+------------------+--------------------
   93424862 | 1089405010 | default             | t                | t                | t                | t
 3604357919 | 1089405010 | default_insert_only | t                | f                | f                | t
 2605070343 | 1089405010 | ddl_sql             | t                | f                | f                | f
(3 rows)

pglogic在安装完成时，默认为创建三个默认的replication set.当然如果有需要，自己也可以自定义relication set,相关的函数为：

•pglogical.create_replication_set(set_name name, replicate_insert bool, replicate_update bool, replicate_delete bool, replicate_truncate bool) This function creates a new replication

•set_name – name of the set, must be unique•replicate_insert – specifies if INSERT is replicated, default true•replicate_update – specifies if UPDATE is replicated, default true•replicate_delete – specifies if DELETE is replicated, default true•replicate_truncate – specifies if TRUNCATE is replicated, default true

1.3 sub订阅端

SELECT pglogical.create_node(
    node_name := 'sub1',
    dsn := 'host=sdw2 port=15432 dbname=postgres user=postgres'   #这里为订阅段的相关信息
);

SELECT pglogical.create_subscription(
    subscription_name := 'sub1',
    provider_dsn := 'host=sdw2 port=5432 dbname=postgres user=pg13'   #这里为发布端的相关信息
);

1.4 DDL的支持

首先要声明的时pglogical不支持ddl的复制，但是可以通过触发器的方式执行

1.4.1 定义触发器

CREATE OR REPLACE FUNCTION pglogical_assign_repset()
RETURNS event_trigger AS $$
DECLARE obj record;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        IF obj.object_type = 'table' THEN
            IF obj.schema_name = 'config' THEN
                PERFORM pglogical.replication_set_add_table('configuration', obj.objid);
            ELSIF NOT obj.in_extension THEN
                PERFORM pglogical.replication_set_add_table('default', obj.objid);
            END IF;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER pglogical_assign_repset_trg
    ON ddl_command_end
    WHEN TAG IN ('CREATE TABLE', 'CREATE TABLE AS')
    EXECUTE PROCEDURE pglogical_assign_repset();

### 1.4.2 使用pglogical.replicate_ddl_command在发布端创建表
select pglogical.replicate_ddl_command('create table public.ysw2(a int primary key);');

然后订阅端也会相应的创建表。

但是有一点需要注意：

SELECT pglogical.create_subscription(
    subscription_name := 'sub1',
    provider_dsn := 'host=sdw2 port=5432 dbname=postgres user=pg13'   #这里为发布端的相关信息
);

如果订阅端没有用户pg13，则复制会报错。导致整个逻辑复制挂掉，报错信息如下：

2021-06-30 14:16:02.716 CST,,"postgres",121849,,60dc0c22.1dbf9,2,"",2021-06-30 14:16:02 CST,4/5558,8903,ERROR,22023,"role ""pg13"" does not exist",,,,,"during execution of queued SQL statement: create table public.ysw2(a int primary key);

postgresql

文章转载自开源喵，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。