PostgreSQL12 如何逻辑复制部署

前言：

我们前面提到的流复制（即物理复制）是基于实例级别的同步，即备库和主库是一模一样的；而逻辑复制是基于表级别的，可以同步某些指定的表，实现更细粒度的同步功能。使用逻辑复制的功能，我们在实现特殊需求时会方便的多，比如PostgreSQL的跨大版本升级、从一个实例拆分某一个库到另一个实例等等。

逻辑复制架构图：

原理：

逻辑复制的原理其实就是master节点将其Publication（你可以暂且理解为一种数据对象）中的表的WAL日志进行解析后，形成一种特殊格式的日志流，传送给slave节点，之后备库上的Subscription对这些日志流进行解析重放，从而达到同步表数据的功能。这之间的WAL传输都是通过各自服务器上的WAL发布进程和订阅进程所完成的。

注意事项：
待同步的表必须要有复制标识（主键、唯一索引），也可以设置为full表示整行数据作为键值但效率非常低，若没有复制标识，则表上的update/delete操作会报错。
DDL操作不会被复制，即master节点上的DDL操作，需手工在logical slave节点上执行一下；

环境信息：

角色	主机IP	端口	库名	复制用户名	版本
发布节点master	10.25.15.77	1921	mydb	logical_user	PostgreSQL12
发布节点slave	10.25.15.79	1921	desdb	logical_user	PostgreSQL12

部署步骤：

1.参数设置

修改master节点的配置文件postgresql.conf

wal_level = logical
max_replication_slots = 8
max_wal_senders = 10

修改logical slave节点的配置文件postgresql.conf

max_replication_slots = 4
max_logical_replication_workers = 4
max_worker_processes = 8

2.创建逻辑用户

在master节点创建逻辑复制用户，此用户需要有REPLICATION权限，以及待同步表的读权限
create user logical_user replication login connection limit 8 encrypted password 'admin123';

创建测试表：
mydb=# create table table1(id int4, name text, primary key(id)); mydb=# insert into table1 values(1,'a');

赋权：
mydb=# grant select on table1 to logical_user;

注：如果嫌一个一个表的赋权麻烦，可以直接让logical_user拥有库下某个schema中所有表的select权限：
mydb=# grant select on all tables in schema public to logical_user; （默认schema名称是public，关于schema的知识请自行搜索）

3.创建发布对象

在master节点上创建Publication
mydb=# create publication pub1 for table table1;
CREATE PUBLICATION

注：

• pub1是发布对象的名字；
• FOR TABLE，指加入发布对象里的表名列表，多表之间用逗号隔开，也可以使用FOR ALL TABLES，表示将当前库中所有表添加进来
  查询刚刚创建的发布：

mydb=# select * from pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
-------+---------+----------+--------------+-----------+-----------+-----------+-------------
40989 | pub1    |       10 | f            | t         | t         | t         | t

说明：

• pubowner，指发布对象的owner
• puballtables，表示是否发布当前库的所有表，f表示false，t表示true
• pubinsert，指是否同步insert操作
• pubupdate，指是否同步update操作
• pubdelete，指是否同步delete操作
• pubtruncate，指是否同步truncate操作

4.创建订阅对象

因为DDL操作无法同步过来，所以需要手动在logical slave上先创建表,但只许创建表结构

postgres=# \c desdb;        #切换在desdb库下；
desdb=# create table table1(id int4, name text, primary key(id));

处于安全角度的考虑，建议创建一个隐藏的密码文件~/.pgpass, 内容如下：

10.25.15.77:1921:mydb:logical_user:admin123

修改一下权限：
[postgres@ndcnvx509 ~]# chmod 600 .pgpass

在logical slave节点上创建订阅对象，

desdb=# create subscription sub_1 connection 'host=10.25.15.77 port=1921 dbname=mydb user=logical_user' publication pub1;
NOTICE:  created replication slot "sub_1" on publisher
CREATE SUBSCRIPTION

注：上述红色字体created replication slot "sub_1" on publisher显示自动创建了一个复制槽 sub_1
说明：

• sub_1，表示订阅名称；
• connection，后面紧跟的是连接信息，主要有host,port,dbname(待同步的库），用户名user，而密码则会自动去上述创建的隐藏文件~/.pgpass中读取。
• pub1，表示这次连接的发布对象的名称，即接收这个发布对象同步过来的表；

查看订阅对象

desdb=# select * from pg_subscription;
  oid  | subdbid | subname | subowner | subenabled |                       subconninfo                        | subslotname | subsynccommit | subpublications
-------+---------+---------+----------+------------+----------------------------------------------------------+-------------+---------------+-----------------
40993 |   40979 | sub_1   |       10 | t          | host=10.25.15.77 port=1921 dbname=mydb user=logical_user | sub_1       | off           | {pub1}

此时我们在master节点上也可以查看到订阅的相关信息
（在master节点上执行）

mydb=# select slot_name,plugin,slot_type,database,active,restart_lsn from pg_replication_slots;
slot_name |  plugin  | slot_type | database | active | restart_lsn
-----------+----------+-----------+----------+--------+-------------
sub_1     | pgoutput | logical   | mydb     | t      | 0/15002340

5.验证同步结果

此时我们查看logical slave的table1表上是否数据已经同步

desdb=# select * from table1;
id | name
----+------
  1 | a

此时master，slave节点上分别会有WAL发布进程和WAL订阅进程如下：
postgres: walsender logical_user 10.25.15.79(39236) idle （master节点）

postgres: logical replication worker for subscription 40993 （slave节点）

至此，PostgreSQL的逻辑复制架构就算是部署完成了。。。

补充部分

6.如何新增一个表的同步

如果我们想新增加一张表的同步，怎么办？请使用如下命令：
（在master节点上）

mydb=# create table table2(id int4, age int4);            #注意此处我们并没有给表table2创建主键，为了后续实验
CREATE TABLE
mydb=# insert into table2 values(1,1);
INSERT 0 1
mydb=# grant select on table2 to logical_user;
GRANT
mydb=# alter publication pub1 add table table2;
ALTER PUBLICATION
mydb=# select * from pg_publication_tables;
pubname | schemaname | tablename
---------+------------+-----------
pub1    | public     | table1
pub1    | public     | table2

（logical slave节点上执行如下命令）

desdb=# create table table2(id int4, age int4);
CREATE TABLE
desdb=# alter subscription sub_1 refresh publication;        #刷新订阅
ALTER SUBSCRIPTION
desdb=# select * from table2;                    #此时查看发现数据已经同步过来
id | age
----+-----
  1 |   1

7.在没有主键的同步表上update会报错

刚才我们新建了一个没有主键的同步表table2，insert是没有问题，但是update的时候就会报错如下：
（master节点上）

mydb=# update table2 set age=22 where id=1;
ERROR:  cannot update table "table2" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.

原因：因为表上没有复制标识（没有主键 或 唯一索引），所以不允许更新

添加主键（master节点上执行）

mydb=# alter table table2 add primary key(id);
ALTER TABLE
mydb=# update table2 set age=22 where id=1;
UPDATE 1

注：logical slave节点上也需要添加相应主键，否则无法同步update操作

desdb=# alter table table2 add primary key(id);
ALTER TABLE
desdb=# select * from table2 where id=1;
id | age
----+-----
  1 |  22

8.逻辑复制启动和停止

其实就是通过开启，停止logical slave节点上的订阅来实现的
关闭订阅

desdb=# alter subscription sub_1 disable;
ALTER SUBSCRIPTION
desdb=# select subname,subenabled,subslotname,subpublications from pg_subscription;
subname | subenabled | subslotname | subpublications
---------+------------+-------------+-----------------
sub_1   | f          | sub_1       | {pub1}

开启订阅：

desdb=# alter subscription sub_1 enable;
ALTER SUBSCRIPTION
desdb=# select subname,subenabled,subslotname,subpublications from pg_subscription;
subname | subenabled | subslotname | subpublications
---------+------------+-------------+-----------------
sub_1   | t          | sub_1       | {pub1}

本文地址：https://blog.csdn.net/jianlong727/article/details/112596914