当前位置: 首页 > article >正文

PostgreSQL16 的双向逻辑复制

一、配置 双向逻辑复制具体步骤

参考:PostgreSQL 16 双向逻辑复制与事务回环控制 - 墨天轮

1. 安装和准备环境

  • 确保在所有参与复制的服务器上都安装了 PostgreSQL 16。
  • 主服务器:192.168.0.100
  • 从服务器:192.168.0.102

2. 配置 PostgreSQL

在每个服务器上执行以下操作:

a. 修改 `postgresql.conf

在文件最后添加以下配置:

wal_level = logical

max_replication_slots = 10

max_wal_senders = 10

max_logical_replication_workers = 4

#shared_preload_libraries = ‘pglogical’

注意:

  • wal_level:设置 wal_level 为 logical,这是启用逻辑复制的必要条件。
  • max_replication_slots:设置一个足够大的值,以允许足够的复制槽(replication slots)。
  • max_wal_senders:设置一个足够大的值,以允许足够的 WAL 发送进程。
  • max_logical_replication_workers:设置一个足够大的值,以允许足够的逻辑复制工作者。
  • shared_preload_libraries:添加 pglogical(如果你使用 pglogical 扩展)或其他任何逻辑复制扩展。postgresql10以后自身具有逻辑复制功能,即发布和订阅,但是之前的版本如果想要表级别的同步,需要pglogic插件实现。
b. 修改 `pg_hba.conf
  • 配置适当的认证规则,允许其他服务器连接到当前服务器进行复制。
  • 图中开放所有端口连接

c. 修改配置后需要重启postgresql

如果开始配置的时候就没有启动postgresql,就无需重启,直接启动即可

3. 创建复制用户

  • 在每个服务器上创建一个专门用于复制的用户。

  • \du 用于列出所有角色以及它们的相关信息

复制

CREATE USER repuser replication LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'repuser';

4. 配置双向复制

  • 在两个服务器上都创建发布和订阅,以便它们可以相互复制。
a. 在服务器 A 上:
  • 创建发布,指向服务器 B。
  • 创建订阅,从服务器 B 接收数据。
b. 在服务器 B 上:
  • 创建发布,指向服务器 A。
  • 创建订阅,从服务器 A 接收数据。

5. 初始化复制

a. 创建发布

一个发布只能存在于一个数据库中,所以创建发布者时,需要先指定数据库。

  • 主服务器复制
CREATE PUBLICATION aspub1 FOR ALL TABLES;
CREATE PUBLICATION nspub1 FOR ALL TABLES;
CREATE PUBLICATION hrpub1 FOR ALL TABLES;
  • 从服务器复制
CREATE PUBLICATION aspub2 FOR ALL TABLES;
CREATE PUBLICATION nspub2 FOR ALL TABLES;
CREATE PUBLICATION hrpub2 FOR ALL TABLES;
b. 创建订阅
  • 主服务器。复制
CREATE SUBSCRIPTION hrsub1 
CONNECTION 'host=192.168.0.102 port=5432 user=repuser password=repuser dbname=postgres' 
PUBLICATION hrpub2 
WITH (origin = none, copy_data = false);
CREATE SUBSCRIPTION assub1 
CONNECTION 'host=192.168.0.102 port=5432 user=repuser password=repuser dbname=loraserver_as' 
PUBLICATION aspub2 
WITH (origin = none, copy_data = false);
CREATE SUBSCRIPTION nssub1 
CONNECTION 'host=192.168.0.102 port=5432 user=repuser password=repuser dbname=loraserver_ns' 
PUBLICATION nspub2 
WITH (origin = none, copy_data = false);
  • 从服务器。复制
CREATE SUBSCRIPTION hrsub2 
CONNECTION 'host=192.168.0.100 port=5432 user=repuser password=repuser dbname=postgres' 
PUBLICATION hrpub1 
WITH (origin = none, copy_data = false);

指定在复制启动后是否应复制正在订阅的发布中的现有数据。 默认值是

CREATE SUBSCRIPTION assub2 
CONNECTION 'host=192.168.0.100 port=5432 user=repuser password=repuser dbname=loraserver_as' 
PUBLICATION aspub1 
WITH (origin = none, copy_data = false);
CREATE SUBSCRIPTION nssub2 
CONNECTION 'host=192.168.0.100 port=5432 user=repuser password=repuser dbname=loraserver_ns' 
PUBLICATION nspub1 
WITH (origin = none, copy_data = false);
c. 查看创建
-- 列出所有发布
SELECT * FROM pg_publication;
-- 列出所有订阅
SELECT * FROM pg_subscription;
d. 删除创建
-- 断开订阅(如果它正在运行)
ALTER SUBSCRIPTION your_subscription_name DISABLE;

-- 删除发布
DROP PUBLICATION your_publication_name;

-- 删除订阅
DROP SUBSCRIPTION your_subscription_name;
e. 具体截图

主机截图

从机截图

6. 验证复制

a.使用navicat连接两个数据库

b.在主机192.168.1.181中插入数据,看从机192.168.1.182是否发生变化

主机新建user表,在从机中出现user表

c.在从机192.168.1.182中插入数据,看主机192.168.1.181是否发生变化

从机插入数据

主机更新数据

注意事项

  • 确保使用 WITH (ORIGIN = NONE) 参数来避免复制循环。
  • 考虑网络延迟和带宽对复制性能的影响。
  • 确保数据一致性和完整性。

二、梳理原理图

一个发布只能存在于一个数据库中,一个订阅者只能连接到一个数据库。

参考:https://blog.csdn.net/xuejianxinokok/article/details/135099053PostgreSQL 16 的新功能:双向逻辑复制_postgresql16中的新增功能:双向逻辑复制-CSDN博客

画板

说明:Postgres 16 添加了一个 WITH 语句的新参数,用于过滤掉某些节点的复制。双向逻辑复制使用此参数WITH(ORIGIN = NONE),这会过滤掉来自非NONE 源的连接的所有复制。本质上,这只允许复制新添加的数据,您可能会明白为什么会出现这种情况。如果一个数据库插入新数据并将其复制到第二个数据库,则第二个数据库将复制该数据并插入它,从而触发对原始数据库的另一次复制。我们很快就会得到无限的复制循环,这就是为什么这个选项对于保持一切有限是必要的。

三、新增一张表步骤

  1. 主从pgsql上个创建tb_test表:

create table tb_test(id int primary key ,name varchar(100));

  1. 主pgsql上,授权复制用户对新表的SELECT权限:

GRANT SELECT ON tb_test TO repuser;

注意:如果主从pgsql都给复制用户默认授予了SELECT权限,本步骤可以省略:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO repuser;

  1. 主从pgsql上刷新各自的订阅者,启动同步(如果表里没有数据,copy_data可设为false,就不会去复制初始数据。如果有数据,那一定要设为true,或者不设默认为true,否则会报错。报错时只要把数据都清掉就可以):

ALTER SUBSCRIPTION hrsub1 REFRESH PUBLICATION WITH (copy_data = false);

ALTER SUBSCRIPTION hrsub2 REFRESH PUBLICATION WITH (copy_data = false);

==================================================

四、碰到的问题

日志中文乱码

参考这边文章,修改postgresql.conf文件的下列配置,改成英文:PostgreSQL-解决连接时遇到的乱码问题_postgresql utf8-CSDN博客

lc_messages = ‘en_US.UTF-8’ # locale for system error message
lc_monetary = ‘en_US.UTF-8’ # locale for monetary formatting
lc_numeric = ‘en_US.UTF-8’ # locale for number formatting
lc_time = ‘en_US.UTF-8’ # locale for time formatting

permission denied for table user

从机(订阅者)的pgsql日志上报错:

参考:https://www.51cto.com/article/749596.html

**原因:**没有给复制用户授予对user表的select权限。

**解决方法:**在主机(发布者)上,执行授权sql语句:GRANT SELECT ON public.user TO repuser;

延伸知识

创建复制用户

选择数据库,创建复制用户:repuser

CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 8 ENCRYPTED PASSWORD 'repuser'; 

limit 8 ???

授予对公共模式的使用权限

在主机上操作:

GRANT USAGE ON SCHEMA public TO repuser;

能重复执行吗?

授予对表的读权限

在主机上操作:为了确保逻辑复制能够正常进行,还需要授予用户repuser对特定表user的读权限。

GRANT SELECT ON public.user TO repuser;

设置默认权限

为了简化未来的权限管理,可以设置默认权限,使得新创建的表自动授予该用户读权限。

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO repuser;

添加表

在逻辑主库和逻辑从库手动添加表后,需要在主机把表添加到发布列表中。

ALTER PUBLICATION hrpub1 ADD TABLE logical_tb2;

在主库查看发布列表中的表明。

SELECT * FROM pg_publication_tables;

刷新订阅

使用REFRESH PUBLICATION命令来刷新订阅的发布信息,并确保所有表都被正确复制,避免潜在的数据丢失。

ALTER SUBSCRIPTION hrsub1 REFRESH PUBLICATION;

duplicate key value violates unique constraint “t2_pkey”

把冲突的那行数据删除。

如果有自增主键冲突,参考:postgresql增序列报错问题ERROR: duplicate key value violates unique constraint “sys_department_pkey”_dumplicate key values-CSDN博客

copy_data尽量用false

在创建订阅者和刷新订阅者时,如果把copy_data = true,它会先复制主机(发布者)的初始数据,如果从机(订阅者)的数据库里有数据时,会造成冲突。

只有在从机(订阅者)的数据库里没有数据,需要复制初始数据时,才需要设为true。

创建订阅者

CREATE SUBSCRIPTION nssub2

CONNECTION ‘host=192.168.0.100 port=5432 user=repuser password=repuser dbname=loraserver_ns’

PUBLICATION nspub1

WITH (origin = none, copy_data = false);

刷新订阅者

ALTER SUBSCRIPTION nssub2 REFRESH PUBLICATION with (copy_data = false);

【碰到的问题】

  • 问题描述:

创建订阅者时,如果没有带上WITH (origin = none, copy_data = false),默认copy_data = true。会生成很多复制槽,应该是同步数据时需要。

导致复制槽数量超上限(默认10个)。后续再创建其它订阅者时,因为创建不了复制槽而失败。

  • 解决办法:

首先,删除订阅者,它会同步删除对应的复制槽。然后加上WITH (origin = none, copy_data = false)重新创建订阅者。

删除订阅者

在订阅者数据库侧,先禁用再删除:

ALTER SUBSCRIPTION assub2 DISABLE;

DROP SUBSCRIPTION assub2;

在发布者数据库侧,删除复制槽:

SELECT pg_drop_replication_slot(‘assub2’);

创建发布者

需要选择数据库,否则就会创建再默认的progres数据库下。

查询发布者相关信息时,也需要选择数据库,否则只查到progres数据库下的。

select * from pg_publication;

SELECT * FROM pg_publication_tables;

用到的sql记录

select * from pg_publication;

select * from pg_subscription;

select * from pg_user;

select * from pg_replication_slots;

SELECT * FROM pg_publication_tables;

SELECT * FROM pg_stat_replication;

SELECT * FROM pg_stat_subscription;

GRANT SELECT ON api_key, application, code_migration, device, device_keys, device_multicast_group, device_profile, gateway,

gateway_ping, gateway_ping_rx, gateway_profile, integration, multicast_group, network_server, organization,

organization_user, schema_migrations, service_profile, public.user TO repuser;

GRANT USAGE ON SCHEMA public TO repuser;

create table logical_tb2(id int primary key ,addr varchar(100));

GRANT SELECT ON logical_tb2 TO repuser;

ALTER SUBSCRIPTION nssub1 REFRESH PUBLICATION with (copy_data = false);

SELECT pg_get_serial_sequence(‘t2’, ‘aa’);

create table tb_test(id int primary key ,name varchar(100));

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO repuser;

SELECT * FROM pg_stat_subscription;

select * from pg_stat_replication;

select * from pg_stat_subscription;

SELECT COUNT(*) FROM pg_stat_activity;

SHOW max_connections;

SELECT rolname, rolconnlimit FROM pg_roles WHERE rolname = ‘repuser’;

ALTER ROLE repuser CONNECTION LIMIT 10;

CREATE SUBSCRIPTION assub1

CONNECTION ‘host=192.168.0.102 port=5432 user=repuser password=repuser dbname=loraserver_as’

PUBLICATION aspub2

WITH (origin = none, copy_data = false);

CREATE SUBSCRIPTION nssub1

CONNECTION ‘host=192.168.0.102 port=5432 user=repuser password=repuser dbname=loraserver_ns’

PUBLICATION nspub2

WITH (origin = none, copy_data = false);

CREATE SUBSCRIPTION assub2

CONNECTION ‘host=192.168.0.100 port=5432 user=repuser password=repuser dbname=loraserver_as’

PUBLICATION aspub1

WITH (origin = none, copy_data = false);

CREATE SUBSCRIPTION nssub2

CONNECTION ‘host=192.168.0.100 port=5432 user=repuser password=repuser dbname=loraserver_ns’

PUBLICATION nspub1

WITH (origin = none, copy_data = false);

CREATE PUBLICATION aspub1 FOR ALL TABLES;

CREATE PUBLICATION nspub1 FOR ALL TABLES;

CREATE PUBLICATION aspub2 FOR ALL TABLES;

CREATE PUBLICATION nspub2 FOR ALL TABLES;

ALTER SUBSCRIPTION assub1 SET PUBLICATION aspub2;

ALTER SUBSCRIPTION nssub1 SET PUBLICATION nspub2;

ALTER SUBSCRIPTION assub2 SET PUBLICATION aspub1;

ALTER SUBSCRIPTION nsSub2 SET PUBLICATION nspub1;

SELECT * FROM pg_subscription;

DROP SUBSCRIPTION assub2;

ALTER SUBSCRIPTION assub2 DISABLE;

SELECT pg_drop_replication_slot(‘pg_90159_sync_33441_7399912264007474316’);

DROP PUBLICATION aspub1;


http://www.kler.cn/a/584674.html

相关文章:

  • Netty基础—4.NIO的使用简介一
  • 【贪心算法5】
  • 使用DeepSeek完成一个简单嵌入式开发
  • 如何优化AI模型的Prompt:深度指南
  • 基于jvisualvm的内存监控与远程连接配置指南
  • K8s 1.27.1 实战系列(十)PV PVC
  • C# Unity 唐老狮 No.9 模拟面试题
  • Vue:其他指令
  • Qt的QMenu 和 QAction的样式设置
  • golang从入门到做牛马:第二十篇-Go语言接口:行为的“契约”
  • C#类型转换大总结
  • 4.3 数组和集合的初始及赋值
  • 云原生可观测性:智能运维的数据中枢
  • DeepSeek-R1深度解读
  • HarmonyOS开发 - 电商App实例三( 网络请求axios)
  • nginx中proxy_pass和root的区别
  • STL-List模拟
  • 【QT】:QT图形化界面相关准备工作
  • 【python运行Janus-Pro-1B文生图功能】
  • 开源!速度100Kb/s的有线和无线双模ESP32S3芯片的DAP-Link调试器