前言:
我们前面提到的流复制(即物理复制)是基于实例级别的同步,即备库和主库是一模一样的;而逻辑复制是基于表级别的,可以同步某些指定的表,实现更细粒度的同步功能。使用逻辑复制的功能,我们在实现特殊需求时会方便的多,比如PostgreSQL的跨大版本升级、从一个实例拆分某一个库到另一个实例等等。
逻辑复制架构图:
原理:
逻辑复制的原理其实就是master节点将其Publication(你可以暂且理解为一种数据对象)中的表的WAL日志进行解析后,形成一种特殊格式的日志流,传送给slave节点,之后备库上的Subscription对这些日志流进行解析重放,从而达到同步表数据的功能。这之间的WAL传输都是通过各自服务器上的WAL发布进程和订阅进程所完成的。
注意事项:
待同步的表必须要有复制标识(主键、唯一索引),也可以设置为full表示整行数据作为键值但效率非常低,若没有复制标识,则表上的update/delete操作会报错。
DDL操作不会被复制,即master节点上的DDL操作,需手工在logical slave节点上执行一下;
环境信息:
角色 | 主机IP | 端口 | 库名 | 复制用户名 | 版本 |
---|---|---|---|---|---|
发布节点master | 10.25.15.77 | 1921 | mydb | logical_user | PostgreSQL12 |
发布节点slave | 10.25.15.79 | 1921 | desdb | logical_user | PostgreSQL12 |
部署步骤:
1.参数设置
修改master节点的配置文件postgresql.conf
wal_level = logical
max_replication_slots = 8
max_wal_senders = 10
修改logical slave节点的配置文件postgresql.conf
max_replication_slots = 4
max_logical_replication_workers = 4
max_worker_processes = 8
2.创建逻辑用户
在master
节点创建逻辑复制用户,此用户需要有REPLICATION
权限,以及待同步表的读权限
create user logical_user replication login connection limit 8 encrypted password 'admin123';
创建测试表:
mydb=# create table table1(id int4, name text, primary key(id)); mydb=# insert into table1 values(1,'a');
赋权:
mydb=# grant select on table1 to logical_user;
注:如果嫌一个一个表的赋权麻烦,可以直接让logical_user拥有库下某个schema中所有表的select权限:
mydb=# grant select on all tables in schema public to logical_user;
(默认schema名称是public,关于schema的知识请自行搜索)
3.创建发布对象
在master节点上创建Publication
mydb=# create publication pub1 for table table1;
CREATE PUBLICATION
注:
- pub1是发布对象的名字;
- FOR TABLE,指加入发布对象里的表名列表,多表之间用逗号隔开,也可以使用FOR ALL TABLES,表示将当前库中所有表添加进来
查询刚刚创建的发布:
mydb=# select * from pg_publication;
oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
-------+---------+----------+--------------+-----------+-----------+-----------+-------------
40989 | pub1 | 10 | f | t | t | t | t
说明:
- pubowner,指发布对象的owner
- puballtables,表示是否发布当前库的所有表,f表示false,t表示true
- pubinsert,指是否同步insert操作
- pubupdate,指是否同步update操作
- pubdelete,指是否同步delete操作
- pubtruncate,指是否同步truncate操作
4.创建订阅对象
因为DDL操作无法同步过来,所以需要手动在logical slave上先创建表,但只许创建表结构
postgres=# \c desdb; #切换在desdb库下;
desdb=# create table table1(id int4, name text, primary key(id));
处于安全角度的考虑,建议创建一个隐藏的密码文件~/.pgpass
, 内容如下:
10.25.15.77:1921:mydb:logical_user:admin123
修改一下权限:
[postgres@ndcnvx509 ~]# chmod 600 .pgpass
在logical slave节点上创建订阅对象,
desdb=# create subscription sub_1 connection 'host=10.25.15.77 port=1921 dbname=mydb user=logical_user' publication pub1;
NOTICE: created replication slot "sub_1" on publisher
CREATE SUBSCRIPTION
注:
上述红色字体created replication slot "sub_1" on publisher
显示自动创建了一个复制槽 sub_1
说明:
- sub_1,表示订阅名称;
- connection,后面紧跟的是连接信息,主要有host,port,dbname(待同步的库),用户名user,而密码则会自动去上述创建的隐藏文件~/.pgpass中读取。
- pub1,表示这次连接的发布对象的名称,即接收这个发布对象同步过来的表;
查看订阅对象
desdb=# select * from pg_subscription;
oid | subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications
-------+---------+---------+----------+------------+----------------------------------------------------------+-------------+---------------+-----------------
40993 | 40979 | sub_1 | 10 | t | host=10.25.15.77 port=1921 dbname=mydb user=logical_user | sub_1 | off | {pub1}
此时我们在master
节点上也可以查看到订阅的相关信息
(在master节点上执行)
mydb=# select slot_name,plugin,slot_type,database,active,restart_lsn from pg_replication_slots;
slot_name | plugin | slot_type | database | active | restart_lsn
-----------+----------+-----------+----------+--------+-------------
sub_1 | pgoutput | logical | mydb | t | 0/15002340
5.验证同步结果
此时我们查看logical slave的table1表上是否数据已经同步
desdb=# select * from table1;
id | name
----+------
1 | a
此时master,slave节点上分别会有WAL发布进程和WAL订阅进程如下:
postgres: walsender logical_user 10.25.15.79(39236) idle (master节点)
postgres: logical replication worker for subscription 40993 (slave节点)
至此,PostgreSQL的逻辑复制架构就算是部署完成了。。。
补充部分
6.如何新增一个表的同步
如果我们想新增加一张表的同步,怎么办?请使用如下命令:
(在master
节点上)
mydb=# create table table2(id int4, age int4); #注意此处我们并没有给表table2创建主键,为了后续实验
CREATE TABLE
mydb=# insert into table2 values(1,1);
INSERT 0 1
mydb=# grant select on table2 to logical_user;
GRANT
mydb=# alter publication pub1 add table table2;
ALTER PUBLICATION
mydb=# select * from pg_publication_tables;
pubname | schemaname | tablename
---------+------------+-----------
pub1 | public | table1
pub1 | public | table2
(logical slave
节点上执行如下命令)
desdb=# create table table2(id int4, age int4);
CREATE TABLE
desdb=# alter subscription sub_1 refresh publication; #刷新订阅
ALTER SUBSCRIPTION
desdb=# select * from table2; #此时查看发现数据已经同步过来
id | age
----+-----
1 | 1
7.在没有主键的同步表上update会报错
刚才我们新建了一个没有主键的同步表table2,insert是没有问题,但是update的时候就会报错如下:
(master节点上)
mydb=# update table2 set age=22 where id=1;
ERROR: cannot update table "table2" because it does not have a replica identity and publishes updates
HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
原因:
因为表上没有复制标识(没有主键 或 唯一索引),所以不允许更新
添加主键(master节点上执行)
mydb=# alter table table2 add primary key(id);
ALTER TABLE
mydb=# update table2 set age=22 where id=1;
UPDATE 1
注:
logical slave节点上也需要添加相应主键,否则无法同步update操作
desdb=# alter table table2 add primary key(id);
ALTER TABLE
desdb=# select * from table2 where id=1;
id | age
----+-----
1 | 22
8.逻辑复制启动和停止
其实就是通过开启,停止logical slave节点上的订阅来实现的
关闭订阅
desdb=# alter subscription sub_1 disable;
ALTER SUBSCRIPTION
desdb=# select subname,subenabled,subslotname,subpublications from pg_subscription;
subname | subenabled | subslotname | subpublications
---------+------------+-------------+-----------------
sub_1 | f | sub_1 | {pub1}
开启订阅:
desdb=# alter subscription sub_1 enable;
ALTER SUBSCRIPTION
desdb=# select subname,subenabled,subslotname,subpublications from pg_subscription;
subname | subenabled | subslotname | subpublications
---------+------------+-------------+-----------------
sub_1 | t | sub_1 | {pub1}