Can session_replication_role used like MySQL's BlackHole Engine?-阿里云开发者社区

Can session_replication_role used like MySQL's BlackHole Engine?

2016-03-30 1514

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介：

测试版本PostgreSQL 9.0.2
    PostgreSQL数据库自带的流复制带来了非常强大的数据库容灾和负载均衡等特性体验。它告诉我们什么是TMD惊喜。预知惊喜请翻阅我以前写的两篇博客
http://blog.163.com/digoal@126/blog/static/16387704020110442050808/
http://blog.163.com/digoal@126/blog/static/1638770402010113053825671/
    不过还是有点不完美，例如不能控制哪些表是需要复制的哪些表是不需要复制的。
    在MySQL中有一种存储引擎叫做黑洞存储引擎，配置为黑洞存储引擎的表对他做DML时啥都不做，但是可以在SLAVE端配置这个表为非黑洞引擎，那么这些DML在SLAVE端将被执行，并有作用。
    在PostgreSQL中有一个叫session_replication_role的参数，根据参数的解释:

  session_replication_role (enum)
Controls firing of replication-related triggers and rules for  the         current session.  

Setting this variable requires          superuser privilege and results in discarding any previously cached          query plans.  

Possible values are origin (the default),         replica and local.         

See ALTER TABLE for         more information.

在ALTER TABLE中也有一段这样的语法:

   DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    DISABLE RULE rewrite_rule_name
    ENABLE RULE rewrite_rule_name
    ENABLE REPLICA RULE rewrite_rule_name
    ENABLE ALWAYS RULE rewrite_rule_name



从而可以根据当前SESSION的角色属性来控制rule和trigger是否生效。那么是否可以利用这个特性来控制哪些表可以被复制，哪些表不能被

复制呢？(如创建DO INSTEAD NOTHING的rule或触发器),欲知结果，请看如下测试:

1. 测试一 : 主库session_replication_role=origin hot_standby session_replication_role=replica
主库 :

digoal=> show session_replication_role;
 session_replication_role 
--------------------------
 origin
(1 row)
digoal=> select count(*) from tbl_user;
 count 
-------
    25
(1 row)

备库 :

digoal=> show session_replication_role;
 session_replication_role 
--------------------------
 replica
(1 row)
digoal=> select count(*) from tbl_user;
  count 
 -------
     25
 (1 row)

主库 :
新建insert do nothing的触发器,

CREATE OR REPLACE FUNCTION digoal.f_user_insert_replica()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$ begin return old; end;
$function$
create trigger t_user_insert_replica before insert on tbl_user for each row execute procedure f_user_insert_replica();
digoal=> \d+ tbl_user

默认情况下是这样的，在origin或local模式role模式生效,

Triggers:
    t_user_insert_replica BEFORE INSERT ON tbl_user FOR EACH ROW EXECUTE PROCEDURE f_user_insert_replica()

现在的目标是让他在replica模式生效,因此

digoal=> alter table tbl_user disable trigger t_user_insert_replica ;
ALTER TABLE
digoal=> alter table tbl_user enable replica trigger t_user_insert_replica ;
ALTER TABLE
digoal=> \d+ tbl_user (已经改变为仅仅replica模式被触发)
Triggers firing on replica only:
    t_user_insert_replica BEFORE INSERT ON tbl_user FOR EACH ROW EXECUTE PROCEDURE f_user_insert_replica()

现在开始测试插入操作:

digoal=> insert into digoal.tbl_user values (1,'zhou','digoal','sky-mobi');
INSERT 0 1
digoal=> select count(*) from digoal.tbl_user;
 count 
-------
    26
(1 row)

很明显，主库因为在origin模式所以没有触发这个触发器.记录已经插入.
下面看看hot_standby的情况:

digoal=> select count(*) from tbl_user;
 count 
-------
    26
(1 row)

很明显hot_standby也没有触发这个触发器。记录与主库一致。
把触发器改为origin和local触发模式，验证触发器有效性,
主库:

digoal=> alter table tbl_user enable trigger t_user_insert_replica ;
ALTER TABLE
\d+ tbl_user
Triggers:
    t_user_insert_replica BEFORE INSERT ON tbl_user FOR EACH ROW EXECUTE PROCEDURE f_user_insert_replica()
digoal=> insert into digoal.tbl_user values (1,'zhou','digoal','sky-mobi');
INSERT 0 0

触发器有效

digoal=> select count(*) from digoal.tbl_user;
 count 
-------
    26
(1 row)

hot_standby数据库:

digoal=> select count(*) from digoal.tbl_user;
 count 
-------
    26
(1 row)

# 换成for each statement结果一样.

2. 测试二 : 看看规则行不行
主库:

首先删除触发器
digoal=> drop trigger t_user_insert_replica on tbl_user;
创建规则
digoal=> create or replace rule r_tbl_user_insert_replica as ON  insert to tbl_user do instead nothing;
digoal=> alter table tbl_user  disable rule r_tbl_user_insert_replica ;
ALTER TABLE
digoal=> alter table tbl_user enable replica rule r_tbl_user_insert_replica ;
ALTER TABLE
Rules firing on replica only:
    r_tbl_user_insert_replica AS
    ON INSERT TO tbl_user DO INSTEAD NOTHING
digoal=> select count(*) from digoal.tbl_user;
 count 
-------
    26
(1 row)
digoal=> insert into digoal.tbl_user values (1,'zhou','digoal','sky-mobi');
INSERT 0 1
digoal=> select count(*) from digoal.tbl_user;
 count 
-------
    27
(1 row)

hot_standby库(很明显又没有起到作用):

digoal=> select count(*) from tbl_user;
 count 
-------
    27
(1 row)

主库:(测试RULE有效性)

digoal=> set session_replication_role=replica;
digoal=> insert into digoal.tbl_user values (1,'zhou','digoal','sky-mobi');
INSERT 0 0
digoal=> select count(*) from digoal.tbl_user;
 count 
-------
    27
(1 row)

3. 测试三 : 修改session_replication_role
Primary和Slave连接使用walsender和walreceiver进程，那么会不会是slave连接到主库的模式决定了触发器或规则的触发条件呢？
修改主库的模式 :

vi postgresql.conf
session_replication_role=replica

重启主库和hot_standby数据库.
重新以上的测试，还是没有达到预期效果.

4. 测试四 : 修改recovery.conf
那么会不会是需要在recovery.conf中配置呢?
修改hot_standby数据库的recovery.conf配置:

primary_conninfo = 'host=172.16.3.33 port=1921 user=repuser1 keepalives_idle=60 options="session_replication_role=replica"'

修改后重启hot_standby数据库,报错,说明还是行不通
postgresql-2011-02-09_105814.csv:2011-02-09 10:58:19.593 CST,,,16375,,4d5202cb.3ff7,1,,2011-02-09 10:58:19 CST,,0,FATAL,XX000,"could not connect to the primary server: invalid connection option ""session_replication_role""

5. 测试五 : 非stream模式
注释掉hot_standby 数据库的配置文件recovery.conf中的primary_conninfo参数
重启hot_standby数据库,
在主库测试:

digoal=# set session session_replication_replica=replica;
digoal=# insert into digoal.tbl_user select * from digoal.tbl_user;
INSERT 0 0
digoal=# set session session_replication_replica=origin;
digoal=# insert into digoal.tbl_user select * from digoal.tbl_user;
INSERT 0 27
...
digoal=# insert into digoal.tbl_user select * from digoal.tbl_user;
INSERT 0 221184
digoal=# checkpoint;
CHECKPOINT
digoal=# select * from pg_switch_xlog();
 pg_switch_xlog 
----------------
 4/F6536390
(1 row)

digoal=# checkpoint;
CHECKPOINT

等待hot_standby数据库应用XLOG后，查看

digoal=> select count(*) from digoal.tbl_user;
 count  
--------
 442368
(1 row)

stream和log shipping模式下得到的结果一样，

6. 测试六 :
开启hot_standby的DEBUG信息

debug_print_parse = on
debug_print_rewritten = on
debug_print_plan = on
debug_pretty_print = on
log_parser_stats = on
log_planner_stats = on
log_executor_stats = on

在复制过程中,到hot_standby的日志中看不到相关的DEBUG信息，说明复制过程没有发生parse,rewritten,plan.executor这些事件.
参考 PostgreSQL manual 章节: Overview of PostgreSQL Internals , rule和trigger在复制时没有被触发与复制的机制和rule,trigger在数据库的执行阶段有直接关系.
参考源代码部分/src/backend/replication

目前想要在PostgreSQL 9.0.2 日志级复制环境中控制哪个表需要被复制，哪个表不需要被复制，使用session_replication_role和触发器或rule模式配合是行不通的。
当然在pgpool这类SQL级的复制中是可以行得通的.

Can session_replication_role used like MySQL's BlackHole Engine?

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像