Can session_replication_role used like MySQL's BlackHole Engine?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:
  测试版本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级的复制中是可以行得通的.
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
SQL 关系型数据库 MySQL
总结 vue3 的一些知识点:MySQL LIKE 子句
总结 vue3 的一些知识点:MySQL LIKE 子句
|
关系型数据库 MySQL 数据库
在 MySQL 中使用 LIKE
【8月更文挑战第12天】
1475 1
|
SQL 关系型数据库 MySQL
MySQL 8.0报错--1118-Row size too large. The maximum row size for the used table type, not counting BLOBs,is 8126,
MySQL 8.0报错--1118-Row size too large. The maximum row size for the used table type, not counting BLOBs,is 8126,
MySQL 8.0报错--1118-Row size too large. The maximum row size for the used table type, not counting BLOBs,is 8126,
|
11月前
|
搜索推荐 关系型数据库 MySQL
mysql like查询优化
通过合理的索引设计、使用全文索引、优化查询结构以及考虑分片和分区表,可以显著提高MySQL中 `LIKE`查询的性能。针对不同的应用场景选择合适的优化策略,能够有效地提升数据库查询效率,减少查询时间。希望这些方法和技巧能帮助您优化MySQL数据库中的模糊查询。
1144 4
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
搜索推荐 关系型数据库 MySQL
MySQL 模糊查询新纪元:超越 LIKE+% 的高效探索
在数据库的日常操作中,模糊查询是一项不可或缺的功能,它允许我们根据不完全匹配的关键字来检索数据。传统上,MySQL 使用 LIKE 关键字配合 % 通配符来实现这一功能,虽然灵活但性能上往往不尽如人意,尤其是在处理大型数据集时。今天,我们将一起探索几种超越 LIKE+% 的模糊查询技术,以提升查询效率与用户体验。
664 2
|
12月前
|
SQL 关系型数据库 MySQL
MySQL异常一之: You can‘t specify target table for update in FROM clause解决办法
这篇文章介绍了如何解决MySQL中“不能在FROM子句中指定更新的目标表”(You can't specify target table for update in FROM clause)的错误,提供了错误描述、需求说明、错误做法和正确的SQL写法。
2215 0
|
关系型数据库 MySQL 数据库
docker启动mysql多实例连接报错Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’
docker启动mysql多实例连接报错Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’
1100 0
|
关系型数据库 MySQL Linux
Docker安装mysql详细教程, mysqld: Can‘t read dir of ‘/etc/mysql/conf.d/‘(报错已解决)
Docker安装mysql详细教程, mysqld: Can't read dir of '/etc/mysql/conf.d/' (Errcode: 2 - No such file or directory) 已解决
|
网络协议 关系型数据库 MySQL
启动mysql时的异常为:[ERROR] Can‘t start server: Bind on TCP/IP port. Got error: 98: Address already in used
启动mysql时的异常为:[ERROR] Can‘t start server: Bind on TCP/IP port. Got error: 98: Address already in used

热门文章

最新文章

推荐镜像

更多