原作者：戴秋龙

• 逻辑复制案例
• 配置步骤

逻辑复制案例

学习PG逻辑复制，由于测试环境实例有限在同一个实例中不同库之间配置逻辑复制。

1. 需要在发布端建好复制槽，然后再订阅端使用该复制槽，否则订阅时会一直hang住。
2. 逻辑从库不能同步DDL语句，比如新建索引，添加字段等。

配置步骤

1. 配置主库，逻辑从库配置文件
   主库：

wal_level = logical
max_wal_senders = 10
max_replication_slots = 8

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

逻辑从库：

  wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8

max_replication_slots：设置数据库复制槽数量，应大于订阅节点的数量
max_logical_replication_workers：设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量，此参数默认值为4

1. 创建逻辑复制账号

  CREATE USER logical_repl REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'logical_repl';

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

3.在主库/从库上面创建库以及表
逻辑主库：
/** 创建逻辑复制主库表 */

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

逻辑从库：
/** 在逻辑从库上创建不同的库 */

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

1. 逻辑主库上创建发布
   /** 在发布主库上创建发布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)

1. 发布端建立复制槽

解释  select pg_create_logical_replication_slot('pg_logical_kkcx_melotall','pgoutput');
pg_create_logical_replication_slot   
---------------------------------------
 (pg_logical_kkcx_melotall,0/16B2C828)

由于同一个实例需要，再发布端建立逻辑复制槽。

1. 从库上面建立订阅

  desdb=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=192.168.1.5 port=5432 user=logical_repl dbname=sourcedb password=logical_repl' PUBLICATION pub1  with (create_slot=false,slot_name='pg_logical_kkcx_melotall');

1. 逻辑复制账号授权

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

1. 添加逻辑复制表
   /** 主库上创建表结构 */

  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)