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

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

关于 Logical Replication

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

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

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

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

演示 Logical Replication

两节点上设置postgresql.conf

1
wal_level = logical # minimal, replica, or logical

create user repuser(1921 实例上操作)

1
2
3
4
5
CREATE USER repuser
REPLICATION
LOGIN
CONNECTION LIMIT 10
ENCRYPTED PASSWORD 'repuser';

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

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

1921实例francs库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
创建测试表
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库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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

1
2
3
4
5
6
7
8
9
10
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}

数据验证

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

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

查看 francs 库日志

1
2
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库操作

1
2
3
4
5
6
7
给 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库操作

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

备注:数据有了。

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

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

1923实例des库操作

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

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

1921实例francs库删除一条数据

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

1923实例des库操作

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

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

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

1
2
3
4
5
6
7
8
9
10
11
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库操作

1
2
3
4
5
6
7
8
9
10
11
12
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

1
2
3
4
5
des=# ALTER SUBSCRIPTION sub1 DISABLE ;
ALTER SUBSCRIPTION

des=# ALTER SUBSCRIPTION sub1 ENABLE ;
ALTER SUBSCRIPTION

Logical Replication 原理

Logical Replication 原理实际上用到了 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

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%