bigdatapostgresql

PostgreSQL高可用：逻辑复制

, , ,
  1. 《PostgreSQL从入门到不后悔》
  2. 《PostgreSQL高可用：逻辑复制》
  3. 《PostgreSQL高可用 - PG 11集群》

从PostgreSQL 10（以下简称PG）开始，PG支持逻辑复制能力，可实现仅复制部分表或PG服务器上的部分database。逻辑复制的一大优点是支持跨版本间复制，也不需要主从节点的操作系统和硬件架构相同。例如，我们可以实现一台Linux服务器上的PG 11和Windows服务器上的PG 10之间的复制；通过逻辑复制还可以实现不停服的数据库版本更新。

在PG逻辑复制的概念体系中，数据提供方被称为发布者（publisher），数据接收方被称为订阅者（subscriber）。同一个PG即可以作为发布者，同时也可以作为订阅者，这样即可实现级联复制，可以及大的减轻主节点的负担。

注意：但需要注意的是，逻辑复制是单向的。你在从节点上修改的数据行将不会反向同步的主节点，同时相应行也不会再响应主节点的数据变更。

设置逻辑复制

本文使用两台主机来演示PG的逻辑复制：

  1. 10.0.32.37：主节点，数据发布端
  2. 10.0.32.36：从节点，数据订阅端

配置

启用逻辑复制非常简单，设置PG的系统参数wal_level为logical即可。可在SQL控制台通过以下命令修改：

1
alter system set wal_level = logical;

也可逻辑配置文件以使其全局生效（并重启PG）,编辑postgresql.conf并设置如下参数：

1
2
wal_level = logical			# minimal, replica, or logical

创建数据发布者

使用具有superuser权限的用户登录需要设置为数据发布者的数据库：

1
psql -h 10.0.32.35 -U devuser -d watch_log -W

创建数据发布者

1
2
watch_log=# create publication custom_watch_log for table l_basic, l_contact; -- 多张表使用英文逗号分隔列出
CREATE PUBLICATION

注意：这里不要使用for all tables创建数据发布者，因为这将造成发布者在以后不能增、删表。

默认PG只对insert、delete语句进行逻辑复制，若需要对其它DML语句也先进逻辑复制需要设置表的replica identity [option]属性。

1
alter table l_basic replica identity full ;

创建数据订阅者

使用具有superuser权限的用户登录需要设置为数据订阅者的数据库：

1
psql -h 10.0.32.36 -U devuser -d watch_log -W

PG的逻辑复制不会同步DDL语句，需要在订阅者数据库上提前创建后需要进行逻辑复制的表。

创建数据订阅者

1
2
3
watch_log=# create subscription full_watch_log connection 'host=10.0.32.37 port=5432 dbname=watch_log user=devuser password=dbuser.password' publication full_watch_log;
NOTICE:  created replication slot "custom_watch_log" on publisher
CREATE SUBSCRIPTION

演示

1: 初始

数据发布

1
2
3
4
5
6
watch_log=> \conninfo
以用户 "devuser" 的身份, 在主机"10.0.32.37", 端口"5432"连接到数据库 "watch_log"
watch_log=> select * from l_basic ;
 imei | i | p | s | o | u 
------+---+---+---+---+---
(0 行记录)

数据订阅

1
2
3
4
5
6
watch_log=> \conninfo
以用户 "devuser" 的身份, 在主机"10.0.32.36", 端口"5432"连接到数据库 "watch_log"
watch_log=> select * from l_basic ;
 imei | i | p | s | o | u 
------+---+---+---+---+---
(0 行记录)

2: 数据发布者插入记录

数据发布

1
2
3
4
5
6
7
8
watch_log=> insert into l_basic(imei, i, p, s, o, u) values('imei-1', now(), 11, 11, 11.11, 11.11), ('imei-2', now(), 22, 22, 22.22, 22.22);
INSERT 0 2
watch_log=> select * from l_basic ;
  imei  |             i              | p  | s  |   o   |   u
--------+----------------------------+----+----+-------+-------
 imei-1 | 2019-07-10 19:37:39.283234 | 11 | 11 | 11.11 | 11.11
 imei-2 | 2019-07-10 19:37:39.283234 | 22 | 22 | 22.22 | 22.22
(2 行记录)

数据订阅

1
2
3
4
5
6
watch_log=> select * from l_basic ;
  imei  |             i              | p  | s  |   o   |   u
--------+----------------------------+----+----+-------+-------
 imei-1 | 2019-07-10 19:44:47.357619 | 11 | 11 | 11.11 | 11.11
 imei-2 | 2019-07-10 19:44:47.357619 | 22 | 22 | 22.22 | 22.22
(2 行记录)

3: 数据发布者编辑记录

数据发布

1
2
3
4
5
6
7
8
9
watch_log=> update l_basic set p = 44, o = 44.44 where imei = 'imei-1';
UPDATE 1
watch_log=> delete from l_basic where imei = 'imei-2';
DELETE 1
watch_log=> select * from l_basic ;
  imei  |             i              | p  | s  |   o   |   u
--------+----------------------------+----+----+-------+-------
 imei-1 | 2019-07-10 19:44:47.357619 | 44 | 11 | 44.44 | 11.11
(1 行记录)

数据订阅

1
2
3
4
5
watch_log=> select * from l_basic ;
  imei  |             i              | p  | s  |   o   |   u
--------+----------------------------+----+----+-------+-------
 imei-1 | 2019-07-10 19:44:47.357619 | 44 | 11 | 44.44 | 11.11
(1 行记录)

增加新的数据发布表

数据发布端添加新表

使用之前创建publication时的用户登录数据库

1
2
3
psql -h 10.0.32.37 -U postgres -d watch_log -W
watch_log=# alter publication custom_watch_log add table test ;
ALTER PUBLICATION

数据订阅端添加新表并刷新subscription

创建数据表test：

1
2
3
psql -h 10.0.32.36 -U devuser -d watch_log -W
watch_log=> create table test(id serial primary key, name varchar(255));
CREATE TABLE

使用之前创建publication时的用户登录数据库

1
2
3
psql -h 10.0.32.36 -U postgres -d watch_log -W
watch_log2=# alter subscription custom_watch_log refresh publication ;
ALTER SUBSCRIPTION

小结

最后放张图：
PostgreSQL 11 logical replication

分享到