PostgreSQL主从复制-逻辑复制-编程学习网

1、物理复制及逻辑复制对比

前文做了PostgreSQL物理复制的部署,其有如下主要优点

物理层面完全一致，是主要的复制方式，其类似于Oracle的DG。
延迟低，事务执行过程中产生REDO record，实时的在备库apply，事务结束时，备库立马能见到数据。
物理复制的一致性、可靠性高，不必担心数据逻辑层面不一致。

但是其又在实际使用的场景中存在一些无法满足的需求，例如：

无法满足指定库或部分表的复制需求。
将多个数据库实例的数据汇聚到同一个目标库或将一个库的数据分发到多个不同的库。
不同的版本之间的复制。
不同库名之间的不同步。

对于以上场景,物理复制是无法满足的，因此逻辑复制应运而生了。

逻辑复制的复制架构图如下：

图片来源于《PostgreSQL实战》

逻辑复制是基于逻辑解析，其核心原理是逻辑主库将Publication中表的WAL日志解析成一定格式并发送给逻辑备库，逻辑备库Subscription接收到解析后的WAL日志后进行重做，从而实现表数据同步。

2、逻辑复制的部署

PS：以下的逻辑从库可以在新的机器上部署，如在原先的从库上修改，需停止原实例，并将recovery.conf文件删除或重命名（如修改为recovery.conf.done）

（1）修改主库的postgresql.conf

可以基于原先的物理复制的配置文件进行修改,配置逻辑复制主要需调整如下参数

wal_level = logical
max_wal_senders = 10
max_replication_slots = 8

参数简要说明如下

wal_level：设置成logical才支持逻辑复制.
max_wal_senders：由于每个订阅节点和流复制备库在主库上都会占用主库上一个WAL发送进程，因此此参数设置值需大于max_replication_slots参数值加上物理备库数量。
max_replication_slots：设置值需大于订阅节点的数量。

（2）修改逻辑从库的postgresql.conf

逻辑从库的postgresql.conf也可以在物理复制的基础上修改，与主库不同的是主要修改如下参数

wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8

参数简要说明：

wal_level：设置成logical才支持逻辑复制,逻辑从库可以视情况设置。
max_replication_slots：设置数据库复制槽数量，应大于订阅节点的数量。
max_logical_replication_workers：设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量，此参数默认值为4。

（3）创建逻辑复制账号

postgres=# CREATE USER logical_repl REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'logical_repl'; 
CREATE ROLE

逻辑复制用户需要REPLICATION权限即可，可以不需要SUPERUSER权限，之后需要在发布节点上将需要同步的表赋权给logical_repl用户，使logical_repl账号具有对这些表的读权限。

（4）在逻辑主库上创建库及表

 
[postgres@PG32 data]$ createdb sourcedb 
[postgres@PG32 data]$ psql -d sourcedb
 psql (11.4) 
Type "help" for help. 
sourcedb=# create table logical_tb1(id int primary key,name varchar(20));

（5）逻辑从库上创建库及表


[postgres@PG33 data]$ createdb desdb 
[postgres@PG33 data]$ psql -d desdb 
psql (11.4) 
Type "help" for help. 
desdb=# create table logical_tb1(id int primary key,name varchar(20)); 
CREATE TABLE 
desdb=#

注：逻辑复制的表结构需要手动在从库创建

（6）在逻辑主库上创建发布


 sourcedb=# CREATE PUBLICATION pub1 FOR TABLE logical_tb1; 
CREATE PUBLICATION

如果需发布多张表则表名间用逗号（,）分割，如果需发布所有库，则将FOR TABLE 调整为FOR ALL TABLES。

此时可以查看到如下信息。

sourcedb=# SELECT * FROM pg_publication;
 pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate 
---------+----------+--------------+-----------+-----------+-----------+-------------
 pub1    |       10 | f            | t         | t         | t         | t
(1 row)

字段说明如下：

pubname：指发布的名称。
pubowner：指发布的属主，可以和pg_user视图的usesysid字段关联查询得到属主具体信息。
puballtables：是否发布数据库中的所有表，t表示发布数据库中所有已存在的表和以后新建的表。
pubinsert：t表示仅发布表上的INSERT操作。
pubupdate：t表示仅发布表上的UPDATE操作。
pubdelete：t表示仅发布表上的DELETE操作。

（7）在逻辑从库上创建订阅

desdb=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.56.32 port=5432 user=logical_repl dbname=sourcedb password=logical_repl' PUBLICATION pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

创建成功后，可以在逻辑主库上查询到如下信息：

sourcedb=# SELECT slot_name,plugin,slot_type,database,active,restart_lsn    FROM pg_replication_slots where slot_name='sub1';
 slot_name |  plugin  | slot_type | database | active | restart_lsn
-----------+----------+-----------+----------+--------+-------------
 sub1      | pgoutput | logical   | sourcedb | t      | 0/6022D30
(1 row)

在逻辑从库上可以查询到如下信息：

desdb=# SELECT * FROM pg_subscription;
 subdbid | subname | subowner | subenabled |                                      subconninfo                                      | subslotname | subsynccommit | subpublications
---------+---------+----------+------------+---------------------------------------------------------------------------------------+-------------+---------------+-----------------
   24995 | sub1    |       10 | t          | host=192.168.56.32 port=5432 user=logical_repl dbname=sourcedb password=logical_repl | sub1        | off           | {pub1}
(1 row)

（8）给复制账号授权

因为步骤2.3中只创建了复制账号并未对需要复制的表进行授权，因此，此时逻辑从库的日志中有如下错误

2019-10-10 15:57:21.847 CST [27443] ERROR: could not start initial contents copy for table "public.logical_tb1": ERROR: permission denied for table logical_tb1
2019-10-10 15:57:21.848 CST [24722] LOG: background worker "logical replication worker" (PID 27443) exited with exit code 1

因此，现在需要对复制用户授权。

sourcedb=# GRANT USAGE ON SCHEMA public TO logical_repl;
GRANT 
sourcedb=# GRANT SELECT ON logical_tb1 TO logical_repl;
GRANT

授权后,显示正常。

2019-10-10 16:00:25.959 CST [28204] LOG: logical replication table synchronization worker for subscription "sub1", table "logical_tb1" has started
2019-10-10 16:00:25.967 CST [28204] LOG: logical replication table synchronization worker for subscription "sub1", table "logical_tb1" has finished

（9）测试数据同步

在逻辑主库插入数据。

 
sourcedb=# insert into logical_tb1(id,name) values(1,'a'),(2,'bca');
INSERT 0 2

在逻辑从库查看结果。

 
desdb=# select * from logical_tb1;
 id | name
----+------
  1 | a
  2 | bca
(2 rows)

由此可见数据已同步完成。

（10）添加复制所需的表

在逻辑主库和逻辑从库均添加一张新表，并添加到发布列表中。

 
sourcedb=# create table logical_tb2(id int primary key ,addr varchar(100));
CREATE TABLE 
sourcedb=#

desdb=# create table logical_tb2(id int primary key ,addr varchar(100));
CREATE TABLE 
 
sourcedb=# GRANT SELECT ON logical_tb2 TO logical_repl;
GRANT
 
sourcedb=# ALTER PUBLICATION pub1 ADD TABLE logical_tb2;
ALTER PUBLICATION

sourcedb=# SELECT * FROM pg_publication_tables;
 pubname | schemaname |  tablename
---------+------------+-------------
 pub1    | public     | logical_tb1
 pub1    | public     | logical_tb2
(2 rows)

此时已加入一张表进入发布列表中。

此时在主库写入数据，查看从库情况如下：

 
sourcedb=# insert into logical_tb2(id,addr) values(1,'beijing');
INSERT 0 1 

desdb=# select  * from logical_tb2;
 id | addr
----+------
(0 rows)

因为还需要在从库刷新一下订阅。

 
desdb=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
 
ALTER SUBSCRIPTION
desdb=# select  * from logical_tb2;
 id |  addr
----+---------
  1 | beijing
(1 row)

至此，PostgreSQL的逻辑复制也部署完毕。