PostgreSQL自带逻辑复制简单使用
PostgreSQL自带逻辑复制简单使用
一、逻辑复制说明
| 角色 | IP | 端口 | 数据库名 | 用户名 | 版本 |
|---|---|---|---|---|---|
| 发布端 | 192.168.198.165 | 8432 | pubdb | repuser | PostgreSQL 13.13 |
| 订阅端 | 192.168.198.162 | 8432 | subdb | repuser | PostgreSQL 13.13 |
二、搭建逻辑复制环境
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');
