PostgreSQL自带逻辑复制简单使用

一、逻辑复制说明

二、搭建逻辑复制环境

2.1 发布端配置

发布端 postgresql.conf 配置

在发布端的 postgresql.conf 配置文件设置一下参数：

listen_addresses = '*'
wal_level = logical
max_replication_slots = 8
max_wal_senders = 10

参数设置说明如下：

• wal_level：设置成 ‘logical’ 才支持逻辑复制，该参数的含义是，让数据库在 WAL 日志中记录逻辑解码所需的更多信息，低于这个级别逻辑复制不能工作。
• max_replication_slots：设置值必须大于订阅的数量。
• max_wal_senders：由于每个订阅在主库都会占用主库的一个 WAL 发送进程，因此参数设置值必须大于max_replication_slots 参数值加上物理备库数。

发布端 pg_hba.conf 配置

在发布端的 pg_hba.conf 配置文件中设置一下参数：

host    replication     repuser       192.168.198.165/24         md5

含义是：允许用 rep从 192.168.6.22 的网络上发起到本数据库的流复制连接，使用 md5 密码认证。

2.2 订阅端配置

订阅端postgresql.conf 配置

在订阅端的 postgresql.conf 配置文件设置一下参数：

listen_addresses = '*'
wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8

参数设置说明如下：

• max_replication_slots：设置数据库复制槽数量，应该大于订阅节点的数量。
• max_logical_replication_workers：设置逻辑复制进程数，应大于订阅节点的数量，并且给表同步预留一些进程数量。max_logical_replication_workers 会消耗后台进程数，并且从 max_worker_precesses 参数设置的后台进程数中消费，因此 max_worker_precesses 参数需要设置的大些。

2.3 发布节点创建逻辑复制用户

2.3.1 创建逻辑复制用户

发布节点上的逻辑复制用户需要具备replication权限。发布端创建逻辑复制用户的命令：

postgres=# create user repuser replication login connection limit 8 password '123456';

注：用于逻辑复制的用户必须是 replication 角色或 superuser 角色。

2.3.2 为复制表创建发布

发布节点为复制表创建发布的命令如下：

postgres=# create database pubdb;
postgres=# \c pubdb repuser
You are now connected to database "pubdb" as user "repuser".

pubdb=> create table tt(id int4 primary key,name text);
CREATE TABLE

pubdb=> insert into tt values(1,'aa');
INSERT 0 1

--用 postgres 用户创建发布
postgres=# \c pubdb fbase
You are now connected to database "pubdb" as user "fbase".

pubdb=# create publication pub1 for table tt;
CREATE PUBLICATION

如果需要发布多张表，则表名间用逗号（,）分隔，如果需要发布所有表，则将“for table"调整为“for all tables”。

2.3.3 查看创建的发布

命令如下：

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

配置参数说明如下：

• pubname：指发布的名称。
• pubowner：指发布的属主，可以和 pg_user 视图的 usesyid 字段关联查询属主的具体信息。
• puballtables：是否发布数据库中的所有表，”t“ 表示发布数据库中的所有已存在的表和以后新建的表。
• pubinsert：”t“ 表示仅发布表上的 insert 操作。
• pubupdate：”t“ 表示仅发布表上的 update操作。
• pubdelete："t" 表示仅发布表上的 delete 操作。
• pubtruncate："t" 表示仅发布表上的 truncate 操作。

2.3.4 发布节点为复制用户授权

命令如下：

pubdb=# \c pubdb fbase
You are now connected to database "pubdb" as user "postgres".

pubdb=# grant connect on database pubdb to repuser;
GRANT
pubdb=# grant usage on schema public to repuser;
GRANT
pubdb=# grant select on tt to repuser;
GRANT

2.3.5 订阅节点创建接收表

命令如下：

[postgres@docker1 ~]$ psql -h 192.168.198.162 -p 8432

postgres=# create database subdb;
CREATE DATABASE
postgres=# create user repuser replication login connection limit 8 password '123456';
CREATE ROLE
postgres=# \c subdb repuser
You are now connected to database "subdb" as user "repuser".

subdb=> create table tt(id int4 primary key,name text);
CREATE TABLE

2.3.6 订阅节点创建订阅

命令如下：

subdb=> \c subdb fbase
You are now connected to database "subdb" as user "fbase".

subdb=# create subscription sub1 connection 'host=192.168.198.164 port=8432 dbname=pubdb user=repuser password=123456' publication pub1;
NOTICE:  created replication slot "sub1" on publisher
CREATE SUBSCRIPTION

2.3.7 查看订阅

命令如下：

subdb=# select * from pg_subscription;
  oid  | subdbid | subname | subowner | subenabled |                               subconninfo                                | subslotname 
| subsynccommit | subpublications 
-------+---------+---------+----------+------------+--------------------------------------------------------------------------+-------------
+---------------+-----------------
 16395 |   16385 | sub1    |       10 | t          | host=192.168.198.164 port=8432 dbname=pubdb user=repuser password=123456 | sub1        
| off           | {pub1}
(1 row)

2.3.8 发布节点为复制用户授权

命令如下：

subdb=# grant connect on database subdb to repuser;
GRANT
subdb=# grant usage on schema public to repuser;
GRANT
subdb=# grant select on tt to repuser;
GRANT

2.3.9 发布点查询连接信息

创建成功后，可以在发布节点查询到以下信息：

pubdb=# 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   | pubdb    | t      | 0/C010B18
(1 row)

配置完成后，发布节点向表中插入、删除数据

pubdb=> insert into tt values(2,'tt');
INSERT 0 1
pubdb=> delete from tt where id = 1;
DELETE 1

订阅节点查看数据：

subdb=# select * from tt;
 id | name 
----+------
  2 | tt
(1 row)

2.3.10 添加复制所需的表

示例如下：

在发布节点主库和订阅节点从库均添加一张新表，并添加到发布列表中。

--发布节点创建表结构，命令如下：
pubdb=> create table tb(id int primary key,addr varchar(100));
CREATE TABLE

--订阅节点创建表结构，命令如下：
subdb=> create table tb(id int primary key,addr varchar(100));
CREATE TABLE

--在发布节点中给逻辑复制账号授权，命令如下：
pubdb=> grant select on tb to repuser;
GRANT

--添加新表到发布列表
pubdb=> \c pubdb fbase
pubdb=# alter publication pub1 add table tb;
ALTER PUBLICATION

--在发布节点查看发布列表中的表名，命令如下：
pubdb=# select * from pg_publication_tables;
 pubname | schemaname | tablename 
---------+------------+-----------
 pub1    | public     | tt
 pub1    | public     | tb
(2 rows)

此时已将一张表添加到发布列表中。

--此时发布节点写入数据
pubdb=> insert into tb values(1,'beijing');
INSERT 0 1

--订阅节点查看数据
subdb=> select * from tb;
 id | addr 
----+------
(0 rows)

--订阅节点却没查到数据，需要在订阅节点库刷新一下订阅
subdb=> \c subdb fbase
You are now connected to database "subdb" as user "fbase".
subdb=# alter subscription sub1 refresh publication ;
ALTER SUBSCRIPTION

--刷新完成后查看，订阅节点已经有插入的数据了
subdb=# select * from tb;
 id |  addr   
----+---------
  1 | beijing
(1 row)

2.3.11 清除复制设置

订阅端执行。

命令如下：

subdb=# drop subscription sub1;
NOTICE:  dropped replication slot "sub1" on publisher
DROP SUBSCRIPTION

2.3.12 禁用和启用订阅

订阅端执行。

命令如下：

subdb=# alter subscription sub1 disable;
subdb=# alter subscription sub1 enable;

注意：

​ 两个或多个发布端的主键不能有重复的内容，并且相同的有唯一约束的字段不能有重复的内容，否则之后发布的会报错，订阅端发生主键或唯一约束冲突，并且停止复制；发生主键或唯一约束冲突后，可通过删除订阅端造成唯一约束冲突的记录，然后使用 alter subscription name enable 让订阅继续；

三、 视图和表介绍

3.1 pg_publication

介绍

pg_publication 是 PostgreSQL 中的一个系统表，用于存储逻辑复制发布的元数据。逻辑复制允许你从一个 PostgreSQL 数据库（发布者）向另一个 PostgreSQL 数据库（订阅者）复制数据更改。

表的结构

pg_publication 表包含了关于发布的各种信息，主要包括以下字段：

• pubname: 发布的名称。
• puballtables: 一个布尔值，指示该发布是否包含所有表。
• pubinsert, pubupdate, pubdelete, pubtruncate: 每个布尔字段分别指示发布是否包含 INSERT、UPDATE、DELETE 或 TRUNCATE 操作。

操作示例

# 创建发布
CREATE PUBLICATION mypublication FOR ALL TABLES;
CREATE PUBLICATION mypublication FOR TABLE mytable;
# 查看发布
SELECT * FROM pg_publication;
# 修改发布
ALTER PUBLICATION mypublication ADD TABLE new_table;
ALTER PUBLICATION mypublication DROP TABLE old_table;
# 删除发布
DROP PUBLICATION mypublication;

3.2 pg_publication_tables

介绍

pg_publication_tables 是 PostgreSQL 中的一个系统视图，用于存储与发布（publication）相关的表信息。这个视图提供了逻辑复制中包含的表的详细信息。

视图的结构

pg_publication_tables 视图包含了以下字段：

• publication: 发布的名称。
• tablename: 表的名称。
• schemaname: 表所在的模式名称。
• pubinsert, pubupdate, pubdelete, pubtruncate: 每个布尔字段分别指示发布是否包含 INSERT、UPDATE、DELETE 或 TRUNCATE 操作。

操作示例

# 要查看特定发布中的所有表
SELECT * FROM pg_publication_tables WHERE publication = 'your_publication_name';
# 查看所有发布中的表
SELECT * FROM pg_publication_tables;

3.3 pg_subscription

pg_subscription 是 PostgreSQL 中的一个系统表，用于存储逻辑复制订阅（subscription）的元数据。逻辑复制允许您从一个 PostgreSQL 数据库（发布者）向另一个 PostgreSQL 数据库（订阅者）复制数据更改。

表的结构

pg_subscription 表包含了关于订阅的各种信息，主要包括以下字段：

• subname: 订阅的名称。
• subowner: 订阅的拥有者。
• subenabled: 一个布尔值，指示该订阅是否处于启用状态。
• subconninfo: 一个字符串，包含用于连接到发布者的连接信息。
• subslotname: 一个字符串，包含用于发布者端复制槽的名称。
• subpublications: 一个字符串数组，包含订阅的发布名称列表。
• subcopydata: 一个布尔值，指示订阅是否复制初始数据。
• subslot_type: 一个字符串，指示复制槽的类型（通常是 'logical'）。
• suboptions: 一个字符串数组，包含额外的订阅选项。

操作示例

# 创建订阅
CREATE SUBSCRIPTION mysubscription CONNECTION 'host=my_publisher_host dbname=my_publisher_db user=my_publisher_user password=my_publisher_password' PUBLICATION mypublication;
# 查看订阅
SELECT * FROM pg_subscription;
# 启用/禁用订阅
ALTER SUBSCRIPTION mysubscription ENABLE;
ALTER SUBSCRIPTION mysubscription DISABLE;
# 删除订阅
DROP SUBSCRIPTION mysubscription;

3.4 pg_replication_slots

介绍

pg_replication_slots 是 PostgreSQL 中的一个系统表，用于存储复制槽（replication slot）的信息。复制槽是持久化的复制起点，它们保存了复制流的状态，使得订阅者能够从发布者那里接收更新的数据。

表的结构

pg_replication_slots 表包含了关于复制槽的各种信息，主要包括以下字段：

• slot_name: 复制槽的名称。
• slot_type: 复制槽的类型，通常为 'logical' 或 'physical'。
• plugin: 所使用的插件名称。
• tmp_slot_name: 如果适用，临时复制槽的名称。
• active: 一个布尔值，指示复制槽是否处于活动状态。
• active_pid: 如果复制槽处于活动状态，这是复制进程的 PID。
• xmin: 事务 ID 的最小值，用于确定何时可以清理旧的 WAL 文件。
• catalog_xmin: 目录事务 ID 的最小值。
• restart_lsn: 复制槽的重启位置，即复制槽的最新位置。
• confirmed_flush_lsn: 已确认的刷新位置，即已确认被订阅者接收到的位置。

操作示例

# 创建复制槽
SELECT * FROM pg_create_logical_replication_slot('myslot', 'test_decoding');
# 查看复制槽
SELECT * FROM pg_replication_slots;
# 删除复制槽
SELECT * FROM pg_drop_replication_slot('myslot');