postgresql从库搭建--逻辑复制
1 物理复制及逻辑复制对比
前文做了PostgreSQL物理复制的部署,其有如下主要优点
- 物理层面完全一致，是主要的复制方式，其类似于Oracle的DG
- 延迟低，事务执行过程中产生REDO record，实时的在备库apply，事务结束时，备库立马能见到数据
- 物理复制的一致性、可靠性高，不必担心数据逻辑层面不一致
但是其又在实际使用的场景中存在一些无法满足的需求，例如：
- 无法满足指定库或部分表的复制需求
- 将多个数据库实例的数据汇聚到同一个目标库或将一个库的数据分发到多个不同的库
- 不同的版本之间的复制
- 不同库名之间的表同步
对于以上场景,物理复制时无法满足的，因此逻辑复制应运而生了。
逻辑复制的复制架构图如下：
图片来源于《PostgreSQL实战》
逻辑复制是基于逻辑解析，其核心原理是逻辑主库将Publication中表的WAL日志解析成一定格式并发送给逻辑备库，逻辑备库Subscription接收到解析后的WAL日志后进行重做，从而实现表数据同步。
2. 逻辑复制的部署
PS： 以下的逻辑从库可以在新的机器上部署，如在原先的从库上修改，需停止原实例，并将recovery.conf文件删除或重命名（如修改为recovery.conf.done）
2.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.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
2.3 创建逻辑复制账号
postgres=# CREATE USER logical_repl REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD \'logical_repl\';
CREATE ROLE
逻辑复制用户需要REPLICATION权限即可，可以不需要SUPERUSER权限，之后需要在发布节点上将需要同步的表赋权给logical_repl用户，使logical_repl账号具有对这些表的读权限。
2.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));
2.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=#
注：逻辑复制的表结构需要手动在从库创建
2.6 在逻辑主库上创建发布
/** 在发布主库上创建发布pub1,注意实在sourcedb库下执行 */ 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操作
2.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)