PostgreSQL 逻辑复制

目前逻辑复制中两点需要注意：

1. 不支持DDL的同步，需要主库与备库手动执行
2. 仅支持table的复制。其他对象不支持

配置发布节点 - 主库端

修改主库配置参数

vi postgresql.conf配置文件

wal_level = replica
max_wal_senders = 10 
max_replication_slots = 8

创建逻辑复制用户

[postgres@pg01 data]$ psql

create user luser
replication
login
connection limit 8
encrypted password '123456';

创建测试表

创建要复制的表，并插入一条测试数据

dong=# create table tlt (id int , name varchar(20));
CREATE TABLE

dong=# insert into tlt values (1 , 'logical');
INSERT 0 1

注意tlt表上，没有主键

赋予select权限

将需要复制的表，授予select权限给复制用户

dong=# grant select on tlt to luser;
GRANT

创建发布

发布该库中的指定的表：

dong=# create publication pub1 for table tlt;

或者也可以发布该库中的所有表，包括以后在这个库中新建的表：

dong=# create publication pub1 for all table ; 

查看发布的表list

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

查询发布状态

dong=# select * from pg_publication;
  oid  | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate 
-------+---------+----------+--------------+-----------+-----------+-----------+-------------
 16464 | pub1    |       10 | f            | t         | t         | t         | t
(1 row)

配置订阅节点 - 逻辑备库端

修改备库配置参数

vi postgresql.conf

[postgres@pg02 data]$ vi postgresql.conf
max_replication_slots = 8
max_logical_replication_workers = 4

重启后生效

[postgres@pg02 data]$ pg_ctl  restart  

创建相应的库

[postgres@pg02 data]$ createdb dong

创建需要复制的表结构

注意我这里创建表的时候，并没有创建到dong这个数据库中（其实是不小心创建错了，那就将错就错吧，看后边会有什么情况）

postgres=# create table tlt (id int , name varchar(20));
CREATE TABLE

创建订阅

创建订阅，必须使用pg的用户才可以

[postgres@pg02 data]$ psql

postgres=# create subscription sub1 connection 'host=192.168.56.103 port=5432 dbname=dong user=luser password=123456'
postgres-# publication pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

在发布节点（主）查看逻辑复制槽信息

postgres=# select slot_name , plugin , slot_type , database , active , restart_lsn
postgres-#   from pg_replication_slots;
   slot_name   |    plugin     | slot_type | database | active | restart_lsn 
---------------+---------------+-----------+----------+--------+-------------
 logical_slot1 | test_decoding | logical   | postgres | f      | 0/7F014FD0
 sub1          | pgoutput      | logical   | dong     | t      | 0/82000688
(2 rows)

在订阅节点（备） 查看逻辑复制信息

postgres=# select * from pg_subscription;
  oid  | subdbid | subname | subowner | subenabled |                             subconninfo                              | subslotname | subsynccommit | subpublic
ations 
-------+---------+---------+----------+------------+----------------------------------------------------------------------+-------------+---------------+----------
-------
 24597 |   13830 | sub1    |       10 | t          | host=192.168.56.103 port=5432 dbname=dong user=luser password=123456 | sub1        | off           | {pub1}
(1 row)

检查数据

发布端

dong=# insert into tlt values (2,'data');
INSERT 0 1
dong=# 
dong=# 
dong=#  select * from tlt;
 id |  name   
----+---------
  1 | logical
  2 | data
(2 rows)

订阅端

postgres=#  select * from tlt;
 id |  name   
----+---------
  1 | logical
  2 | data
(2 rows)

支持update、delete

我们之前买了一个伏笔，主库的表没有主键，那么是无法同步update、delete操作的，并且主库端也会报错，如下：

dong=# update tlt set name='new date' where id=2;
ERROR:  cannot update table "tlt" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.

DDL操作是不能同步的，所以需要在两个节点上分别创建主键。

dong=# alter table tlt add primary key(id);
ALTER TABLE
dong=# 

再次尝试更新数据，成功

dong=# update tlt set name='new date' where id=2;
UPDATE 1

在接收端，查看是否有 new data

postgres=# select * from tlt;
 id |   name   
----+----------
  1 | logical
  2 | new date
(2 rows)

添加复制表

1 - 发布：新建表

新建表

dong=# create table order_detail (id serial , 
dong(#     order_info varchar(20) , 
dong(#     order_date timestamp(0) with time zone) ;
CREATE TABLE

插入测试数据

dong=# insert into order_detail (id , order_date)
dong-# values (1, '2015-10-01');
INSERT 0 1

dong=# insert into order_detail (id , order_date)
dong-# values (1, '2021-10-01');
INSERT 0 1

查看测试表及数据

dong=# select * from order_detail;
 id | order_info |       order_date       
----+------------+------------------------
  1 |            | 2015-10-01 00:00:00+08
  1 |            | 2021-10-01 00:00:00+08
(2 rows)

2 - 发布：赋权给复制账户

dong=# grant select on order_detail to luser;
GRANT

3 - 发布：添加至已有的发布

dong=# alter publication pub1 add table order_detail;
ALTER PUBLICATION

4 - 发布：检查

dong=# select * from pg_publication_tables;
 pubname | schemaname |  tablename   
---------+------------+--------------
 pub1    | public     | tlt
 pub1    | public     | order_detail
(2 rows)

5 - 订阅：新建表

```sql
dong=# create table order_detail (id serial , 
dong(#     order_info varchar(20) , 
dong(#     order_date timestamp(0) with time zone) ;
CREATE TABLE

## 6 - 订阅：刷新订阅

```sql
postgres=# alter subscription sub1 refresh publication;
ALTER SUBSCRIPTION

7 - 订阅：检查

postgres=# select * from order_detail;
 id | order_info |       order_date       
----+------------+------------------------
  1 |            | 2015-10-01 00:00:00+08
  1 |            | 2021-10-01 00:00:00+08
(2 rows)

删除复制表

发布：在发布中drop

alter publication pub1 drop table order_detail;

逻辑复制启动、停止

订阅节点启动同步数据

alter subscription sub1 disable;

订阅节点停止同步数据

alter subscription sub1 enable;