注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL10:逻辑复制(Logical Replication)之一  

2017-05-28 14:20:04|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
PostgreSQL10另一重量级新特性是支持内置的逻辑复制( Logical Replication),这一新特性主要提交者为来自 2ndquadrant 的开发者,感谢他们的付出!

关于 Logical Replication
介绍Logical Replication之前,先介绍下  Streaming Replication,中文常称之为流复制,流复制最早在 9.0 版本出现 ,生产环境使用非常普遍,常用在高可用、读写分离场景,流复制是基于 WAL 日志的物理复制,适用于实例级别的复制,而Logical Replication 属于逻辑复制,可基于表级别复制,是一种粒度可细的复制,主要用在以下场景:

1、满足业务上需求,实现某些指定表数据同步;
2、报表系统,采集报表数据;
3、PostgreSQL 跨版本数据同步
4、PostgreSQL 大版本升级

在10版本之前,虽然没有内置的逻辑复制,也可以通过其它方式实现,例如触发器、自定义脚本实现表级别同步,另外也可以通过外部工具 Londiste3 实现,详见  Londiste3:搭建简单的基于表的复制 

接下来做个演示,本机装了两个 pg10 实例,端口分别为 1921,1923,计划将端口1921上的francs 库作为源库,端口1923上的des库作为目标库。


一、演示 Logical Replication
-- 两节点上设置postgresql.conf

wal_level = logical                     # minimal, replica, or logical


--create user repuser(1921 实例上操作)

CREATE USER repuser 
  REPLICATION
  LOGIN
  CONNECTION LIMIT 10
  ENCRYPTED PASSWORD 'repuser';

  备注:用于逻辑复制的用户必须是  replication 角色 superuser 角色

--赋权
需要给 repuser 用户对源库、源表、源schmea 赋权,便于演示,这里暂不赋权限。

1921实例francs库操作

--创建测试表
francs=> create table test_lr1(id int4 primary key ,name text);
CREATE TABLE

francs=> insert into test_lr1 values (1,'a');
INSERT 0 1

--创建 PUBLICATION pub1
francs=> create PUBLICATION pub1 FOR TABLE test_lr1;
CREATE PUBLICATION
 
--查看 PUBLICATION
francs=# select * from pg_publication;
-[ RECORD 1 ]+------
pubname      | pub1
pubowner     | 16384
puballtables | f
pubinsert    | t
pubupdate    | t
pubdelete    | t

1923实例des库操作

des=> create schema francs;
CREATE SCHEMA

des=> create table francs.test_lr1(id int4 ,name text);
CREATE TABLE

des=> select * from francs.test_lr1 ;
 id | name 
----+------
(0 rows)

--创建subscription
des=# create subscription sub1 connection 'host=127.0.0.1 port=1921 dbname=francs user=repuser' publication pub1;
NOTICE:  synchronized table states
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

备注:必须是具有superuser权限的用户才能创建subscription。


--查看subscription

des=# select * from pg_subscription;
[ RECORD 1 ]---+----------------------------------------------------
subdbid         | 16387
subname         | sub1
subowner        | 10
subenabled      | t
subconninfo     | host=127.0.0.1 port=1921 dbname=francs user=repuser
subslotname     | sub1
subsynccommit   | off
subpublications | {pub1}


--数据验证

des=# select * from francs.test_lr1 ;
 id | name 
----+------
(0 rows)

备注:这时看数据还没有同步过来。

--查看 francs 库日志

2017-05-28 11:11:57.609 CST,"repuser","francs",13332,"127.0.0.1:56500",592a3ffd.3414,2,"COPY",2017-05-28 11:11:57 CST,5/1338,999,ERROR,42501,"permission denied for schema francs",,,,,,"COPY francs.test_lr1 TO STDOUT",,,"sub1_16416_sync_16409"

备注:原来没有给 repuser 相应权限,赋权即可。

1921实例francs库操作

--给 repuser 赋权
francs=> grant connect on database francs to repuser;
GRANT
francs=> grant usage on schema francs to repuser;
GRANT
francs=> grant select on test_lr1 to repuser;
GRANT

1923实例des库操作

des=# select * from francs.test_lr1 ;
 id | name 
----+------
  1 | a
(1 row)

备注:数据有了。

1921实例francs库再插入一条数据

francs=> insert into test_lr1 values (2,'b');
INSERT 0 1


1923实例des库操作

des=> select * from francs.test_lr1 ;
 id | name 
----+------
  1 | a
  2 | b
(2 rows)

备注:新插入的数据被同步过来了,这时再演示下删除操作

1921实例francs库删除一条数据

francs=> delete from test_lr1 where id=1;
DELETE 1


1923实例des库操作

des=# select * from francs.test_lr1 ;
 id | name 
----+------
  2 | b
(1 rows)

备注:id=1 的数据在备库也被删掉了,这个时候如果要加入一个逻辑复制表如何操作?

--1921实例francs库新建一张大表,插入1000万数据

francs=> create table test_big2(id int4 primary key, create_time timestamp without time zone default clock_timestamp(), name character varying(32));
CREATE TABLE

francs=> insert into test_big2(id,name) select n,n*random()*10000 from generate_series(1,10000000) n ; 
INSERT 0 10000000

francs=> grant select on test_big2 to repuser;
GRANT

francs=# ALTER PUBLICATION pub1 add TABLE francs.test_big2;
ALTER PUBLICATION


1923实例des库操作

des=> create table francs.test_big2(id int4 primary key, create_time timestamp without time zone default clock_timestamp(), name character varying(32));
CREATE TABLE

des=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION ;
NOTICE:  added subscription for table francs.test_big2
ALTER SUBSCRIPTIO

des=# select count(*) from francs.test_big2;
  count   
----------
 10000000
(1 row)

备注:PUBLICATION 节点增加表后,SUBSCRIPTION 节点需要执行 ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION 命令进行刷新;1000万数据同步大概用了不到30秒,速度很快,在同步过程中看到了 COPY 进程,推测在数据初始化时用的是 copy 

-- SUBSCRIPTION 进程可以 DISABLE 或 ENABLE

des=# ALTER SUBSCRIPTION sub1 DISABLE ;
ALTER SUBSCRIPTION

des=# ALTER SUBSCRIPTION sub1 ENABLE ;
ALTER SUBSCRIPTION


二、 Logical Replication 原理
Logical Replication 原理实际上用到了 Replication slots 的概念,关于这个可参考之前写的博客,PostgreSQL9.4: 初识逻辑解析 ( logical decoding )  ,PostgreSQL 9.4: Replication slots 

三、总结
publication - 发布者
  1. 逻辑复制的前提是将数据库 wal_level 参数设置成 logical;
  2. 源库上逻辑复制的用户必须具有 replicatoin 或 superuser 角色;
  3. 逻辑复制目前仅支持数据库表逻辑复制,其它对象例如函数、视图不支持;
  4. 逻辑复制支持DML(UPDATE、INSERT、DELETE)操作,TRUNCATE 和 DDL 操作不支持;
  5. 需要发布逻辑复制的表,须配置表的 REPLICA IDENTITY 特性;
  6. 一个数据库中可以有多个publication,通过 pg_publication  查看;
  7. 允许一次发布所有表,语法: CREATE PUBLICATION alltables FOR ALL TABLES;

subscription - 订阅者
  1. 订阅节点需要指定发布者的连接信息;
  2. 一个数据库中可以有多个订阅者;
  3. 可以使用enable/disable启用/暂停该订阅;
  4. 发布节点和订阅节点表的模式名、表名必须一致,订阅节点允许表有额外字段;
  5. 发布节点增加表名,订阅节点需要执行: ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION  

四、参考
  评论这张
 
阅读(290)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017