Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

Can session_replication_role used like MySQL’s BlackHole Engine?

3 minute read

背景

测试版本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 10.0将会支持逻辑复制。

正文

在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级的复制中是可以行得通的.

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...