PostgreSQL逻辑复制搭建

1.什么是逻辑复制：

逻辑复制是基于表的复制方式，可以有选择性的复制一些表，可以实现跨大版本的数据同步。
主库从库都可读写，适用于从库要求写的业务场景。

2.搭建逻辑复制：

2.1 修改逻辑主库的postgresql.conf

wal_level=logical
max_wal_senders=10
max_replication_slots=8 

参数简要介绍：
wal_level:
max_wal_senders:walsender进程的最大数量
max_replication_slots:复制槽的最大数量

2.2 修改逻辑从库的postgresql.conf

wal_level=logical
max_replication_slots=8 
max_logical_replication_workers = 8 

2.3 创建流复制账号：

postgres=# create user logical_repl replication login connection limit 8 encrypted password 'logical_repl';
CREATE ROLE

2.4 在逻辑主库上创建库和表：

postgres=# create database sourcedb;
CREATE DATABASE
postgres=# \c sourcedb
You are now connected to database "sourcedb" as user "postgres".
sourcedb=# create table logical_tbl(id int primary key,name varchar(100));
CREATE TABLE

2.5 在逻辑主库上创建发布：

sourcedb=# create publication pub1 for table logical_tbl;
CREATE PUBLICATION
sourcedb=# select * from pg_publication;
 pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete 
---------+----------+--------------+-----------+-----------+-----------
 pub1    |       10 | f            | t         | t         | t

2.6 在逻辑从库上 建库和表
逻辑从库的表需要自己手动创建

postgres=# create database desdb;
CREATE DATABASE
postgres=# \c desdb;
You are now connected to database "desdb" as user "postgres".
desdb=# create table logical_tbl(id int primary key,name varchar(100));
CREATE TABLE

2.7 在逻辑从库上创建订阅：

desdb=# create subscription sub1 connection 'host=192.168.246.112 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,slot_type,database,active,restart_lsn from pg_replication_slots where slot_name = 'sub1';
 slot_name | slot_type | database | active | restart_lsn 
-----------+-----------+----------+--------+-------------
 sub1      | logical   | sourcedb | t      | 0/80AC5BF0
(1 row)

在逻辑从库上可以看到：

desdb=# select * from pg_subscription;
 subdbid | subname | subowner | subenabled |                                     subconninfo                                      | subslotname | subsynccommit | subpublicat
ions 
---------+---------+----------+------------+--------------------------------------------------------------------------------------+-------------+---------------+------------
-----
   25436 | sub1    |       10 | t          | host=192.168.246.112 port=5432 user=logical_repl dbname=sourcedb password=login_repl | sub1        | off           | {pub1}
(1 row)

2.8 给复制账号赋权：
在3.3中只创建了复制账号，并未对需要复制的表进行赋权，因此，此时逻辑从库日志中有如下错误：

ERROR,XX000,"could not start initial contents copy for table ""public.logical_tbl"": ERROR:  permission denied for relation logical_tbl",,,,,,,,,""

现在对复制账号进行赋权：

sourcedb=# grant usage on schema public to logical_repl;
GRANT
sourcedb=# grant select on logical_tbl to logical_repl;
GRANT

赋权后逻辑从库的日志显示正常：

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

2.9 测试数据同步
在逻辑主库中插入数据

/** 在sourcedb中插入两条数据 */
sourcedb=# insert into logical_tbl(id,name)values(1,'a'),(2,'b');
INSERT 0 2

在逻辑从库查看结果：

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

由此可见数据同步完成。
2.10 添加复制所需的表
在逻辑主库和逻辑从库均添加一张表，并添加到发布列表中：

sourcedb=# create table logical_tbl2(id int primary key,name varchar(100));
CREATE TABLE
desdb=# create table logical_tbl2(id int primary key,name varchar(100));
CREATE TABLE
sourcedb=# insert into logical_tbl2 values(1,'beijing');
INSERT 0 1
sourcedb=# grant select on logical_tbl2 to logical_repl;
GRANT
sourcedb=# alter publication pub1 add table logical_tbl2;
ALTER PUBLICATION

可是逻辑从库中却并未同步表2的数据：

desdb=# select * from logical_tbl2;
 id | name 
----+------
(0 rows)

这是因为需要在从库刷新下订阅：

desdb=# alter subscription sub1 refresh publication;
ALTER SUBSCRIPTION
desdb=# select * from logical_tbl2;
 id |  name   
----+---------
  1 | beijing
(1 row)

2.11 从主库查询发布了哪些表

sourcedb=# select * from pg_publication_tables ;
 pubname | schemaname |  tablename  
---------+------------+-------------
 pub1    | public     | logical_tbl
(1 row)

2.12 主库上表结构发生改变，从库还会继续同步数据吗

sourcedb=# alter table logical_tbl add column flag text;
ALTER TABLE
sourcedb=# insert into logical_tbl values(2,'qaz2',2);  
INSERT 0 1
在从库查询，可以看到，并没有同步第2条数据
desdb=# SELECT * FROM logical_tbl ;
 id | name | grade 
----+------+-------
  1 | qaz  |     1
(1 row)
从库也添加flag字段后，从库的表才会继续同步数据；
desdb=# alter table logical_tbl add column flag text;
ALTER TABLE
desdb=# SELECT * FROM logical_tbl ;                  
 id | name | grade | flag 
----+------+-------+------
  1 | qaz  |     1 | 
  2 | qaz2 |     2 | 
(2 rows)

2.13从库表中加条数据，主库再加这条数据，从库会同步主库中新添加的这条数据吗

desdb=# insert into logical_tbl values(3,'ss',3);    
INSERT 0 1
desdb=# SELECT * FROM logical_tbl ;              
 id | name | grade | flag 
----+------+-------+------
  1 | qaz  |     1 | 
  2 | qaz2 |     2 | 
  3 | ss   |     3 | 
(3 rows)

sourcedb=# insert into logical_tbl values(3,'wuliahao',3);
INSERT 0 1
sourcedb=# select * from logical_tbl;
 id |   name   | grade | flag 
----+----------+-------+------
  1 | qaz      |     1 | 
  2 | qaz2     |     2 | 
  3 | wuliahao |     3 | 
(3 rows)
desdb=# SELECT * FROM logical_tbl ;              
 id | name | grade | flag 
----+------+-------+------
  1 | qaz  |     1 | 
  2 | qaz2 |     2 | 
  3 | ss   |     3 | 
(3 rows)

这是因为表logical_tbl有主键限制，3这条记录在表中已存在，插入不进去了，后面主库再插入的数据也被阻塞，插入不进去了。

sourcedb=# insert into logical_tbl values(4,'nonghl',3,'wdfr');
INSERT 0 1
sourcedb=# select * from logical_tbl;                          
 id |   name   | grade | flag 
----+----------+-------+------
  1 | qaz      |     1 | 
  2 | qaz2     |     2 | 
  3 | wuliahao |     3 | 
  4 | nonghl   |     3 | wdfr
(4 rows)

在从库中只看到3条记录：

desdb=# SELECT * FROM logical_tbl ;              
 id | name | grade | flag 
----+------+-------+------
  1 | qaz  |     1 | 
  2 | qaz2 |     2 | 
  3 | ss   |     3 | 
(3 rows)

从日志中可以看到：提示主键重复

 20:05:37.991 CST,,,5828,,5f143711.16c4,2,,2020-07-19 20:05:37 CST,3/723,159750,ERROR,23505,
 "duplicate key value violates unique constraint ""logical_tbl_pkey""","Key (id)=(3) already exists.",,,,,,,,""
 20:05:37.993 CST,,,3265,,5f14169d.cc1,400,,2020-07-19 17:47:09 
 CST,,0,LOG,00000,"worker process: logical replication worker for subscription 25451 
 (PID 5828) exited with exit code 1",,,,,,,,,""

把从库这条ID=3的数据删掉试试呢
可以看到从库和主库数据一样了

desdb=# delete from logical_tbl where id =3;
DELETE 1
desdb=# SELECT * FROM logical_tbl ;
 id |   name   | grade | flag 
----+----------+-------+------
  1 | qaz      |     1 | 
  2 | qaz2     |     2 | 
  3 | wuliahao |     3 | 
  4 | nonghl   |     3 | wdfr
(4 rows)